auto_increment_offset != 1 + innodb_autoinc_lock_mode=1 => bulk inserts fail

[Laurynas Biveinis]

Description:
Running concurrent bulk inserts on a server with auto_increment_offset!=1,
auto_increment_increment>1 and innodb_autoinc_lock_mode=1 results in intermittent errors
like this: Duplicate entry '87674' for key 'PRIMARY'
even though primary key is set to auto_increment and is not specified in the insert
statement.

There's no errors if auto_increment_offset=1 or innodb_autoinc_lock_mode=0
('traditional')

It's reproducible on both current GA releases (5.1.57 and 5.5.12).

How to repeat:
Start mysqld with innodb_autoinc_lock_mode=1

Run attached script (note: it will delete all *log files in the current directory)
prerequisites: PHP 5.3 with mysql extension

php run.php -h 127.0.0.1 -p 3306 -uroot -P -dtest

It generates a number of log files (t*.log) in current directory (one log file per insert
process).

Expected results: log files should not contain any 'Duplicate entry '...' for key
'PRIMARY'' messages, regardless of auto_increment_offset setting.

Actual results: when auto_increment_offset is set to something != 1 in thr.php some
inserts fail.

[9 Apr 22:53] Sergei Golubchik

not the PHP one, I didn't try it. But from the patch later. Here's the test case:
# This is the test case for bug #61209 - duplicate key errors
# when using auto_increment_increment > 1 and auto_increment_offset > 1
#
--source include/have_innodb.inc

#
# Precautionary clean up.
#
--disable_warnings
DROP TABLE IF EXISTS bug_61209;
--enable_warnings

#
# Create test data.
#
CREATE TABLE bug_61209 (a INT auto_increment, PRIMARY KEY(a)) ENGINE=InnoDB;

INSERT INTO bug_61209 VALUES (), ();

--echo
--echo # Connect con1
--connect (con1,localhost,root,,)
--echo
--echo # Connect con2
--connect (con2,localhost,root,,)

--echo
--echo # Connection con1
--connection con1
SET SESSION auto_increment_increment=3;
SET SESSION auto_increment_offset=2;
SELECT GET_LOCK('a', 9);

--echo
--echo # Connection con2
--connection con2
SET SESSION auto_increment_increment=3;
SET SESSION auto_increment_offset=2;
send INSERT INTO bug_61209 (a) VALUES (NULL), (NULL), (NULL + GET_LOCK('a', 10));

--echo
--echo # Connection con1 reap
--connection con1
INSERT INTO bug_61209 (a) VALUES (NULL), (NULL), (NULL);
SELECT RELEASE_LOCK('a');

--echo
--echo # Connection con2 reap
--connection con2
reap;

#
# Clean up
#
DROP TABLE bug_61209;

[Laurynas Biveinis (laurynas-biveinis)]

MySQL 5.5.23 released with fix.

Additional bugs pointer out by Sergei Golubchik in http://bugs.mysql.com/bug.php?id=61209 will be handled separately.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-550