MySQL Bugs: #50440: mark [INSERT|REPLACE|CREATE]...SELECT unsafe if updated table has autoinc column

Bug #50440	mark [INSERT\|REPLACE\|CREATE]...SELECT unsafe if updated table has autoinc column
Submitted:	19 Jan 2010 11:26	Modified:	10 Feb 2012 14:23
Reporter:	Sven Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Sven Sandberg	CPU Architecture:	Any
Tags:	autoincrement, create select, insert select, REPLACE SELECT, unsafe

Description:
The following statements can cause the slave to go out of sync if logged in statement format and the updated table has an auto_increment column:

  INSERT ... SELECT
  REPLACE ... SELECT
  CREATE TABLE ... SELECT

The order that rows are retrieved from SELECT may differ between master and slave. If two rows are retrieved in different order on master and slave, then the slave will generate different autoinc value for the autoinc column on those rows, compared to the master. So the slave will go out of sync.

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

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT, KEY(b));
CREATE TABLE t3 (a INT, b INT AUTO_INCREMENT, KEY(b));

INSERT INTO t1 VALUES (1), (2);

--echo [on slave]
--sync_slave_with_master
DELETE FROM t1;
INSERT INTO t1 VALUES (2), (1);

--echo [on master]
--connection master

INSERT INTO t2(a) SELECT * FROM t1;
REPLACE INTO t3(a) SELECT * FROM t1;
CREATE TABLE t4 (a INT, b INT AUTO_INCREMENT, KEY(b)) SELECT * FROM t1;

SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t4;

--sync_slave_with_master

SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t4;

--exit

Suggested fix:
Mark all these statement types as unsafe, so that:
 - the statements will be logged in row format if binlog_format=mixed
 - the statements will generate a warning if binlog_format=statement

Note: we may add the condition that the statement is not marked unsafe if the SELECT has an ORDER BY primary key clause.

See also BUG#50439.

Verified just as described with recent 5.1.43 from bzr:

openxs@suse:/home2/openxs/dbs/5.1/mysql-test> ./mtr --mysqld=--binlog_format=mixed bug50440
Logging: ./mtr  --mysqld=--binlog_format=mixed bug50440
100426 19:44:02 [Note] Plugin 'FEDERATED' is disabled.
100426 19:44:02 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.1.43
Using binlog format 'mixed'
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /home2/openxs/dbs/5.1/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/home2/openxs/dbs/5.1/mysql-test/var'...
Installing system database...
Using server port 32776
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009

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

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

main.bug50440                            [ fail ]
        Test ended at 2010-04-26 19:44:04

CURRENT_TEST: main.bug50440
--- /home2/openxs/dbs/5.1/mysql-test/r/bug50440.result  2010-04-26 19:43:57.000000000 +0300
+++ /home2/openxs/dbs/5.1/mysql-test/r/bug50440.reject  2010-04-26 19:44:04.000000000 +0300
@@ -0,0 +1,41 @@
+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 (a INT);
+CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT, KEY(b));
+CREATE TABLE t3 (a INT, b INT AUTO_INCREMENT, KEY(b));
+INSERT INTO t1 VALUES (1), (2);
+[on slave]
+DELETE FROM t1;
+INSERT INTO t1 VALUES (2), (1);
+[on master]
+INSERT INTO t2(a) SELECT * FROM t1;
+REPLACE INTO t3(a) SELECT * FROM t1;
+CREATE TABLE t4 (a INT, b INT AUTO_INCREMENT, KEY(b)) SELECT * FROM t1;
+SELECT * FROM t2;
+a      b
+1      1
+2      2
+SELECT * FROM t3;
+a      b
+1      1
+2      2
+SELECT * FROM t4;
+b      a
+1      1
+2      2
+SELECT * FROM t2;
+a      b
+2      1
+1      2
+SELECT * FROM t3;
+a      b
+2      1
+1      2
+SELECT * FROM t4;
+b      a
+1      2
+2      1

Given the recent concerns about unnecessary warnings for unsafe statements, we should avoid marking statements unsafe when there is an ORDER BY primary_key clause. This will be easier to implement this after BUG#42415. So I will not fix this bug until BUG#42415 is ready.

See bug #53079 | bug #48608 | bug #45677 | bug #42415

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.5+. Documented in the 5.5.22 and 5.6.5 changelogs as follows:

        Statements that wrote to tables with AUTO_INCREMENT columns
        based on an unordered SELECT from another table could lead to
        the master and the slave going out of sync, as the order in
        which the rows are retrieved from the table may differ between
        them. Such statements include any INSERT ... SELECT, REPLACE ...
        SELECT, or CREATE TABLE ... SELECT statement. Such statements
        are now marked as unsafe for statement-based replication, which
        causes the execution of one to throw a warning, and forces the
        statement to be logged using the row-based format if the logging
        format is MIXED.

Closed.