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

Bug #977916 reported by Laurynas Biveinis
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Invalid
High
Laurynas Biveinis
5.5
Invalid
High
Laurynas Biveinis

Bug Description

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;

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

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.

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.