MySQL Bugs: #57250: mark INSERT...SELECT with autoinc column unsafe when innodb_autoinc_lock

Bug #57250	mark INSERT...SELECT with autoinc column unsafe when innodb_autoinc_lock_mode=2
Submitted:	5 Oct 2010 14:07	Modified:	5 Oct 2010 14:22
Reporter:	Sven Sandberg	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	auto_increment, bulk insert, innodb_autoinc_lock_mode, insert select, REPLACE SELECT

Description:
When innodb_autoinc_lock_mode = 2, and an INSERT...SELECT or REPLACE...SELECT statement that generates autoinc values is written to the binary log in statement format, then it may cause the slave to stop. This is documented at http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html . We should mark INSERT...SELECT and REPLACE...SELECT unsafe, so that (1) a warning is printed when binlog_format=statement; (2) the statement is written to the binary log in row format when binlog_format=mixed.

Note that the scenario in this bug is similar to BUG#50440. However, this bug is slightly different. In BUG#50440, the statement is unsafe only when rows are retrieved in non-deterministic order. In BUG#50440, the statement can be made safe by adding ORDER BY primary_key. The present bug is a concern even when there is an ORDER BY primary_key.

So, after both this bug and BUG#50440 have been fixed, an INSERT..SELECT statement where the inserted-into table has an autoinc column shall be unsafe if at least one of the following conditions holds:
 - there is no order by primary key
 - innodb_autoinc_lock_mode = 2

See also BUG#55481, BUG#50439, BUG#55211.

How to repeat:
The following test case is nondeterministic, so it may not always fail, but it does fail most of the time.

--source include/have_binlog_format_mixed.inc
--source include/have_innodb.inc
--source include/master-slave.inc

SELECT @@global.innodb_autoinc_lock_mode;

CREATE TABLE t2 (b INT);
CREATE TABLE t1 (a INT KEY AUTO_INCREMENT, b INT) ENGINE = InnoDB;

--disable_query_log
--let $n= 1000
while ($n) {
  INSERT INTO t2 VALUES (1);
  --dec $n
}
--enable_query_log

send INSERT INTO t1(b) SELECT 1 FROM t2;
--connection master1
INSERT INTO t1(b) SELECT 2 FROM t2;
--connection master
--reap

--sync_slave_with_master

Verified with current 5.1.52 from bzr on Ubuntu:

openxs@ubuntu:/home2/openxs/dbs/5.1/mysql-test$ ./mtr --mysqld=--innodb_autoinc_lock_mode=2 bug57250 | more
Logging: ./mtr  --mysqld=--innodb_autoinc_lock_mode=2 bug57250
101005 17:21:06 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.1.52
Checking supported features...
 - skipping ndbcluster, mysqld not compiled with ndbcluster
 - 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 49501

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

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

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug57250                            [ fail ]
        Test ended at 2010-10-05 17:21:15

CURRENT_TEST: main.bug57250
=== SHOW MASTER STATUS ===
---- 1. ----
File	slave-bin.000001
Position	88531
Binlog_Do_DB	
Binlog_Ignore_DB	
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
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	88760
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	88686
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	1062
Last_Error	Error 'Duplicate entry '128' for key 'PRIMARY'' on query. Defaul
t database: 'test'. Query: 'INSERT INTO t1(b) SELECT 1 FROM t2'
Skip_Counter	0
Exec_Master_Log_Pos	88540
Relay_Log_Space	89061
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	
Master_SSL_Verify_Server_Cert	No
Last_IO_Errno	0
Last_IO_Error	
Last_SQL_Errno	1062
Last_SQL_Error	Error 'Duplicate entry '128' for key 'PRIMARY'' on query. Defaul
t database: 'test'. Query: 'INSERT INTO t1(b) SELECT 1 FROM t2'
...