MySQL Bugs: #46731: Replication aborts on DML statement with concurrent CREATE or RENAME table

Bug #46731	Replication aborts on DML statement with concurrent CREATE or RENAME table
Submitted:	14 Aug 2009 16:22	Modified:	29 Oct 2009 7:34
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 5.4.4	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If one connection starts a DML statement which requires a non-existent table, and another connection creates the table before the 1st one reaches the point when the table is needed, the query in the 1st connection still fails with error 1146. 

If the query has modified something before it failed, it is written into the binary log with the corresponding error code; however, the DDL statement from the 2nd connection appears in the binary log earlier, which allows a slave to process both queries successfully, and slave SQL thread aborts as the error code on slave (0) does not match the master (1146).

Mixed- and statement- based replication are affected.

How to repeat:
# Test case for 5.1 and above

--source include/master-slave.inc
--source include/have_binlog_format_mixed_or_statement.inc

connect (m1,127.0.0.1,root,,test,$MASTER_MYPORT,);
connect (m2,127.0.0.1,root,,test,$MASTER_MYPORT,);
connect (s,127.0.0.1,root,,test,$SLAVE_MYPORT,);

connection m1;

use test;
--disable_warnings
drop table if exists tbl_fail_on_create;
drop table if exists log_fail_on_create;
drop trigger if exists trig_fail_on_create;
--enable_warnings
create table tbl_fail_on_create ( i int ) engine = MyISAM;
create trigger trig_fail_on_create after insert on tbl_fail_on_create 
	for each row insert into log_fail_on_create values (new.i);

send insert into tbl_fail_on_create values((select sleep(3)));
--real_sleep 1

connection m2;
create table log_fail_on_create ( i int ) engine = MyISAM;

connection m1;
--error 1146
reap;

connection s;
--real_sleep 1
select * from test.log_fail_on_create;
let $err_msg = query_get_value(show slave status, 'Last_SQL_Error', 1);
--echo # Error message: $err_msg

Thank you for the report.

Verified as described. Although I'd say this is limitation of STATEMENT binary log format.

It affects mixed replication mode as well -- no warning or switch to RBR.

If one connection starts a DML statement which requires a non-existent table, 
and another connection creates the table before the 1st one reaches the point 
when the table is needed, the query in the 1st connection still fails with 
error 1146.

Why we can't change the behavior as the query in the 1st connection will 
succeed with error 0 for fixing the bug.