Bug #76940 create or drop temporary table lead slave mismatch with master
Submitted: 5 May 2015 14:08 Modified: 6 May 2015 6:47
Reporter: zhang simon (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.5, 5.5.45, 5.5.8 OS:Any
Assigned to: CPU Architecture:Any

[5 May 2015 14:08] zhang simon
Description:
create or drop temporary table lead slave mismatch with master

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

--connection master
create table t1(c1 int primary key)  engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);

create temporary table tmp as select * from t1;

begin;
insert into t1 values(6);
drop temporary table tmp;
--error ER_DUP_ENTRY
insert into t1 values (7),(8),(5);
commit;

select * from t1;

sync_slave_with_master;
--echo # slave result mismatch with master
--connection slave
select * from t1;

--connection master
drop table t1;
sync_slave_with_master;

--source include/rpl_end.inc

###################### result ########################

include/master-slave.inc
[connection master]
create table t1(c1 int primary key)  engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);
create temporary table tmp as select * from t1;
begin;
insert into t1 values(6);
drop temporary table tmp;
insert into t1 values (7),(8),(5);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
commit;
select * from t1;
c1
1
2
3
4
5
6
# slave result mismatch with master
select * from t1;
c1
1
2
3
4
5
6
7
8
drop table t1;
include/rpl_end.inc

Suggested fix:
insert into t1 values (7),(8),(5); 
this sql have failed and should not write binlog;
[6 May 2015 6:47] Umesh Shastry
Hello zhang simon,

Thank you for the report and test case.
Confirmed that only 5.5(latest version checked 5.5.45, lowest version checked 5.5.8) builds are affected.

Thanks,
Umesh
[6 May 2015 6:48] Umesh Shastry
// 5.5.45 - affected

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45/mysql-test: ./mtr --mysqld='--binlog_format=row' 76490
defined(@array) is deprecated at lib/mtr_cases.pm line 339.
        (Maybe you should just omit the defined()?)
defined(@array) is deprecated at ./mtr line 493.
        (Maybe you should just omit the defined()?)
Logging: ./mtr  --mysqld=--binlog_format=row 76490
150506  7:57:08 [Note] /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld (mysqld 5.5.45) starting as process 15526 ...
150506  7:57:08 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.45
Using binlog format 'row'
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-5.5.45/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
[connection master]
create table t1(c1 int primary key)  engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);
create temporary table tmp as select * from t1;
begin;
insert into t1 values(6);
drop temporary table tmp;
insert into t1 values (7),(8),(5);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
commit;
select * from t1;
c1
1
2
3
4
5
6
# slave result mismatch with master
select * from t1;
c1
1
2
3
4
5
6
7
8
drop table t1;
include/rpl_end.inc
main.76490                               [ pass ]    101
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.101 of 7 seconds executing testcases

Completed: All 1 tests were successful.
[6 May 2015 6:51] Umesh Shastry
// 5.5.8 affected (see 2 extra rows, also it complains other things at the beginning and end)

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.8/mysql-test: ./mtr --mysqld='--binlog_format=row' 76490
defined(@array) is deprecated at lib/mtr_cases.pm line 327.
        (Maybe you should just omit the defined()?)
defined(@array) is deprecated at ./mtr line 466.
        (Maybe you should just omit the defined()?)
Logging: ./mtr  --mysqld=--binlog_format=row 76490
150506  8:41:07 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.8
Using binlog format 'row'
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
Collecting tests...
vardir: /export/umesh/server/binaries/mysql-5.5.8/mysql-test/var
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-5.5.8/mysql-test/var'...
Installing system database...
Using server port 47137

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

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
create table t1(c1 int primary key)  engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);
create temporary table tmp as select * from t1;
begin;
insert into t1 values(6);
drop temporary table tmp;
insert into t1 values (7),(8),(5);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
commit;
select * from t1;
c1
1
2
3
4
5
6
# slave result mismatch with master
select * from t1;
c1
1
2
3
4
5
6
7
8
drop table t1;
main.76490                               [ fail ]
        Test ended at 2015-05-06 08:41:10
[6 May 2015 6:51] Umesh Shastry
// 5.6, 5.7 and 5.8 latest builds not affected

// 5.6.26

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.26/mysql-test: ./mtr --mysqld='--binlog_format=row' 76490
Logging: ./mtr  --mysqld=--binlog_format=row 76490
2015-05-06 07:59:39 0 [Note] /export/umesh/server/binaries/mysql-5.6.26/bin/mysqld (mysqld 5.6.26-enterprise-commercial-advanced) starting as process 15643 ...
2015-05-06 07:59:39 15643 [Note] Plugin 'FEDERATED' is disabled.
2015-05-06 07:59:39 15643 [Note] Binlog end
2015-05-06 07:59:39 15643 [Note] Shutting down plugin 'CSV'
2015-05-06 07:59:39 15643 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.26
Using binlog format 'row'
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-5.6.26/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]
create table t1(c1 int primary key)  engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);
create temporary table tmp as select * from t1;
begin;
insert into t1 values(6);
drop temporary table tmp;
insert into t1 values (7),(8),(5);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
commit;
select * from t1;
c1
1
2
3
4
5
6
# slave result mismatch with master
select * from t1;
c1
1
2
3
4
5
6
drop table t1;
include/rpl_end.inc
main.76490                               [ pass ]    142
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.142 of 6 seconds executing testcases

Completed: All 1 tests were successful.

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.26/mysql-test:

// 5.7.8

Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-5.7.8/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]
create table t1(c1 int primary key)  engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);
create temporary table tmp as select * from t1;
begin;
insert into t1 values(6);
drop temporary table tmp;
insert into t1 values (7),(8),(5);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
commit;
select * from t1;
c1
1
2
3
4
5
6
# slave result mismatch with master
select * from t1;
c1
1
2
3
4
5
6
drop table t1;
include/rpl_end.inc
main.76490                               [ pass ]    206
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.206 of 16 seconds executing testcases

Completed: All 1 tests were successful.

// 5.8.0

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.8.0/mysql-test: ./mtr --mysqld='--binlog_format=row' 76490
Logging: ./mtr  --mysqld=--binlog_format=row 76490
MySQL Version 5.8.0
Using binlog format 'row'
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-5.8.0/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]
create table t1(c1 int primary key)  engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);
create temporary table tmp as select * from t1;
begin;
insert into t1 values(6);
drop temporary table tmp;
insert into t1 values (7),(8),(5);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
commit;
select * from t1;
c1
1
2
3
4
5
6
# slave result mismatch with master
select * from t1;
c1
1
2
3
4
5
6
drop table t1;
include/rpl_end.inc
main.76490                               [ pass ]    232
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.232 of 17 seconds executing testcases

Completed: All 1 tests were successful.