path: root/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test

# 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
-- source include/have_debug.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_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

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 #
--replace_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

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_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

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_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

# 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 #
--replace_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

# 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_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

# 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_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;
insert into t1 values(11);
commit;

--replace_column 5 #
--replace_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;


# 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_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

delete from t1;
delete from t2;
reset master;

begin;
insert into t1 values(13);
insert into t2 select * from t1;
rollback;

--replace_column 5 #
--replace_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

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_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

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_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

# 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 engine=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_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;
do release_lock("lock1");
drop table t0,t2;

# End of 4.1 tests

#
# Test behaviour of CREATE ... SELECT when mixing MyISAM and InnoDB tables
#

set autocommit=0;
CREATE TABLE t1 (a int, b int) engine=myisam;
reset master;
INSERT INTO t1 values (1,1),(1,2);
--error 1062
CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1;
# This should give warning
DROP TABLE if exists t2;
INSERT INTO t1 values (3,3);
--error 1062
CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1;
ROLLBACK;
# This should give warning
DROP TABLE IF EXISTS t2;

CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
INSERT INTO t1 VALUES (4,4);
--error 1062
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
SELECT * from t2;
TRUNCATE table t2;
INSERT INTO t1 VALUES (5,5);
--error 1062
INSERT INTO t2 select * from t1;
SELECT * FROM t2;
DROP TABLE t2;

INSERT INTO t1 values (6,6);
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ;
INSERT INTO t1 values (7,7);
ROLLBACK;
INSERT INTO t1 values (8,8);
--error 1062
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
COMMIT;
INSERT INTO t1 values (9,9);
--error 1062
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
ROLLBACK;
SELECT * from t2;
TRUNCATE table t2;
INSERT INTO t1 values (10,10);
--error 1062
INSERT INTO t2 select * from t1;
SELECT * from t1;
INSERT INTO t2 values (100,100);
--error 1062
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
COMMIT;
INSERT INTO t2 values (101,101);
--error 1062
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
ROLLBACK;
SELECT * from t2;
DROP TABLE t1,t2;
--replace_regex /table_id: [0-9]+/table_id: #/
show binlog events from 102;

# 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