# 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; show binlog events from 79; 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; show binlog events from 79; 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; show binlog events from 79; 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 :) show binlog events from 79; # 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); show binlog events from 79; # 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; show binlog events from 79; # Check that when the query updat1ng the MyISAM table is the first in the # transact1on, 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; show binlog events from 79; insert into t1 values(11); commit; show binlog events from 79; # 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; show binlog events from 79; delete from t1; delete from t2; reset master; begin; insert into t1 values(13); insert into t2 select * from t1; rollback; show binlog events from 79; 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; show binlog events from 79; 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 :) show binlog events from 79; # 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); show binlog events from 79; do release_lock("lock1"); drop table t0,t2; # End of 4.1 tests