1 include/master-slave.inc
3 CREATE TABLE tmyisam (a int) ENGINE = MYISAM;
4 CREATE TABLE tinnodb (a int) ENGINE = INNODB;
5 CREATE TABLE tndb (a int) ENGINE = NDB;
6 SHOW CREATE TABLE tmyisam;
8 tmyisam CREATE TABLE `tmyisam` (
9 `a` int(11) DEFAULT NULL
10 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
11 SHOW CREATE TABLE tinnodb;
13 tinnodb CREATE TABLE `tinnodb` (
14 `a` int(11) DEFAULT NULL
15 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
16 SHOW CREATE TABLE tndb;
18 tndb CREATE TABLE `tndb` (
19 `a` int(11) DEFAULT NULL
20 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
22 ==== Single-engine transactions ====
23 ---- autocommitted ----
25 INSERT INTO tmyisam VALUES (0);
26 INSERT INTO tinnodb VALUES (1);
27 INSERT INTO tndb VALUES (2);
28 ---- committed with BEGIN ----
30 INSERT INTO tmyisam VALUES (3);
31 INSERT INTO tmyisam VALUES (4);
34 INSERT INTO tinnodb VALUES (5);
35 INSERT INTO tinnodb VALUES (6);
38 INSERT INTO tndb VALUES (7);
39 INSERT INTO tndb VALUES (8);
41 ---- rolled back with BEGIN ----
43 INSERT INTO tmyisam VALUES (9);
44 INSERT INTO tmyisam VALUES (10);
47 Warning 1196 Some non-transactional changed tables couldn't be rolled back
49 INSERT INTO tinnodb VALUES (11);
50 INSERT INTO tinnodb VALUES (12);
53 INSERT INTO tndb VALUES (13);
54 INSERT INTO tndb VALUES (14);
56 ---- committed with AUTOCOMMIT = 0 ----
58 INSERT INTO tmyisam VALUES (15);
59 INSERT INTO tmyisam VALUES (16);
61 INSERT INTO tinnodb VALUES (17);
62 INSERT INTO tinnodb VALUES (18);
64 INSERT INTO tndb VALUES (19);
65 INSERT INTO tndb VALUES (20);
67 ---- rolled back with AUTOCOMMIT = 0 ----
68 INSERT INTO tmyisam VALUES (21);
69 INSERT INTO tmyisam VALUES (22);
72 Warning 1196 Some non-transactional changed tables couldn't be rolled back
73 INSERT INTO tinnodb VALUES (23);
74 INSERT INTO tinnodb VALUES (24);
76 INSERT INTO tndb VALUES (25);
77 INSERT INTO tndb VALUES (26);
80 ==== MyISAM + InnoDB ====
81 ---- committed with BEGIN ----
83 INSERT INTO tmyisam VALUES (27);
84 INSERT INTO tinnodb VALUES (28);
87 INSERT INTO tinnodb VALUES (29);
88 INSERT INTO tmyisam VALUES (30);
90 ---- rolled back with BEGIN ----
92 INSERT INTO tmyisam VALUES (31);
93 INSERT INTO tinnodb VALUES (32);
96 Warning 1196 Some non-transactional changed tables couldn't be rolled back
98 INSERT INTO tinnodb VALUES (33);
99 INSERT INTO tmyisam VALUES (34);
102 Warning 1196 Some non-transactional changed tables couldn't be rolled back
103 ---- committed with AUTOCOMMIT = 0 ----
105 INSERT INTO tmyisam VALUES (35);
106 INSERT INTO tinnodb VALUES (36);
108 INSERT INTO tinnodb VALUES (37);
109 INSERT INTO tmyisam VALUES (38);
111 ---- rolled back with AUTOCOMMIT = 0 ----
112 INSERT INTO tmyisam VALUES (39);
113 INSERT INTO tinnodb VALUES (40);
116 Warning 1196 Some non-transactional changed tables couldn't be rolled back
117 INSERT INTO tinnodb VALUES (41);
118 INSERT INTO tmyisam VALUES (42);
121 Warning 1196 Some non-transactional changed tables couldn't be rolled back
123 ==== MyISAM + NDB ====
124 ---- committed with BEGIN----
126 INSERT INTO tmyisam VALUES (43);
127 INSERT INTO tndb VALUES (44);
130 INSERT INTO tndb VALUES (45);
131 INSERT INTO tmyisam VALUES (46);
133 ---- rolled back with BEGIN ----
135 INSERT INTO tmyisam VALUES (47);
136 INSERT INTO tndb VALUES (48);
139 Warning 1196 Some non-transactional changed tables couldn't be rolled back
141 INSERT INTO tndb VALUES (49);
142 INSERT INTO tmyisam VALUES (50);
145 Warning 1196 Some non-transactional changed tables couldn't be rolled back
146 ---- committed with AUTOCOMMIT = 0 ----
148 INSERT INTO tmyisam VALUES (51);
149 INSERT INTO tndb VALUES (52);
151 INSERT INTO tndb VALUES (53);
152 INSERT INTO tmyisam VALUES (54);
154 ---- rolled back with AUTOCOMMIT = 0 ----
155 INSERT INTO tmyisam VALUES (55);
156 INSERT INTO tndb VALUES (56);
159 Warning 1196 Some non-transactional changed tables couldn't be rolled back
160 INSERT INTO tndb VALUES (57);
161 INSERT INTO tmyisam VALUES (58);
164 Warning 1196 Some non-transactional changed tables couldn't be rolled back
166 ==== InnoDB + NDB ====
167 ---- committed with BEGIN ----
169 INSERT INTO tinnodb VALUES (59);
170 INSERT INTO tndb VALUES (60);
173 INSERT INTO tndb VALUES (61);
174 INSERT INTO tinnodb VALUES (62);
176 ---- rolled back with BEGIN ----
178 INSERT INTO tinnodb VALUES (63);
179 INSERT INTO tndb VALUES (64);
182 INSERT INTO tndb VALUES (65);
183 INSERT INTO tinnodb VALUES (66);
185 ---- committed with AUTOCOMMIT = 0 ----
187 INSERT INTO tinnodb VALUES (67);
188 INSERT INTO tndb VALUES (68);
190 INSERT INTO tndb VALUES (69);
191 INSERT INTO tinnodb VALUES (70);
193 ---- rolled back with AUTOCOMMIT = 0 ----
194 INSERT INTO tinnodb VALUES (71);
195 INSERT INTO tndb VALUES (72);
197 INSERT INTO tndb VALUES (73);
198 INSERT INTO tinnodb VALUES (74);
201 ==== MyISAM + InnoDB + NDB ====
202 ---- committed with BEGIN ----
204 INSERT INTO tmyisam VALUES (75);
205 INSERT INTO tinnodb VALUES (76);
206 INSERT INTO tndb VALUES (77);
209 INSERT INTO tmyisam VALUES (78);
210 INSERT INTO tndb VALUES (79);
211 INSERT INTO tinnodb VALUES (80);
214 INSERT INTO tinnodb VALUES (81);
215 INSERT INTO tmyisam VALUES (82);
216 INSERT INTO tndb VALUES (83);
219 INSERT INTO tinnodb VALUES (84);
220 INSERT INTO tndb VALUES (85);
221 INSERT INTO tmyisam VALUES (86);
224 INSERT INTO tndb VALUES (87);
225 INSERT INTO tmyisam VALUES (88);
226 INSERT INTO tinnodb VALUES (89);
229 INSERT INTO tndb VALUES (90);
230 INSERT INTO tinnodb VALUES (91);
231 INSERT INTO tmyisam VALUES (92);
233 ---- rolled back with BEGIN ----
235 INSERT INTO tmyisam VALUES (93);
236 INSERT INTO tinnodb VALUES (94);
237 INSERT INTO tndb VALUES (95);
240 Warning 1196 Some non-transactional changed tables couldn't be rolled back
242 INSERT INTO tmyisam VALUES (96);
243 INSERT INTO tndb VALUES (97);
244 INSERT INTO tinnodb VALUES (98);
247 Warning 1196 Some non-transactional changed tables couldn't be rolled back
249 INSERT INTO tinnodb VALUES (99);
250 INSERT INTO tmyisam VALUES (100);
251 INSERT INTO tndb VALUES (101);
254 Warning 1196 Some non-transactional changed tables couldn't be rolled back
256 INSERT INTO tinnodb VALUES (102);
257 INSERT INTO tndb VALUES (103);
258 INSERT INTO tmyisam VALUES (104);
261 Warning 1196 Some non-transactional changed tables couldn't be rolled back
263 INSERT INTO tndb VALUES (105);
264 INSERT INTO tmyisam VALUES (106);
265 INSERT INTO tinnodb VALUES (107);
268 Warning 1196 Some non-transactional changed tables couldn't be rolled back
270 INSERT INTO tndb VALUES (108);
271 INSERT INTO tinnodb VALUES (109);
272 INSERT INTO tmyisam VALUES (110);
275 Warning 1196 Some non-transactional changed tables couldn't be rolled back
276 ---- committed with AUTOCOMMIT = 0 ----
278 INSERT INTO tmyisam VALUES (111);
279 INSERT INTO tinnodb VALUES (112);
280 INSERT INTO tndb VALUES (113);
282 INSERT INTO tmyisam VALUES (114);
283 INSERT INTO tndb VALUES (115);
284 INSERT INTO tinnodb VALUES (116);
286 INSERT INTO tinnodb VALUES (117);
287 INSERT INTO tmyisam VALUES (118);
288 INSERT INTO tndb VALUES (119);
290 INSERT INTO tinnodb VALUES (120);
291 INSERT INTO tndb VALUES (121);
292 INSERT INTO tmyisam VALUES (122);
294 INSERT INTO tndb VALUES (123);
295 INSERT INTO tmyisam VALUES (124);
296 INSERT INTO tinnodb VALUES (125);
298 INSERT INTO tndb VALUES (126);
299 INSERT INTO tinnodb VALUES (127);
300 INSERT INTO tmyisam VALUES (128);
302 ---- rolled back with AUTOCOMMIT = 0 ----
303 INSERT INTO tmyisam VALUES (129);
304 INSERT INTO tinnodb VALUES (130);
305 INSERT INTO tndb VALUES (131);
308 Warning 1196 Some non-transactional changed tables couldn't be rolled back
309 INSERT INTO tmyisam VALUES (132);
310 INSERT INTO tndb VALUES (133);
311 INSERT INTO tinnodb VALUES (134);
314 Warning 1196 Some non-transactional changed tables couldn't be rolled back
315 INSERT INTO tinnodb VALUES (135);
316 INSERT INTO tmyisam VALUES (136);
317 INSERT INTO tndb VALUES (137);
320 Warning 1196 Some non-transactional changed tables couldn't be rolled back
321 INSERT INTO tinnodb VALUES (138);
322 INSERT INTO tndb VALUES (139);
323 INSERT INTO tmyisam VALUES (140);
326 Warning 1196 Some non-transactional changed tables couldn't be rolled back
327 INSERT INTO tndb VALUES (141);
328 INSERT INTO tmyisam VALUES (142);
329 INSERT INTO tinnodb VALUES (143);
332 Warning 1196 Some non-transactional changed tables couldn't be rolled back
333 INSERT INTO tndb VALUES (144);
334 INSERT INTO tinnodb VALUES (145);
335 INSERT INTO tmyisam VALUES (146);
338 Warning 1196 Some non-transactional changed tables couldn't be rolled back
340 ---- Mixed statements Innodb ----
342 INSERT INTO tndb VALUES (147);
343 INSERT INTO tinnodb SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
345 INSERT INTO tndb VALUES (148);
347 INSERT INTO tinnodb SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
348 INSERT INTO tndb VALUES (149);
351 INSERT INTO tndb VALUES (150);
352 INSERT INTO tmyisam SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
354 INSERT INTO tndb VALUES (151);
356 INSERT INTO tmyisam SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
357 INSERT INTO tndb VALUES (152);
359 ==== Verify the result ====
360 SELECT * FROM tmyisam ORDER BY a;
413 SELECT * FROM tinnodb ORDER BY a;
442 SELECT * FROM tndb ORDER BY a;
476 include/diff_tables.inc [master:tmyisam, slave:tmyisam]
477 include/diff_tables.inc [master:tinnodb, slave:tinnodb]
478 include/diff_tables.inc [master:tndb, slave:tndb]
481 DROP TABLE tmyisam, tinnodb, tndb;