1 # Check that binlog is ok when a transaction mixes updates to InnoDB and
3 # It would be nice to make this a replication test, but in 4.0 the
4 # slave is always with --skip-innodb in the testsuite. I (Guilhem) however
5 # did some tests manually on a slave; tables are replicated fine and
6 # Exec_Master_Log_Pos advances as expected.
8 -- source include/have_log_bin.inc
9 -- source include/have_innodb.inc
13 drop table if exists t1, t2;
16 connect (con1,localhost,root,,);
17 connect (con2,localhost,root,,);
20 create table t1 (a int) engine=innodb;
21 create table t2 (a int) engine=myisam;
26 insert into t1 values(1);
27 insert into t2 select * from t1;
30 source include/show_binlog_events.inc;
37 insert into t1 values(2);
38 insert into t2 select * from t1;
39 # should say some changes to non-transact1onal tables couldn't be rolled back
42 source include/show_binlog_events.inc;
49 insert into t1 values(3);
50 savepoint my_savepoint;
51 insert into t1 values(4);
52 insert into t2 select * from t1;
53 rollback to savepoint my_savepoint;
56 source include/show_binlog_events.inc;
63 insert into t1 values(5);
64 savepoint my_savepoint;
65 insert into t1 values(6);
66 insert into t2 select * from t1;
67 rollback to savepoint my_savepoint;
68 insert into t1 values(7);
70 select a from t1 order by a; # check that savepoints work :)
72 source include/show_binlog_events.inc;
74 # and when ROLLBACK is not explicit?
79 select get_lock("a",10);
81 insert into t1 values(8);
82 insert into t2 select * from t1;
86 # We want to SHOW BINLOG EVENTS, to know what was logged. But there is no
87 # guarantee that logging of the terminated con1 has been done yet (it may not
88 # even be started, so con1 may have not even attempted to lock the binlog yet;
89 # so SHOW BINLOG EVENTS may come before con1 does the loggin. To be sure that
90 # logging has been done, we use a user lock.
91 select get_lock("a",10);
92 source include/show_binlog_events.inc;
94 # and when not in a transact1on?
99 insert into t1 values(9);
100 insert into t2 select * from t1;
102 source include/show_binlog_events.inc;
104 # Check that when the query updat1ng the MyISAM table is the first in the
105 # transaction, we log it immediately.
110 insert into t1 values(10); # first make t1 non-empty
112 insert into t2 select * from t1;
113 source include/show_binlog_events.inc;
114 insert into t1 values(11);
117 source include/show_binlog_events.inc;
119 # Check that things work like before this BEGIN/ROLLBACK code was added,
122 alter table t2 engine=INNODB;
129 insert into t1 values(12);
130 insert into t2 select * from t1;
133 source include/show_binlog_events.inc;
140 insert into t1 values(13);
141 insert into t2 select * from t1;
144 source include/show_binlog_events.inc;
151 insert into t1 values(14);
152 savepoint my_savepoint;
153 insert into t1 values(15);
154 insert into t2 select * from t1;
155 rollback to savepoint my_savepoint;
158 source include/show_binlog_events.inc;
165 insert into t1 values(16);
166 savepoint my_savepoint;
167 insert into t1 values(17);
168 insert into t2 select * from t1;
169 rollback to savepoint my_savepoint;
170 insert into t1 values(18);
172 select a from t1 order by a; # check that savepoints work :)
174 source include/show_binlog_events.inc;
176 # Test for BUG#5714, where a MyISAM update in the transaction used to
177 # release row-level locks in InnoDB
179 connect (con3,localhost,root,,);
185 alter table t2 engine=MyISAM;
187 insert into t1 values (1);
189 select * from t1 for update;
192 select (@before:=unix_timestamp())*0; # always give repeatable output
194 send select * from t1 for update;
197 insert into t2 values (20);
202 select (@after:=unix_timestamp())*0; # always give repeatable output
203 # verify that innodb_lock_wait_timeout was exceeded. When there was
204 # the bug, the reap would return immediately after the insert into t2.
205 select (@after-@before) >= 2;
210 # test for BUG#7947 - DO RELEASE_LOCK() not written to binlog on rollback in
211 # the middle of a transaction
215 create temporary table ti (a int) engine=innodb;
217 insert into ti values(1);
219 create temporary table t1 (a int) engine=myisam;
221 insert t1 values (1);
223 create table t0 (n int);
224 insert t0 select * from t1;
226 insert into t0 select GET_LOCK("lock1",null);
228 create table t2 (n int) engine=innodb;
229 insert into t2 values (3);
232 select get_lock("lock1",60);
233 source include/show_binlog_events.inc;
234 do release_lock("lock1");
240 # Test behaviour of CREATE ... SELECT when mixing MyISAM and InnoDB tables
244 CREATE TABLE t1 (a int, b int) engine=myisam;
246 INSERT INTO t1 values (1,1),(1,2);
248 CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1;
249 # This should give warning
250 DROP TABLE if exists t2;
251 INSERT INTO t1 values (3,3);
253 CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1;
255 # This should give warning
256 DROP TABLE IF EXISTS t2;
258 CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
259 INSERT INTO t1 VALUES (4,4);
261 CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
264 INSERT INTO t1 VALUES (5,5);
266 INSERT INTO t2 select * from t1;
270 INSERT INTO t1 values (6,6);
271 CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ;
272 INSERT INTO t1 values (7,7);
274 INSERT INTO t1 values (8,8);
276 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
278 INSERT INTO t1 values (9,9);
280 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
284 INSERT INTO t1 values (10,10);
286 INSERT INTO t2 select * from t1;
288 INSERT INTO t2 values (100,100);
290 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
292 INSERT INTO t2 values (101,101);
294 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
298 source include/show_binlog_events.inc;
300 # Test for BUG#16559 (ROLLBACK should always have a zero error code in
301 # binlog). Has to be here and not earlier, as the SELECTs influence
302 # XIDs differently between normal and ps-protocol (and SHOW BINLOG
303 # EVENTS above read XIDs).
305 connect (con4,localhost,root,,);
308 create table t1 (a int) engine=innodb;
309 create table t2 (a int) engine=myisam;
310 select get_lock("a",10);
312 insert into t1 values(8);
313 insert into t2 select * from t1;
317 select get_lock("a",10); # wait for rollback to finish
320 let $MYSQLD_DATADIR= `select @@datadir`;
321 # we check that the error code of the "ROLLBACK" event is 0 and not
322 # ER_SERVER_SHUTDOWN (i.e. disconnection just rolls back transaction
323 # and does not make slave to stop)
324 if (`select @@binlog_format = 'ROW'`)
326 --exec $MYSQL_BINLOG --start-position=524 $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output
329 if (`select @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`)
331 --exec $MYSQL_BINLOG --start-position=555 $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output
334 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
336 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output"))
338 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
340 @a like "%#%error_code=0%ROLLBACK\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR
341 @a like "%#%error_code=0%ROLLBACK\\r\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%",
342 @a not like "%#%error_code=%error_code=%";
346 # Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
347 # bug #28960 non-trans temp table changes with insert .. select
348 # not binlogged after rollback
350 # testing appearence of insert into temp_table in binlog.
351 # There are two branches of execution that require different setup.
357 create temporary table tt (a int unique);
358 create table ti (a int) engine=innodb;
364 insert into ti values (1);
365 insert into ti values (2) ;
366 insert into tt select * from ti;
371 select count(*) from tt /* 2 */;
372 source include/show_binlog_events.inc;
373 select count(*) from ti /* zero */;
374 insert into ti select * from tt;
375 select * from ti /* that is what slave would miss - a bug */;
378 ## send_error() branch
380 delete from tt where a=1;
386 insert into ti values (1);
387 insert into ti values (2) /* to make the dup error in the following */;
389 insert into tt select * from ti /* one affected and error */;
394 source include/show_binlog_events.inc;
395 select count(*) from ti /* zero */;
396 insert into ti select * from tt;
397 select * from tt /* that is what otherwise slave missed - the bug */;
403 # Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
405 # Testing asserts: if there is a side effect of modifying non-transactional
406 # table thd->no_trans_update.stmt must be TRUE;
407 # the assert is active with debug build
411 drop function if exists bug27417;
412 drop table if exists t1,t2;
415 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
417 CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
420 create function bug27417(n int)
423 insert into t1 values (null);
432 insert into t2 values (bug27417(1));
433 insert into t2 select bug27417(2);
437 insert into t2 values (bug27417(2));
438 source include/show_binlog_events.inc; /* only (!) with fixes for #23333 will show there is the query */;
439 select count(*) from t1 /* must be 3 */;
442 select count(*) from t2;
443 delete from t2 where a=bug27417(3);
444 select count(*) from t2 /* nothing got deleted */;
445 source include/show_binlog_events.inc; /* the query must be in regardless of #23333 */;
446 select count(*) from t1 /* must be 5 */;
449 delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
451 select count(*) from t1 /* must be 7 */;
453 # function bug27417 remains for the following testing of bug#23333
457 # Bug#23333 using the patch (and the test) for bug#27471
459 # throughout the bug tests
460 # t1 - non-trans side effects gatherer;
461 # t2 - transactional table;
464 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
465 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
466 CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
467 CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
468 CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
477 insert into t2 values (1);
483 insert into t2 values (bug27417(1));
487 source include/show_binlog_events.inc; /* the output must denote there is the query */;
488 select count(*) from t1 /* must be 1 */;
497 insert into t2 values (2);
503 insert into t2 select bug27417(1) union select bug27417(2);
507 source include/show_binlog_events.inc; /* the output must denote there is the query */;
508 select count(*) from t1 /* must be 2 */;
511 # UPDATE inc multi-update
516 insert into t3 values (1,1),(2,3),(3,4);
521 update t3 set b=b+bug27417(1);
524 source include/show_binlog_events.inc; /* the output must denote there is the query */;
525 select count(*) from t1 /* must be 2 */;
527 ## multi_update::send_eof() branch
532 insert into t3 values (1,1);
533 insert into t4 values (1,1),(2,2);
539 UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
542 source include/show_binlog_events.inc; /* the output must denote there is the query */;
543 select count(*) from t1 /* must be 4 */;
545 ## send_error() branch of multi_update
551 insert into t3 values (1,1),(2,2);
552 insert into t4 values (1,1),(2,2);
558 UPDATE t3,t4 SET t3.a=t4.a + bug27417(1);
561 select count(*) from t1 /* must be 1 */;
568 # DELETE incl multi-delete
575 insert into t2 values (1);
576 insert into t3 values (1,1);
577 create trigger trg_del before delete on t2 for each row
578 insert into t3 values (bug27417(1), 2);
585 source include/show_binlog_events.inc; /* the output must denote there is the query */;
586 select count(*) from t1 /* must be 1 */;
589 drop trigger trg_del;
595 create trigger trg_del_t2 after delete on t2 for each row
596 insert into t1 values (1);
597 insert into t2 values (2),(3);
598 insert into t5 values (1),(2);
603 delete t2.* from t2,t5 where t2.a=t5.a + 1;
606 source include/show_binlog_events.inc; /* the output must denote there is the query */;
607 select count(*) from t1 /* must be 1 */;
616 create table t4 (a int default 0, b int primary key) engine=innodb;
617 insert into t4 values (0, 17);
622 load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
625 select count(*) from t1 /* must be 2 */;
626 source include/show_binlog_events.inc; /* the output must denote there is the query */;
633 drop trigger trg_del_t2;
634 drop table t1,t2,t3,t4,t5;
635 drop function bug27417;