Bug #81101 Duplicate PRIMARY KEY errors after changing auto_increment settings
Submitted: 15 Apr 2016 18:13 Modified: 14 Mar 2017 11:59
Reporter: monty solomon Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.25 OS:CentOS
Assigned to: CPU Architecture:Any

[15 Apr 2016 18:13] monty solomon
Description:
We are getting duplicate primary key errors after changing the auto_increment settings.

We were inserting rows into tables in two (2) databases on separate servers and changed the auto_increment settings on the servers so one of them would use odd values and the other one would use even values for a migration where we didn't want any collisions.

The migration is complete and we changed the auto_increment settings back to the default values and are getting duplicate primary key errors for some INSERT statements.

How to repeat:
Change the auto_increment settings to use even values and insert many rows into several tables.

SET GLOBAL auto_increment_increment = 2 ; SET GLOBAL auto_increment_offset = 2 ;

Change the auto_increment settings back to the standard values and insert rows into the tables.

SET GLOBAL auto_increment_increment = 1 ; SET GLOBAL auto_increment_offset = 1 ;

Observe duplicate primary key error 1435 for some of the INSERT statements.
[15 Apr 2016 18:19] monty solomon
That 1435 was the duplicate value for one of the INSERT statements.

Here are some examples.

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.BatchUpdateException: Duplicate entry '27529' for key 'PRIMARY' [statement:"INSERT INTO audit_invoices

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.BatchUpdateException: Duplicate entry '16205' for key 'PRIMARY' [statement:"INSERT INTO audit_payments

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '7587' for key 'PRIMARY' [statement:"INSERT INTO invoice_payments 

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1435' for key 'PRIMARY' [statement:"INSERT INTO subscription_items
[19 Apr 2016 17:01] monty solomon
An INSERT failed today complaining about a duplicate PRIMARY KEY for an entry that was added three (3) days ago.

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1436' for key 'PRIMARY' [statement:"INSERT INTO subscription_items 

The entry with the id 1436 was created at 160416  1:50:56 Z and the failure was today at 160419 11:53:16 Z.
[19 Apr 2016 17:07] monty solomon
The AUTO_INCREMENT=1437 for the table.

mysql> select max(id) from subscription_items\G
*************************** 1. row ***************************
max(id): 1436
1 row in set (0.00 sec)

mysql> select @@auto_increment_increment,  @@auto_increment_offset\G
*************************** 1. row ***************************
@@auto_increment_increment: 1
   @@auto_increment_offset: 1
1 row in set (0.00 sec)

mysql> show global variables like 'auto_increment%'\G
*************************** 1. row ***************************
Variable_name: auto_increment_increment
        Value: 1
*************************** 2. row ***************************
Variable_name: auto_increment_offset
        Value: 1
2 rows in set (0.00 sec)
[20 Apr 2016 0:10] monty solomon
It appears that the auto increment is working better after a server restart.

Something may be in a weird state after the dynamic changes.
[10 Mar 2017 6:54] yang wang
same problem in MySQL 5.6.19 version
[14 Mar 2017 11:58] Thirunarayanan Balathandayuthapani
It is a duplicate of bug#76872.
It is fixed as a part of mysql-5.6.35
[14 Mar 2017 11:59] Thirunarayanan Balathandayuthapani
It is a duplicate of bug#76872
[13 Oct 2017 23:49] Richard Balue
I'm still seeing this issue with version 5.6.35 FreeBSD 10.3 and can reproduce it with the above testing methodology.  Can someone open this ticket back up or do I have to open a new one?