Description:
When store function which insert rows into other tables is called, row events of
the statement are binlogged like:
BEGIN
ROW EVENTS on t2.
ROW EVENTS on t3.
CREATE TABLE t1.
ROW EVENTS on t1
COMMIT/ROLLBACK.
The row events for other tables are binlogged before the 'CREATE TABLE' tatement. As 'CREATE TABLE' always ends a transaction automatically, the statement's row events are divided into different transactions and the first part of row event is not able to roll back if rollback encounters in the second part.
How to repeat:
source include/master-slave.inc;
source include/have_innodb.inc;
source include/have_binlog_format_row.inc;
CREATE TABLE t2(a INT) ENGINE=MYISAM;
CREATE TABLE t3(a INT) ENGINE=INNODB;
delimiter |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
insert into t3 values(1);
insert into t2 values(1);
return 1;
END|
delimiter ;|
let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1);
--error 1062
CREATE TABLE t1(UNIQUE(a)) ENGINE=MYISAM SELECT 1 as a UNION ALL SELECT f1() as a;
eval SHOW BINLOG EVENTS IN '$binlog_file' FROM $binlog_start;
SELECT * FROM t2;
SELECT * FROM t3;
sync_slave_with_master;
--echo [on slave]
SELECT * FROM t2;
SELECT * FROM t3;
Suggested fix:
Put all row events after 'CREATE TABLE ...' statement.
Description: When store function which insert rows into other tables is called, row events of the statement are binlogged like: BEGIN ROW EVENTS on t2. ROW EVENTS on t3. CREATE TABLE t1. ROW EVENTS on t1 COMMIT/ROLLBACK. The row events for other tables are binlogged before the 'CREATE TABLE' tatement. As 'CREATE TABLE' always ends a transaction automatically, the statement's row events are divided into different transactions and the first part of row event is not able to roll back if rollback encounters in the second part. How to repeat: source include/master-slave.inc; source include/have_innodb.inc; source include/have_binlog_format_row.inc; CREATE TABLE t2(a INT) ENGINE=MYISAM; CREATE TABLE t3(a INT) ENGINE=INNODB; delimiter |; CREATE FUNCTION f1() RETURNS INT BEGIN insert into t3 values(1); insert into t2 values(1); return 1; END| delimiter ;| let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1); --error 1062 CREATE TABLE t1(UNIQUE(a)) ENGINE=MYISAM SELECT 1 as a UNION ALL SELECT f1() as a; eval SHOW BINLOG EVENTS IN '$binlog_file' FROM $binlog_start; SELECT * FROM t2; SELECT * FROM t3; sync_slave_with_master; --echo [on slave] SELECT * FROM t2; SELECT * FROM t3; Suggested fix: Put all row events after 'CREATE TABLE ...' statement.