MySQL Bugs: #75479: replication break with temporary tables

Bug #75479	replication break with temporary tables
Submitted:	12 Jan 2015 8:46	Modified:	12 Jan 2015 9:12
Reporter:	qinglin zhang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	replication, temporary

Description:
replication break when master have temporary tables.

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

--connection master
use test;
create temporary table a(id int);

--sync_slave_with_master

--let $rpl_server_number=2
--source include/rpl_restart_server.inc

--connection master
insert into a values(1);

--connection slave
start slave;
query_vertical show slave status;

Suggested fix:
none

Hello qinglin zhang,

Thank you for the report.

Thanks,
Umesh

//

[ushastry@ushastry]/export/umesh/mysql-5.6.24/mysql-test: ./mtr 75479
Logging: ./mtr  75479
2015-01-12 09:50:30 32642 [Note] Plugin 'FEDERATED' is disabled.
2015-01-12 09:50:30 32642 [Note] Binlog end
2015-01-12 09:50:30 32642 [Note] Shutting down plugin 'MyISAM'
2015-01-12 09:50:30 32642 [Note] Shutting down plugin 'CSV'
MySQL Version 5.6.24
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/export/umesh/mysql-5.6.24/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
Warnings:
Note    ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
Note    ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
use test;
create temporary table a(id int);
include/rpl_restart_server.inc [server_number=2]
insert into a values(1);
start slave;
show slave status;
Slave_IO_State  Checking master version
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     226
Relay_Log_File  slave-relay-bin.000002
Relay_Log_Pos   390
Relay_Master_Log_File   master-bin.000001
Slave_IO_Running        Yes
Slave_SQL_Running       Yes
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno      0
Last_Error
Skip_Counter    0
Exec_Master_Log_Pos     226
Relay_Log_Space 706
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   0
Master_SSL_Verify_Server_Cert   No
Last_IO_Errno   0
Last_IO_Error
Last_SQL_Errno  0
Last_SQL_Error
Replicate_Ignore_Server_Ids
Master_Server_Id        0
Master_UUID     1160314d-9a38-11e4-b5d3-0010e05f3e06
Master_Info_File        /export/umesh/mysql-5.6.24/mysql-test/var/mysqld.2/data/master.info
SQL_Delay       0
SQL_Remaining_Delay     NULL
Slave_SQL_Running_State Waiting for the next event in relay log
Master_Retry_Count      10
Master_Bind
Last_IO_Error_Timestamp
Last_SQL_Error_Timestamp
Master_SSL_Crl
Master_SSL_Crlpath
Retrieved_Gtid_Set
Executed_Gtid_Set
Auto_Position   0
main.75479                               [ fail ]  Found warnings/errors in server log file!
        Test ended at 2015-01-12 09:50:38
line
2015-01-12 11:50:38 32728 [ERROR] Slave SQL: Error 'Table 'test.a' doesn't exist' on query. Default database: 'test'. Query: 'insert into a values(1)', Error_code: 1146
^ Found warnings in /export/umesh/mysql-5.6.24/mysql-test/var/log/mysqld.2.err
ok

 - saving '/export/umesh/mysql-5.6.24/mysql-test/var/log/main.75479/' to '/export/umesh/mysql-5.6.24/mysql-test/var/log/main.75479/'
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 8 seconds executing testcases
mysql-test-run: WARNING: Got errors/warnings while running tests, please examine '/export/umesh/mysql-5.6.24/mysql-test/var/log/warnings' for details.

Completed: Failed 1/1 tests, 0.00% were successful.

Failing test(s): main.75479

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

mysql-test-run: *** ERROR: there were failing test cases

Workaround could be to use Safe slave shutdown when using temporary tables which is explained here http://dev.mysql.com/doc/refman/5.6/en/replication-features-temptables.html