MySQL Bugs: #67942: CREATE TABLE is not written in binary log if connection is killed in the process

Bug #67942	CREATE TABLE is not written in binary log if connection is killed in the process
Submitted:	18 Dec 2012 21:34	Modified:	20 Dec 2012 16:55
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.5, 5.6, 5.5.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
If a connection running CREATE TABLE .. LIKE is killed at a certain moment, it might happen that the table is created, but the corresponding record is not written to the binary log. Naturally, further actions on the table cause replication failure.

I'm not sure whether it can be reproduced with plain CREATE TABLE rather than CREATE TABLE .. LIKE. With CREATE TABLE .. LIKE, I could only reproduce it when the existing table (the one under LIKE) is a temporary table, and only with RBR.

Please note that the test case uses debug sync points. There is no need to add anything to the code, the sync point "create_table_like_before_binlog" already exists in MySQL.  

Partial output from the test case in 'How to repeat' section: 

SHOW TABLES;
Tables_in_test
t2
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       246     Server ver: 10.0.0-MariaDB-debug-log, Binlog ver: 4
master-bin.000001       246     Binlog_checkpoint       1       286     master-bin.000001
master-bin.000001       286     Query   1       396     use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t1`
INSERT INTO t2 VALUES (1);

...
=== SHOW SLAVE STATUS ===
...
Last_Errno      1146
Last_Error      Error executing row event: 'Table 'test.t2' doesn't exist'

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_row.inc

--connect (con1,localhost,root,,)
--let $con1 = `SELECT CONNECTION_ID()`

CREATE TEMPORARY TABLE t1 (j INT);

SET DEBUG_SYNC='create_table_like_before_binlog SIGNAL want_to_binlog WAIT_FOR killed';

send CREATE TABLE IF NOT EXISTS t2 LIKE t1;

--connection master

SET DEBUG_SYNC='now WAIT_FOR want_to_binlog';
eval KILL $con1;
SET DEBUG_SYNC='now SIGNAL killed';

SHOW TABLES;
SHOW BINLOG EVENTS;
INSERT INTO t2 VALUES (1);

--sync_slave_with_master

This is easy to verify on 5.5.28:

**** SHOW SLAVE STATUS on default ****
SHOW SLAVE STATUS;
Slave_IO_State  Waiting for master to send event
Master_Host     127.0.0.1
Master_User     root
Master_Port     13000
Connect_Retry   1
Master_Log_File master-bin.000001
Read_Master_Log_Pos     429
Relay_Log_File  slave-relay-bin.000002
Relay_Log_Pos   364
Relay_Master_Log_File   master-bin.000001
Slave_IO_Running        Yes
Slave_SQL_Running       No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno      1146
Last_Error      Error executing row event: 'Table 'test.t2' doesn't exist'
Skip_Counter    0
Exec_Master_Log_Pos     217
Relay_Log_Space 732
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed      No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master   NULL
Master_SSL_Verify_Server_Cert   No
Last_IO_Errno   0
Last_IO_Error
Last_SQL_Errno  1146
Last_SQL_Error  Error executing row event: 'Table 'test.t2' doesn't exist'
Replicate_Ignore_Server_Ids
Master_Server_Id        1

Thank you for the report.

Verified as described. Bug is not repeatable with versions 5.1.68 and 5.7.1