# Check that binlog is ok when a transaction mixes updates to InnoDB and # MyISAM. # It would be nice to make this a replication test, but in 4.0 the # slave is always with --skip-innodb in the testsuite. I (Guilhem) however # did some tests manually on a slave; tables are replicated fine and # Exec_Master_Log_Pos advances as expected. # Embedded server doesn't support binlogging -- source include/not_embedded.inc -- source include/have_innodb.inc --disable_warnings drop table if exists t1, t2; --enable_warnings connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; reset master; begin; insert into t1 values(1); insert into t2 select * from t1; commit; --replace_column 5 # --replace_result "xid=14" "xid=8" show binlog events from 98; delete from t1; delete from t2; reset master; begin; insert into t1 values(2); insert into t2 select * from t1; # should say some changes to non-transact1onal tables couldn't be rolled back rollback; --replace_column 5 # show binlog events from 98; delete from t1; delete from t2; reset master; begin; insert into t1 values(3); savepoint my_savepoint; insert into t1 values(4); insert into t2 select * from t1; rollback to savepoint my_savepoint; commit; --replace_column 5 # --replace_result "xid=47" "xid=25" show binlog events from 98; delete from t1; delete from t2; reset master; begin; insert into t1 values(5); savepoint my_savepoint; insert into t1 values(6); insert into t2 select * from t1; rollback to savepoint my_savepoint; insert into t1 values(7); commit; select a from t1 order by a; # check that savepoints work :) --replace_column 5 # --replace_result "xid=69" "xid=37" show binlog events from 98; # and when ROLLBACK is not explicit? delete from t1; delete from t2; reset master; select get_lock("a",10); begin; insert into t1 values(8); insert into t2 select * from t1; disconnect con1; connection con2; # We want to SHOW BINLOG EVENTS, to know what was logged. But there is no # guarantee that logging of the terminated con1 has been done yet (it may not # even be started, so con1 may have not even attempted to lock the binlog yet; # so SHOW BINLOG EVENTS may come before con1 does the loggin. To be sure that # logging has been done, we use a user lock. select get_lock("a",10); --replace_column 5 # show binlog events from 98; # and when not in a transact1on? delete from t1; delete from t2; reset master; insert into t1 values(9); insert into t2 select * from t1; --replace_column 5 # --replace_result "xid=117" "xid=60" show binlog events from 98; # Check that when the query updat1ng the MyISAM table is the first in the # transaction, we log it immediately. delete from t1; delete from t2; reset master; insert into t1 values(10); # first make t1 non-empty begin; insert into t2 select * from t1; --replace_column 5 # --replace_result "xid=131" "xid=66" show binlog events from 98; insert into t1 values(11); commit; --replace_column 5 # --replace_result "xid=131" "xid=66" "xid=134" "xid=68" show binlog events from 98; # Check that things work like before this BEGIN/ROLLBACK code was added, # when t2 is INNODB alter table t2 engine=INNODB; delete from t1; delete from t2; reset master; begin; insert into t1 values(12); insert into t2 select * from t1; commit; --replace_column 5 # --replace_result "xid=153" "xid=78" show binlog events from 98; delete from t1; delete from t2; reset master; begin; insert into t1 values(13); insert into t2 select * from t1; rollback; --replace_column 5 # show binlog events from 98; delete from t1; delete from t2; reset master; begin; insert into t1 values(14); savepoint my_savepoint; insert into t1 values(15); insert into t2 select * from t1; rollback to savepoint my_savepoint; commit; --replace_column 5 # --replace_result "xid=185" "xid=94" show binlog events from 98; delete from t1; delete from t2; reset master; begin; insert into t1 values(16); savepoint my_savepoint; insert into t1 values(17); insert into t2 select * from t1; rollback to savepoint my_savepoint; insert into t1 values(18); commit; select a from t1 order by a; # check that savepoints work :) --replace_column 5 # --replace_result "xid=206" "xid=105" show binlog events from 98; # Test for BUG#5714, where a MyISAM update in the transaction used to # release row-level locks in InnoDB connect (con3,localhost,root,,); connection con3; delete from t1; delete from t2; --disable_warnings alter table t2 type=MyISAM; --enable_warnings insert into t1 values (1); begin; select * from t1 for update; connection con2; select (@before:=unix_timestamp())*0; # always give repeatable output begin; send select * from t1 for update; connection con3; insert into t2 values (20); connection con2; --error 1205 reap; select (@after:=unix_timestamp())*0; # always give repeatable output # verify that innodb_lock_wait_timeout was exceeded. When there was # the bug, the reap would return immediately after the insert into t2. select (@after-@before) >= 2; drop table t1,t2; commit; # test for BUG#7947 - DO RELEASE_LOCK() not written to binlog on rollback in the middle # of a transaction connection con2; begin; create temporary table ti (a int) engine=innodb; rollback; insert into ti values(1); set autocommit=0; create temporary table t1 (a int) engine=myisam; commit; insert t1 values (1); rollback; create table t0 (n int); insert t0 select * from t1; set autocommit=1; insert into t0 select GET_LOCK("lock1",null); set autocommit=0; create table t2 (n int) engine=innodb; insert into t2 values (3); disconnect con2; connection con3; select get_lock("lock1",60); --replace_column 5 # --replace_result "xid=206" "xid=105" "xid=224" "xid=114" "xid=227" "xid=115" "xid=231" "xid=117" "xid=258" "xid=132" show binlog events from 98; do release_lock("lock1"); drop table t0,t2; # End of 4.1 tests # Test for BUG#16559 (ROLLBACK should always have a zero error code in # binlog). Has to be here and not earlier, as the SELECTs influence # XIDs differently between normal and ps-protocol (and SHOW BINLOG # EVENTS above read XIDs). connect (con4,localhost,root,,); connection con3; reset master; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; select get_lock("a",10); begin; insert into t1 values(8); insert into t2 select * from t1; disconnect con3; connection con4; select get_lock("a",10); # wait for rollback to finish # we check that the error code of the "ROLLBACK" event is 0 and not # ER_SERVER_SHUTDOWN (i.e. disconnection just rolls back transaction # and does not make slave to stop) --exec $MYSQL_BINLOG --start-position=547 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval select (@a:=load_file("$MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output")) is not null; --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR eval select @a like "%#%error_code=0%ROLLBACK;%ROLLBACK /* added by mysqlbinlog */;%", @a not like "%#%error_code=%error_code=%"; drop table t1, t2;