Bug #67526 Duplicate key error on auto-inc PK with mixed auto_increment_increment clients
Submitted: 8 Nov 2012 18:51 Modified: 7 May 21:37
Reporter: Rob Lineweaver Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.28, 5.5.29, 5.7.0 OS:Linux (Debian/AMD64 6.0.5)
Assigned to: CPU Architecture:Any
Tags: auto_increment_increment, regression
Triage: Needs Triage: D2 (Serious)

[8 Nov 2012 18:51] Rob Lineweaver
Description:
On MySQL 5.5 with innodb_autoinc_lock_mode=1, with connected clients that have different values for auto_increment_increment (some with 1 and some with 2) doing concurrent inserts onto an InnoDB table with an auto-increment primary key, we see duplicate key errors like the following:

Duplicate entry '21211440' for key 'PRIMARY'

No value was specified for the auto-increment column in the insert queries.  I expect to never see a duplicate key error for an auto-increment PK in this case.

Scenarios tested:
  1. MySQL 5.1.66 + innodb_autoinc_lock_mode=1:  no problem
  2. MySQL 5.5.28 + innodb_autoinc_lock_mode=1: duplicate key error reliably produced within a few seconds
     2a.  with both clients using auto_increment_increment = 1:  no problem
     2b.  with both clients using auto_increment_increment = 2:  no problem
  3. MySQL 5.5.28 + innodb_autoinc_lock_mode=0: no problem

How to repeat:

 1. Set up a MySQL 5.5.28 server with innodb_autoinc_lock_mode=1
 2. Create a simple InnoDB table with an auto-increment PK:
     create table test_items (id int(11) auto_increment primary key);
 3. Connect client 1 and set auto_increment_increment to 1:
     set auto_increment_increment=1;
 4. Connect client 2 and set auto_increment_increment to 2:
     set auto_increment_increment=2;
 5. Perform repeated inserts from both clients at the same time:
     insert into `test_items` values (DEFAULT);
 6. Watch for "Duplicate entry ... for key 'PRIMARY'" errors
[8 Nov 2012 20:18] Sveta Smirnova
Thank you for the report.

Verified as described.

Test case for MTR:

--source include/have_innodb.inc

create table test_items (id int(11) auto_increment primary key) engine=innodb;

set auto_increment_increment=1;

delimiter |;
create procedure bug67526(iterations int) begin while iterations > 0 do set iterations = iterations -1; insert into `test_items` values (DEFAULT); end while; end;|
delimiter ;|

connect(con1, localhost, root,,);
connection con1;

set auto_increment_increment=2;

connection default;
--send call bug67526(10000);

connection con1;
call bug67526(10000);

connection default;
--reap

select max(id) from test_items;
[8 Nov 2012 21:01] Rob Lineweaver
fixing typo in tag
[19 Nov 2013 12:08] Sam Butler
We're seeing the same bug reproduced on 5.5.33 (Linux) with two replicating servers that have the same auto_increment_increment value, but a different offset. A single client is connected and trying to insert into the table that throws the duplicate PK error on an auto inc field.
[20 Aug 2015 12:49] Rolf Martin-Hoster
As noticed by Sam this is occurring with same auto_increment_increment  and is similar if not the same as http://bugs.mysql.com/bug.php?id=76872
[8 Apr 8:47] Pavel Katiushyn
I had the same issue on 5.7.25.
[7 May 21:13] Daniel Price
Posted by developer:
 
Bug#15851528 DUPLICATE KEY ERROR ON AUTO-INC PK WITH MIXED AUTO_INCREMENT_INCREMENT CLIENTS

Problem:
Clients running different values for auto_increment_increment
and doing concurrent inserts leads to "Duplicate key error" in one of them.
    
Analysis:
When Autoinc_increment value is reduced in a session,
InnoDB uses last autoinc_increment value
to recalculate the autoinc value.
In case, some other session has inserted a value
with different autoinc_increment, InnoDB recalculate
autoinc values based on current session previous autoinc_increment
instead of considering the autoinc_increment used for last insert
across all session
    
Fix:
revert 1d4494d93c2f as it causing the current bug.
[7 May 21:37] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.5.65, 5.6.45, 5.7.27, 8.0.17 releases, and here's the changelog entry:

Client sessions using different auto_increment_increment values while
performing concurrent insert operations could cause a duplicate key error.