~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

thread_id, event_id, rpad(event_name, 28, ' ') event_name, rpad(current_schema, 10, ' ') current_schema, rpad(digest_text, 72, ' ') digest_text, sql_text;

# Define instrument name for enable/disable instruments

let $pfs_instrument='%statement/%';

--echo

--echo #

--echo # STEP 1 - CREATE AND REPLICATE TEST TABLES

--echo #

connection master;

--echo

--echo **************

--echo *** MASTER ***

--echo **************

--echo

--echo *** Create test tables

--echo

show variables like '%binlog_format%';

--disable_warnings

drop table if exists test.marker;

--enable_warnings

eval $get_thread_id;

create table test.marker(s1 int) engine=innodb;

sync_slave_with_master;

--echo **************

--echo *** SLAVE ***

--echo **************

--echo

--echo *** Clear statement events

--source ../include/rpl_statements_truncate.inc

--echo

--echo #

--echo # STEP 2 - REPLICATE ONE ROW ON MASTER TO GET REPLICATION THREAD ID ON SLAVE

--echo #

connection master;

--echo **************

--echo *** MASTER ***

--echo **************

--echo

insert into test.marker values (0);

--echo

sync_slave_with_master;

100

101

--echo **************

102

--echo *** SLAVE ***

103

--echo **************

104

--echo

105

--echo *** Verify row, get replication thread id, clear statement events

106

--echo

107

108

# TODO: Get slave thread id from threads using thread/sql/slave_sql event name

109

110

select thread_id into @slave_thread_id from performance_schema.events_statements_history

111

where sql_text like '%marker%';

112

let $slave_thread_id= `select @slave_thread_id`;

113

114

--echo *** Verify row inserted on master was replicated

115

select count(*) = 1 as 'Expect 1' from test.marker;

116

117

--echo *** Clear statement events

118

--source ../include/rpl_statements_truncate.inc

119

120

--echo

121

--echo #

122

--echo # STEP 3 - PERFORM DML STATEMENTS ON MASTER

123

--echo #

124

125

connection master;

126

127

--echo **************

128

--echo *** MASTER ***

129

--echo **************

130

--echo

131

show variables like '%binlog_format%';

132

133

--echo *** Clear statement events

134

--source ../include/rpl_statements_truncate.inc

135

136

--echo

137

--echo *** Create/drop table, create/drop database

138

--echo

139

create database marker1_db;

140

create database marker2_db;

141

create table marker1_db.table1 (s1 int) engine=innodb;

142

create table marker2_db.table1 (s1 int) engine=innodb;

143

create table marker2_db.table2 (s1 int) engine=innodb;

144

145

--echo

146

--echo *** Transaction

147

start transaction;

148

insert into marker1_db.table1 values (1), (2), (3);

149

insert into marker2_db.table1 values (1), (2), (3);

150

commit;

151

152

--echo

153

--echo *** Alter

154

alter table marker1_db.table1 add column (s2 varchar(32));

155

156

--echo

157

--echo *** Insert, Update

158

start transaction;

159

insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six');

160

update marker1_db.table1 set s1 = s1 + 1;

161

commit;

162

163

--echo

164

--echo *** Rollback

165

start transaction;

166

insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine');

167

rollback;

168

169

--echo

170

--echo *** Autocommit, Delete, Drop

171

delete from marker1_db.table1 where s1 > 4;

172

drop table marker2_db.table1;

173

drop database marker2_db;

174

175

--source ../include/disable_instruments.inc

176

177

--echo

178

--echo *** Examine statements events that will be compared on the slave

179

--echo

180

181

--replace_column 1 [THREAD_ID] 2 [EVENT_ID]

182

183

eval select $column_list from performance_schema.events_statements_history_long

184

where sql_text like '%marker%' order by event_id;

185

186

--echo

187

--echo #

188

--echo # STEP 4 - REPLICATE STATEMENT EVENTS ON MASTER TO SLAVE

189

--echo #

190

--echo

191

--echo *** Store statement events in holding table, then replicate

192

--echo

193

194

--source ../include/disable_instruments.inc

195

196

--echo #

197

--echo # Create table to hold statement events for later comparison on the slave

198

--echo #

199

200

create table test.master_events_statements_history_long as

201

(select thread_id, event_id, event_name, sql_text, digest, digest_text, current_schema, rows_affected

202

from performance_schema.events_statements_history_long

203

where (thread_id=@my_thread_id and digest_text like '%marker%'));

