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: | |
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
[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?