MySQL Bugs: #67526: Duplicate key error on auto-inc PK with mixed auto_increment

Bug #67526	Duplicate key error on auto-inc PK with mixed auto_increment_increment clients
Submitted:	8 Nov 2012 18:51	Modified:	7 May 2019 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

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

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;

fixing typo in tag

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.

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

I had the same issue on 5.7.25.

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.

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.