1 ###################################################################################
2 # This test checks if transactions that mixes transactional and non-transactional
3 # tables are correctly handled in statement mode. In an nutshell, we have what
6 # 1) "B T T C" generates in binlog the "B T T C" entries.
8 # 2) "B T T R" generates in binlog an "empty" entry.
10 # 3) "B T N C" generates in binlog the "B T N C" entries.
12 # 4) "B T N R" generates in binlog the "B T N R" entries.
14 # 5) "T" generates in binlog the "B T C" entry.
16 # 6) "N" generates in binlog the "N" entry.
18 # 7) "M" generates in binglog the "B M C" entries.
20 # 8) "B N N T C" generates in binglog the "N N B T C" entries.
22 # 9) "B N N T R" generates in binlog the "N N B T R" entries.
24 # 10) "B N N C" generates in binglog the "N N" entries.
26 # 11) "B N N R" generates in binlog the "N N" entries.
28 # 12) "B M T C" generates in the binlog the "B M T C" entries.
30 # 13) "B M T R" generates in the binlog the "B M T R" entries.
31 ###################################################################################
33 --echo ###################################################################################
34 --echo # CONFIGURATION
35 --echo ###################################################################################
39 CREATE TABLE nt_1 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
40 CREATE TABLE nt_2 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
41 CREATE TABLE nt_3 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
42 CREATE TABLE nt_4 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
43 CREATE TABLE tt_1 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
44 CREATE TABLE tt_2 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
45 CREATE TABLE tt_3 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
46 CREATE TABLE tt_4 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
52 CREATE TABLE nt_1 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
53 CREATE TABLE nt_2 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
54 CREATE TABLE nt_3 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
55 CREATE TABLE nt_4 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
56 CREATE TABLE tt_1 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
57 CREATE TABLE tt_2 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
58 CREATE TABLE tt_3 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
59 CREATE TABLE tt_4 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
66 CREATE FUNCTION f1 () RETURNS VARCHAR(64)
71 CREATE FUNCTION f2 () RETURNS VARCHAR(64)
76 CREATE PROCEDURE pc_i_tt_3 (IN x INT, IN y VARCHAR(64))
78 INSERT INTO tt_3 VALUES (y,x,x);
81 CREATE TRIGGER tr_i_tt_3_to_nt_3 BEFORE INSERT ON tt_3 FOR EACH ROW
83 INSERT INTO nt_3 VALUES (NEW.a, NEW.b, NEW.c);
86 CREATE TRIGGER tr_i_nt_4_to_tt_4 BEFORE INSERT ON nt_4 FOR EACH ROW
88 INSERT INTO tt_4 VALUES (NEW.a, NEW.b, NEW.c);
93 --echo ###################################################################################
94 --echo # MIXING TRANSACTIONAL and NON-TRANSACTIONAL TABLES
95 --echo ###################################################################################
98 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
100 --echo #1) "B T T C" generates in binlog the "B T T C" entries.
103 INSERT INTO tt_1 VALUES ("new text 4", 4, "new text 4");
104 INSERT INTO tt_2 VALUES ("new text 4", 4, "new text 4");
107 --source include/show_binlog_events.inc
113 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
115 --echo #1.e) "B T T C" with error in T generates in binlog the "B T T C" entries.
117 INSERT INTO tt_1 VALUES ("new text -2", -2, "new text -2");
120 INSERT INTO tt_1 VALUES ("new text -1", -1, "new text -1"), ("new text -2", -2, "new text -2");
121 INSERT INTO tt_2 VALUES ("new text -3", -3, "new text -3");
125 INSERT INTO tt_2 VALUES ("new text -5", -5, "new text -5");
127 INSERT INTO tt_2 VALUES ("new text -4", -4, "new text -4"), ("new text -5", -5, "new text -5");
130 --source include/show_binlog_events.inc
136 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
138 --echo #2) "B T T R" generates in binlog an "empty" entry.
141 INSERT INTO tt_1 VALUES ("new text 5", 5, "new text 5");
142 INSERT INTO tt_2 VALUES ("new text 5", 5, "new text 5");
145 --source include/show_binlog_events.inc
151 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
153 --echo #2.e) "B T T R" with error in T generates in binlog an "empty" entry.
155 INSERT INTO tt_1 VALUES ("new text -7", -7, "new text -7");
158 INSERT INTO tt_1 VALUES ("new text -6", -6, "new text -6"), ("new text -7", -7, "new text -7");
159 INSERT INTO tt_2 VALUES ("new text -8", -8, "new text -8");
163 INSERT INTO tt_2 VALUES ("new text -10", -10, "new text -10");
165 INSERT INTO tt_2 VALUES ("new text -9", -9, "new text -9"), ("new text -10", -10, "new text -10");
168 --source include/show_binlog_events.inc
174 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
176 --echo #3) "B T N C" generates in binlog the "B T N C" entries.
179 INSERT INTO tt_1 VALUES ("new text 6", 6, "new text 6");
180 INSERT INTO nt_1 VALUES ("new text 6", 6, "new text 6");
183 --source include/show_binlog_events.inc
189 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
191 --echo #3.e) "B T N C" with error in either T or N generates in binlog the "B T N C" entries.
193 INSERT INTO tt_1 VALUES ("new text -12", -12, "new text -12");
196 INSERT INTO tt_1 VALUES ("new text -11", -11, "new text -11"), ("new text -12", -12, "new text -12");
197 INSERT INTO nt_1 VALUES ("new text -13", -13, "new text -13");
201 INSERT INTO tt_1 VALUES ("new text -14", -14, "new text -14");
202 INSERT INTO nt_1 VALUES ("new text -16", -16, "new text -16");
204 INSERT INTO nt_1 VALUES ("new text -15", -15, "new text -15"), ("new text -16", -16, "new text -16");
207 --source include/show_binlog_events.inc
213 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
215 --echo #4) "B T N R" generates in binlog the "B T N R" entries.
218 INSERT INTO tt_1 VALUES ("new text 7", 7, "new text 7");
219 INSERT INTO nt_1 VALUES ("new text 7", 7, "new text 7");
222 --source include/show_binlog_events.inc
228 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
230 --echo #4.e) "B T N R" with error in either T or N generates in binlog the "B T N R" entries.
232 INSERT INTO tt_1 VALUES ("new text -17", -17, "new text -17");
235 INSERT INTO tt_1 VALUES ("new text -16", -16, "new text -16"), ("new text -17", -17, "new text -17");
236 INSERT INTO nt_1 VALUES ("new text -18", -18, "new text -18");
240 INSERT INTO tt_1 VALUES ("new text -19", -19, "new text -19");
241 INSERT INTO nt_1 VALUES ("new text -21", -21, "new text -21");
243 INSERT INTO nt_1 VALUES ("new text -20", -20, "new text -20"), ("new text -21", -21, "new text -21");
246 --source include/show_binlog_events.inc
252 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
254 --echo #5) "T" generates in binlog the "B T C" entry.
256 INSERT INTO tt_1 VALUES ("new text 8", 8, "new text 8");
258 --source include/show_binlog_events.inc
264 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
266 --echo #5.e) "T" with error in T generates in binlog an "empty" entry.
268 INSERT INTO tt_1 VALUES ("new text -1", -1, "new text -1");
270 INSERT INTO tt_1 VALUES ("new text -1", -1, "new text -1"), ("new text -22", -22, "new text -22");
272 INSERT INTO tt_1 VALUES ("new text -23", -23, "new text -23"), ("new text -1", -1, "new text -1");
274 --source include/show_binlog_events.inc
280 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
282 --echo #6) "N" generates in binlog the "N" entry.
284 INSERT INTO nt_1 VALUES ("new text 9", 9, "new text 9");
286 --source include/show_binlog_events.inc
292 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
294 --echo #6.e) "N" with error in N generates in binlog an empty entry if the error
295 --echo # happens in the first tuple. Otherwise, generates the "N" entry and
296 --echo # the error is appended.
298 INSERT INTO nt_1 VALUES ("new text -1", -1, "new text -1");
300 INSERT INTO nt_1 VALUES ("new text -1", -1, "new text -1");
302 INSERT INTO nt_1 VALUES ("new text -24", -24, "new text -24"), ("new text -1", -1, "new text -1");
304 --source include/show_binlog_events.inc
310 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
312 --echo #7) "M" generates in binglog the "B M C" entries.
317 INSERT INTO nt_1 SELECT * FROM tt_1;
321 INSERT INTO tt_1 SELECT * FROM nt_1;
323 INSERT INTO tt_3 VALUES ("new text 000", 000, '');
325 INSERT INTO tt_3 VALUES("new text 100", 100, f1());
327 INSERT INTO nt_4 VALUES("new text 100", 100, f1());
329 INSERT INTO tt_3 VALUES("new text 200", 200, f2());
331 INSERT INTO nt_4 VALUES ("new text 300", 300, '');
333 INSERT INTO nt_4 VALUES ("new text 400", 400, f1());
335 INSERT INTO nt_4 VALUES ("new text 500", 500, f2());
337 CALL pc_i_tt_3(600, "Testing...");
339 UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a= "new text 1", nt_4.a= "new text 1", tt_3.a= "new text 1", tt_4.a= "new text 1" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
341 UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a= "new text 2", tt_4.a= "new text 2", nt_3.a= "new text 2", nt_4.a = "new text 2" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
343 UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 3", nt_3.a= "new text 3", nt_4.a= "new text 3", tt_4.a = "new text 3" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
345 UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 4", nt_3.a= "new text 4", nt_4.a= "new text 4", tt_4.a = "new text 4" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
347 --source include/show_binlog_events.inc
353 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
355 --echo #7.e) "M" with error in M generates in binglog the "B M R" entries.
358 INSERT INTO nt_3 VALUES ("new text -26", -26, '');
361 INSERT INTO tt_3 VALUES ("new text -25", -25, ''), ("new text -26", -26, '');
364 INSERT INTO tt_4 VALUES ("new text -26", -26, '');
367 INSERT INTO nt_4 VALUES ("new text -25", -25, ''), ("new text -26", -26, '');
370 --source include/show_binlog_events.inc
376 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
378 --echo #8) "B N N T C" generates in binglog the "N N B T C" entries.
381 INSERT INTO nt_1 VALUES ("new text 10", 10, "new text 10");
382 INSERT INTO nt_2 VALUES ("new text 10", 10, "new text 10");
383 INSERT INTO tt_1 VALUES ("new text 10", 10, "new text 10");
386 --source include/show_binlog_events.inc
393 --echo #8.e) "B N N T R" See 6.e and 9.e.
400 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
402 --echo #9) "B N N T R" generates in binlog the "N N B T R" entries.
405 INSERT INTO nt_1 VALUES ("new text 11", 11, "new text 11");
406 INSERT INTO nt_2 VALUES ("new text 11", 11, "new text 11");
407 INSERT INTO tt_1 VALUES ("new text 11", 11, "new text 11");
410 --source include/show_binlog_events.inc
416 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
418 --echo #9.e) "B N N T R" with error in N generates in binlog the "N N B T R" entries.
421 INSERT INTO nt_1 VALUES ("new text -25", -25, "new text -25");
422 INSERT INTO nt_2 VALUES ("new text -25", -25, "new text -25");
424 INSERT INTO nt_2 VALUES ("new text -26", -26, "new text -26"), ("new text -25", -25, "new text -25");
425 INSERT INTO tt_1 VALUES ("new text -27", -27, "new text -27");
428 --source include/show_binlog_events.inc
434 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
436 --echo #10) "B N N C" generates in binglog the "N N" entries.
439 INSERT INTO nt_1 VALUES ("new text 12", 12, "new text 12");
440 INSERT INTO nt_2 VALUES ("new text 12", 12, "new text 12");
443 --source include/show_binlog_events.inc
450 --echo #10.e) "B N N C" See 6.e and 9.e.
457 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
459 --echo #11) "B N N R" generates in binlog the "N N" entries.
462 INSERT INTO nt_1 VALUES ("new text 13", 13, "new text 13");
463 INSERT INTO nt_2 VALUES ("new text 13", 13, "new text 13");
466 --source include/show_binlog_events.inc
473 --echo #11.e) "B N N R" See 6.e and 9.e.
480 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
482 --echo #12) "B M T C" generates in the binlog the "B M T C" entries.
486 INSERT INTO nt_1 SELECT * FROM tt_1;
487 INSERT INTO tt_2 VALUES ("new text 14", 14, "new text 14");
492 INSERT INTO tt_1 SELECT * FROM nt_1;
493 INSERT INTO tt_2 VALUES ("new text 15", 15, "new text 15");
497 INSERT INTO tt_3 VALUES ("new text 700", 700, '');
498 INSERT INTO tt_1 VALUES ("new text 800", 800, '');
502 INSERT INTO tt_3 VALUES("new text 900", 900, f1());
503 INSERT INTO tt_1 VALUES ("new text 1000", 1000, '');
507 INSERT INTO tt_3 VALUES(1100, 1100, f2());
508 INSERT INTO tt_1 VALUES ("new text 1200", 1200, '');
512 INSERT INTO nt_4 VALUES ("new text 1300", 1300, '');
513 INSERT INTO tt_1 VALUES ("new text 1400", 1400, '');
517 INSERT INTO nt_4 VALUES("new text 1500", 1500, f1());
518 INSERT INTO tt_1 VALUES ("new text 1600", 1600, '');
522 INSERT INTO nt_4 VALUES("new text 1700", 1700, f2());
523 INSERT INTO tt_1 VALUES ("new text 1800", 1800, '');
527 CALL pc_i_tt_3(1900, "Testing...");
528 INSERT INTO tt_1 VALUES ("new text 2000", 2000, '');
532 UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a= "new text 5", nt_4.a= "new text 5", tt_3.a= "new text 5", tt_4.a= "new text 5" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
533 INSERT INTO tt_1 VALUES ("new text 2100", 2100, '');
537 UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a= "new text 6", tt_4.a= "new text 6", nt_3.a= "new text 6", nt_4.a = "new text 6" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
538 INSERT INTO tt_1 VALUES ("new text 2200", 2200, '');
542 UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 7", nt_3.a= "new text 7", nt_4.a= "new text 7", tt_4.a = "new text 7" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
543 INSERT INTO tt_1 VALUES ("new text 2300", 2300, '');
547 UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 8", nt_3.a= "new text 8", nt_4.a= "new text 8", tt_4.a = "new text 8" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
548 INSERT INTO tt_1 VALUES ("new text 2400", 2400, '');
551 --source include/show_binlog_events.inc
557 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
559 --echo #12.e) "B M T C" with error in M generates in the binlog the "B M T C" entries.
562 --echo # There is a bug in the slave that needs to be fixed before enabling
563 --echo # this part of the test. A bug report will be filed referencing this
567 INSERT INTO nt_3 VALUES ("new text -28", -28, '');
569 INSERT INTO tt_3 VALUES ("new text -27", -27, ''), ("new text -28", -28, '');
570 INSERT INTO tt_1 VALUES ("new text -27", -27, '');
574 INSERT INTO tt_4 VALUES ("new text -28", -28, '');
576 INSERT INTO nt_4 VALUES ("new text -27", -27, ''), ("new text -28", -28, '');
577 INSERT INTO tt_1 VALUES ("new text -28", -28, '');
580 --source include/show_binlog_events.inc
586 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
588 --echo #13) "B M T R" generates in the binlog the "B M T R" entries
593 INSERT INTO nt_1 SELECT * FROM tt_1;
594 INSERT INTO tt_2 VALUES ("new text 17", 17, "new text 17");
599 INSERT INTO tt_1 SELECT * FROM nt_1;
600 INSERT INTO tt_2 VALUES ("new text 18", 18, "new text 18");
602 INSERT INTO tt_1 SELECT * FROM nt_1;
605 INSERT INTO tt_3 VALUES ("new text 2500", 2500, '');
606 INSERT INTO tt_1 VALUES ("new text 2600", 2600, '');
610 INSERT INTO tt_3 VALUES("new text 2700", 2700, f1());
611 INSERT INTO tt_1 VALUES ("new text 2800", 2800, '');
615 INSERT INTO tt_3 VALUES(2900, 2900, f2());
616 INSERT INTO tt_1 VALUES ("new text 3000", 3000, '');
620 INSERT INTO nt_4 VALUES ("new text 3100", 3100, '');
621 INSERT INTO tt_1 VALUES ("new text 3200", 3200, '');
625 INSERT INTO nt_4 VALUES("new text 3300", 3300, f1());
626 INSERT INTO tt_1 VALUES ("new text 3400", 3400, '');
630 INSERT INTO nt_4 VALUES("new text 3500", 3500, f2());
631 INSERT INTO tt_1 VALUES ("new text 3600", 3600, '');
635 CALL pc_i_tt_3(3700, "Testing...");
636 INSERT INTO tt_1 VALUES ("new text 3700", 3700, '');
640 UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a= "new text 9", nt_4.a= "new text 9", tt_3.a= "new text 9", tt_4.a= "new text 9" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
641 INSERT INTO tt_1 VALUES ("new text 3800", 3800, '');
645 UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a= "new text 10", tt_4.a= "new text 10", nt_3.a= "new text 10", nt_4.a = "new text 10" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
646 INSERT INTO tt_1 VALUES ("new text 3900", 3900, '');
650 UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 11", nt_3.a= "new text 11", nt_4.a= "new text 11", tt_4.a = "new text 11" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
651 INSERT INTO tt_1 VALUES ("new text 4000", 4000, '');
655 UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 12", nt_3.a= "new text 12", nt_4.a= "new text 12", tt_4.a = "new text 12" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100;
656 INSERT INTO tt_1 VALUES ("new text 4100", 4100, '');
659 --source include/show_binlog_events.inc
665 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
667 --echo #13.e) "B M T R" with error in M generates in the binlog the "B M T R" entries.
671 INSERT INTO nt_3 VALUES ("new text -30", -30, '');
673 INSERT INTO tt_3 VALUES ("new text -29", -29, ''), ("new text -30", -30, '');
674 INSERT INTO tt_1 VALUES ("new text -30", -30, '');
678 INSERT INTO tt_4 VALUES ("new text -30", -30, '');
680 INSERT INTO nt_4 VALUES ("new text -29", -29, ''), ("new text -30", -30, '');
681 INSERT INTO tt_1 VALUES ("new text -31", -31, '');
684 --source include/show_binlog_events.inc
687 sync_slave_with_master;
689 --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/test-master.sql
690 --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/test-slave.sql
691 --diff_files $MYSQLTEST_VARDIR/tmp/test-master.sql $MYSQLTEST_VARDIR/tmp/test-slave.sql
693 --echo ###################################################################################
695 --echo ###################################################################################
706 DROP PROCEDURE pc_i_tt_3;
710 sync_slave_with_master;