Debian Codegraph @

mysql-test/suite/ndb_rpl/t/ndb_rpl_mix_eng_trans.test

raw
1 # ==== Purpose ====
2 #
3 # Tests that transactions containing multiple table types are
4 # replicated correctly to the slave.
5 #
6 # This test was previously part of rpl_ndb_transactions.
7 #
8 #
9 # ==== Method ====
10 #
11 # Try all combinations of the following:
12 # - Committed/rollback transactions.
13 # - Transactions started by AUTOCOMMIT = 0 or BEGIN.
14 # - Transactions using myisam, innodb, or ndb tables, or combinations
15 # of them. For combinations, we use the engines in all possible
16 # orders.
17 # For single-engine transactions, we also try with AUTOCOMMIT = 1.
18 #
19 #
20 # ==== Related bugs ====
21 #
22 # BUG#26395: if crash during autocommit update to transactional table on master, slave fails
23
24
25 source include/have_ndb.inc;
26 source suite/ndb_rpl/ndb_master-slave.inc;
27
28 CREATE TABLE tmyisam (a int) ENGINE = MYISAM;
29 CREATE TABLE tinnodb (a int) ENGINE = INNODB;
30 CREATE TABLE tndb (a int) ENGINE = NDB;
31
32 SHOW CREATE TABLE tmyisam;
33 SHOW CREATE TABLE tinnodb;
34 --disable_warnings
35 SHOW CREATE TABLE tndb;
36 --enable_warnings
37
38
39 --echo [on master]
40
41
42 --echo ==== Single-engine transactions ====
43
44 --echo ---- autocommitted ----
45
46 SET AUTOCOMMIT = 1;
47
48 INSERT INTO tmyisam VALUES (0);
49 INSERT INTO tinnodb VALUES (1);
50 INSERT INTO tndb VALUES (2);
51
52 --echo ---- committed with BEGIN ----
53
54 BEGIN;
55 INSERT INTO tmyisam VALUES (3);
56 INSERT INTO tmyisam VALUES (4);
57 COMMIT;
58
59 BEGIN;
60 INSERT INTO tinnodb VALUES (5);
61 INSERT INTO tinnodb VALUES (6);
62 COMMIT;
63
64 BEGIN;
65 INSERT INTO tndb VALUES (7);
66 INSERT INTO tndb VALUES (8);
67 COMMIT;
68
69 --echo ---- rolled back with BEGIN ----
70
71 BEGIN;
72 INSERT INTO tmyisam VALUES (9);
73 INSERT INTO tmyisam VALUES (10);
74 ROLLBACK;
75
76 BEGIN;
77 INSERT INTO tinnodb VALUES (11);
78 INSERT INTO tinnodb VALUES (12);
79 ROLLBACK;
80
81 BEGIN;
82 INSERT INTO tndb VALUES (13);
83 INSERT INTO tndb VALUES (14);
84 ROLLBACK;
85
86
87 --echo ---- committed with AUTOCOMMIT = 0 ----
88
89 SET AUTOCOMMIT = 0;
90
91 INSERT INTO tmyisam VALUES (15);
92 INSERT INTO tmyisam VALUES (16);
93 COMMIT;
94
95 INSERT INTO tinnodb VALUES (17);
96 INSERT INTO tinnodb VALUES (18);
97 COMMIT;
98
99 INSERT INTO tndb VALUES (19);
100 INSERT INTO tndb VALUES (20);
101 COMMIT;
102
103 --echo ---- rolled back with AUTOCOMMIT = 0 ----
104
105 INSERT INTO tmyisam VALUES (21);
106 INSERT INTO tmyisam VALUES (22);
107 ROLLBACK;
108
109 INSERT INTO tinnodb VALUES (23);
110 INSERT INTO tinnodb VALUES (24);
111 ROLLBACK;
112
113 INSERT INTO tndb VALUES (25);
114 INSERT INTO tndb VALUES (26);
115 ROLLBACK;
116
117 SET AUTOCOMMIT = 1;
118
119
120 --echo ==== MyISAM + InnoDB ====
121
122 --echo ---- committed with BEGIN ----
123
124 BEGIN;
125 INSERT INTO tmyisam VALUES (27);
126 INSERT INTO tinnodb VALUES (28);
127 COMMIT;
128
129 BEGIN;
130 INSERT INTO tinnodb VALUES (29);
131 INSERT INTO tmyisam VALUES (30);
132 COMMIT;
133
134 --echo ---- rolled back with BEGIN ----
135
136 BEGIN;
137 INSERT INTO tmyisam VALUES (31);
138 INSERT INTO tinnodb VALUES (32);
139 ROLLBACK;
140
141 BEGIN;
142 INSERT INTO tinnodb VALUES (33);
143 INSERT INTO tmyisam VALUES (34);
144 ROLLBACK;
145
146 --echo ---- committed with AUTOCOMMIT = 0 ----
147
148 SET AUTOCOMMIT = 0;
149
150 INSERT INTO tmyisam VALUES (35);
151 INSERT INTO tinnodb VALUES (36);
152 COMMIT;
153
154 INSERT INTO tinnodb VALUES (37);
155 INSERT INTO tmyisam VALUES (38);
156 COMMIT;
157
158 --echo ---- rolled back with AUTOCOMMIT = 0 ----
159
160 INSERT INTO tmyisam VALUES (39);
161 INSERT INTO tinnodb VALUES (40);
162 ROLLBACK;
163
164 INSERT INTO tinnodb VALUES (41);
165 INSERT INTO tmyisam VALUES (42);
166 ROLLBACK;
167
168 SET AUTOCOMMIT = 1;
169
170
171 --echo ==== MyISAM + NDB ====
172
173 --echo ---- committed with BEGIN----
174
175 BEGIN;
176 INSERT INTO tmyisam VALUES (43);
177 INSERT INTO tndb VALUES (44);
178 COMMIT;
179
180 BEGIN;
181 INSERT INTO tndb VALUES (45);
182 INSERT INTO tmyisam VALUES (46);
183 COMMIT;
184
185 --echo ---- rolled back with BEGIN ----
186
187 BEGIN;
188 INSERT INTO tmyisam VALUES (47);
189 INSERT INTO tndb VALUES (48);
190 ROLLBACK;
191
192 BEGIN;
193 INSERT INTO tndb VALUES (49);
194 INSERT INTO tmyisam VALUES (50);
195 ROLLBACK;
196
197 --echo ---- committed with AUTOCOMMIT = 0 ----
198
199 SET AUTOCOMMIT = 0;
200
201 INSERT INTO tmyisam VALUES (51);
202 INSERT INTO tndb VALUES (52);
203 COMMIT;
204
205 INSERT INTO tndb VALUES (53);
206 INSERT INTO tmyisam VALUES (54);
207 COMMIT;
208
209 --echo ---- rolled back with AUTOCOMMIT = 0 ----
210
211 INSERT INTO tmyisam VALUES (55);
212 INSERT INTO tndb VALUES (56);
213 ROLLBACK;
214
215 INSERT INTO tndb VALUES (57);
216 INSERT INTO tmyisam VALUES (58);
217 ROLLBACK;
218
219 SET AUTOCOMMIT = 1;
220
221
222 --echo ==== InnoDB + NDB ====
223
224 --echo ---- committed with BEGIN ----
225
226 BEGIN;
227 INSERT INTO tinnodb VALUES (59);
228 INSERT INTO tndb VALUES (60);
229 COMMIT;
230
231 BEGIN;
232 INSERT INTO tndb VALUES (61);
233 INSERT INTO tinnodb VALUES (62);
234 COMMIT;
235
236 --echo ---- rolled back with BEGIN ----
237
238 BEGIN;
239 INSERT INTO tinnodb VALUES (63);
240 INSERT INTO tndb VALUES (64);
241 ROLLBACK;
242
243 BEGIN;
244 INSERT INTO tndb VALUES (65);
245 INSERT INTO tinnodb VALUES (66);
246 ROLLBACK;
247
248 --echo ---- committed with AUTOCOMMIT = 0 ----
249
250 SET AUTOCOMMIT = 0;
251
252 INSERT INTO tinnodb VALUES (67);
253 INSERT INTO tndb VALUES (68);
254 COMMIT;
255
256 INSERT INTO tndb VALUES (69);
257 INSERT INTO tinnodb VALUES (70);
258 COMMIT;
259
260 --echo ---- rolled back with AUTOCOMMIT = 0 ----
261
262 INSERT INTO tinnodb VALUES (71);
263 INSERT INTO tndb VALUES (72);
264 ROLLBACK;
265
266 INSERT INTO tndb VALUES (73);
267 INSERT INTO tinnodb VALUES (74);
268 ROLLBACK;
269
270 SET AUTOCOMMIT = 1;
271
272
273 --echo ==== MyISAM + InnoDB + NDB ====
274
275 --echo ---- committed with BEGIN ----
276
277 BEGIN;
278 INSERT INTO tmyisam VALUES (75);
279 INSERT INTO tinnodb VALUES (76);
280 INSERT INTO tndb VALUES (77);
281 COMMIT;
282
283 BEGIN;
284 INSERT INTO tmyisam VALUES (78);
285 INSERT INTO tndb VALUES (79);
286 INSERT INTO tinnodb VALUES (80);
287 COMMIT;
288
289 BEGIN;
290 INSERT INTO tinnodb VALUES (81);
291 INSERT INTO tmyisam VALUES (82);
292 INSERT INTO tndb VALUES (83);
293 COMMIT;
294
295 BEGIN;
296 INSERT INTO tinnodb VALUES (84);
297 INSERT INTO tndb VALUES (85);
298 INSERT INTO tmyisam VALUES (86);
299 COMMIT;
300
301 BEGIN;
302 INSERT INTO tndb VALUES (87);
303 INSERT INTO tmyisam VALUES (88);
304 INSERT INTO tinnodb VALUES (89);
305 COMMIT;
306
307 BEGIN;
308 INSERT INTO tndb VALUES (90);
309 INSERT INTO tinnodb VALUES (91);
310 INSERT INTO tmyisam VALUES (92);
311 COMMIT;
312
313 --echo ---- rolled back with BEGIN ----
314
315 BEGIN;
316 INSERT INTO tmyisam VALUES (93);
317 INSERT INTO tinnodb VALUES (94);
318 INSERT INTO tndb VALUES (95);
319 ROLLBACK;
320
321 BEGIN;
322 INSERT INTO tmyisam VALUES (96);
323 INSERT INTO tndb VALUES (97);
324 INSERT INTO tinnodb VALUES (98);
325 ROLLBACK;
326
327 BEGIN;
328 INSERT INTO tinnodb VALUES (99);
329 INSERT INTO tmyisam VALUES (100);
330 INSERT INTO tndb VALUES (101);
331 ROLLBACK;
332
333 BEGIN;
334 INSERT INTO tinnodb VALUES (102);
335 INSERT INTO tndb VALUES (103);
336 INSERT INTO tmyisam VALUES (104);
337 ROLLBACK;
338
339 BEGIN;
340 INSERT INTO tndb VALUES (105);
341 INSERT INTO tmyisam VALUES (106);
342 INSERT INTO tinnodb VALUES (107);
343 ROLLBACK;
344
345 BEGIN;
346 INSERT INTO tndb VALUES (108);
347 INSERT INTO tinnodb VALUES (109);
348 INSERT INTO tmyisam VALUES (110);
349 ROLLBACK;
350
351 --echo ---- committed with AUTOCOMMIT = 0 ----
352
353 SET AUTOCOMMIT = 0;
354
355 INSERT INTO tmyisam VALUES (111);
356 INSERT INTO tinnodb VALUES (112);
357 INSERT INTO tndb VALUES (113);
358 COMMIT;
359
360 INSERT INTO tmyisam VALUES (114);
361 INSERT INTO tndb VALUES (115);
362 INSERT INTO tinnodb VALUES (116);
363 COMMIT;
364
365 INSERT INTO tinnodb VALUES (117);
366 INSERT INTO tmyisam VALUES (118);
367 INSERT INTO tndb VALUES (119);
368 COMMIT;
369
370 INSERT INTO tinnodb VALUES (120);
371 INSERT INTO tndb VALUES (121);
372 INSERT INTO tmyisam VALUES (122);
373 COMMIT;
374
375 INSERT INTO tndb VALUES (123);
376 INSERT INTO tmyisam VALUES (124);
377 INSERT INTO tinnodb VALUES (125);
378 COMMIT;
379
380 INSERT INTO tndb VALUES (126);
381 INSERT INTO tinnodb VALUES (127);
382 INSERT INTO tmyisam VALUES (128);
383 COMMIT;
384
385 --echo ---- rolled back with AUTOCOMMIT = 0 ----
386
387 INSERT INTO tmyisam VALUES (129);
388 INSERT INTO tinnodb VALUES (130);
389 INSERT INTO tndb VALUES (131);
390 ROLLBACK;
391
392 INSERT INTO tmyisam VALUES (132);
393 INSERT INTO tndb VALUES (133);
394 INSERT INTO tinnodb VALUES (134);
395 ROLLBACK;
396
397 INSERT INTO tinnodb VALUES (135);
398 INSERT INTO tmyisam VALUES (136);
399 INSERT INTO tndb VALUES (137);
400 ROLLBACK;
401
402 INSERT INTO tinnodb VALUES (138);
403 INSERT INTO tndb VALUES (139);
404 INSERT INTO tmyisam VALUES (140);
405 ROLLBACK;
406
407 INSERT INTO tndb VALUES (141);
408 INSERT INTO tmyisam VALUES (142);
409 INSERT INTO tinnodb VALUES (143);
410 ROLLBACK;
411
412 INSERT INTO tndb VALUES (144);
413 INSERT INTO tinnodb VALUES (145);
414 INSERT INTO tmyisam VALUES (146);
415 ROLLBACK;
416
417 SET AUTOCOMMIT = 1;
418
419 --echo ---- Mixed statements Innodb ----
420
421 BEGIN;
422 INSERT INTO tndb VALUES (147);
423 INSERT INTO tinnodb SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
424 COMMIT;
425
426 INSERT INTO tndb VALUES (148);
427 BEGIN;
428 INSERT INTO tinnodb SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
429 INSERT INTO tndb VALUES (149);
430 COMMIT;
431
432 BEGIN;
433 INSERT INTO tndb VALUES (150);
434 INSERT INTO tmyisam SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
435 COMMIT;
436
437 INSERT INTO tndb VALUES (151);
438 BEGIN;
439 INSERT INTO tmyisam SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
440 INSERT INTO tndb VALUES (152);
441 COMMIT;
442
443 --echo ==== Verify the result ====
444
445 SELECT * FROM tmyisam ORDER BY a;
446 SELECT * FROM tinnodb ORDER BY a;
447 SELECT * FROM tndb ORDER BY a;
448
449 --echo [on slave]
450 --sync_slave_with_master
451
452 let $diff_tables= master:tmyisam, slave:tmyisam;
453 source include/diff_tables.inc;
454
455 let $diff_tables= master:tinnodb, slave:tinnodb;
456 source include/diff_tables.inc;
457
458 let $diff_tables= master:tndb, slave:tndb;
459 source include/diff_tables.inc;
460
461
462 --echo ==== Clean up ====
463
464 --echo [on master]
465 connection master;
466 DROP TABLE tmyisam, tinnodb, tndb;
467
468 --echo [on slave]
469 sync_slave_with_master;
470
471 --source include/rpl_end.inc