204

205

--source ../include/enable_instruments.inc

206

207

--echo

208

--echo #

209

--echo # STEP 5 - VERIFY DML AND DDL STATEMENT EVENTS ON SLAVE

210

--echo #

211

212

sync_slave_with_master;

213

214

--echo **************

215

--echo *** SLAVE ***

216

--echo **************

217

218

--source ../include/disable_instruments.inc

219

220

--echo

221

--echo *** List statement events from master

222

--echo

223

--replace_column 1 [THREAD_ID] 2 [EVENT_ID]

224

eval select $column_list from master_events_statements_history_long order by event_id;

225

226

--echo

227

--echo *** List statement events on slave

228

--echo

229

230

--replace_column 1 [THREAD_ID] 2 [EVENT_ID]

231

232

eval select $column_list from performance_schema.events_statements_history_long

233

where thread_id = @slave_thread_id and sql_text like '%marker%' order by event_id;

234

235

--echo

236

--echo *** Compare master and slave events

237

--echo

238

239

# Note: The statement digest provides a more robust comparison than the

240

# event name. However, in some cases, e.g. DROP TABLE, the server generates

241

# its own version of the statement which includes additional quotes and a

242

# comment. A digest comparison is therefore impractical for server-generated

243

# statements, so we use both methods to ensure coverage.

244

245

--echo

246

--echo *** Event name comparison - expect 0 mismatches

247

--echo

248

249

select thread_id, event_id, event_name, digest_text, sql_text from performance_schema.events_statements_history_long t1

250

where t1.thread_id = @slave_thread_id and

251

sql_text like '%marker%' and

252

not exists (select * from master_events_statements_history_long t2 where t2.event_name = t1.event_name);

253

254

--echo

255

--echo *** Statement digest comparison - expect 1 mismatch for DROP TABLE

256

--echo

257

258

--replace_column 1 [THREAD_ID] 2 [EVENT_ID] 4 [DIGEST]

259

260

select thread_id, event_id, event_name, digest, digest_text, sql_text from performance_schema.events_statements_history_long t1

261

where t1.thread_id = @slave_thread_id and

262

sql_text like '%marker%' and

263

not exists (select * from master_events_statements_history_long t2 where t2.digest = t1.digest);

264

265

--echo

266

--echo #

267

--echo # STEP 6 - DISABLE REPLICATED STATEMENT EVENTS ON SLAVE

268

--echo #

269

--source ../include/rpl_statements_truncate.inc

270

--source ../include/enable_instruments.inc

271

272

update performance_schema.setup_instruments set enabled='no', timed='no'

273

where name like '%statement/abstract/relay_log%';

274

275

select * from performance_schema.setup_instruments where name like '%statement/abstract/relay_log%';

276

277

--echo

278

--echo #

279

--echo # STEP 7 - UPDATE TABLES ON MASTER, REPLICATE

280

--echo #

281

282

connection master;

283

284

--echo **************

285

--echo *** MASTER ***

286

--echo **************

287

--echo

288

--echo *** Clear statement events

289

--source ../include/rpl_statements_truncate.inc

290

291

--echo *** Update some tables, then replicate

292

--echo

293

294

insert into marker1_db.table1 values (999, '999'), (998, '998'), (997, '997');

295

296

--echo

297

--echo #

298

--echo # STEP 8 - VERIFY TABLE UPDATES FROM MASTER, EXPECT NO STATEMENT EVENTS ON SLAVE

299

--echo #

300

301

sync_slave_with_master;

302

303

--echo **************

304

--echo *** SLAVE ***

305

--echo **************

306

--echo

307

--echo *** Confirm rows were replicated

308

--echo

309

310

select * from marker1_db.table1 where s1 > 900 order by s1;

311

312

--echo

313

--echo *** Confirm that are no statements events from the replication thread

314

--echo

315

316

select * from performance_schema.events_statements_history_long

317

where thread_id = @slave_thread_id;

318

319

--source ../include/enable_instruments.inc

320

321

--echo

322

--echo #

323

--echo # STEP 9 - CLEAN UP

324

--echo #

325

--echo

326

--disable_query_log

327

--disable_warnings

328

329

connection master;

330

drop table test.marker;

331

drop table test.master_events_statements_history_long;

332

drop database marker1_db;

333

334

sync_slave_with_master;

335

336

--enable_warnings

337

--enable_query_log

338

--source include/rpl_end.inc

Older »