We encountered an interesting error on a client database this past few days, where MySQL was issuing random errors, complaining about:


These errors were coming up on various tables at different times in the day, but the crazy thing is - the duplicate key error is on an auto-increment field!

That's not normal.

When you have a table defined as


There's no way you can generate a duplicate key error on the 'id' field, unless you manually pass the value of the auto_increment id field (which we were not)

Running a mysqlcheck reported all tables are OK. We proceeded to issue OPTIMIZE TABLE statements (this rebuilds indexes) as well as manually running REPAIR TABLE for each table, but nothing helped.

The table indexes were obviously corrupted in some way, although MySQL tools reported all is well...

How to fix this problem

The only way we could fix it, is by rebuilding the entire database from scratch.

Dropping all tables and reloading them from a backup.

Step 1

Create a backup of the database by issuing:


Replace USER with your MySQL username, PASSWORD with your MySQL password and DBNAME with the databasename you are looking to repair.

Step 2

Drop and recreate the database


Step 3

Reload the import

The duplicate key entry happens when you reach the upper limit of the auto increment field, in your case, being a signed int the upper boundary is 2147483647.

Once the last record is with the id 2147483647, when you insert a new record the id should be 2147483648. But mysql reduce it inside the valid range, and tries to insert the id 2147483647, therefore it produces a duplicate key entry.

IMHO it's better altering the index field, setting it to an unsigned int (4294967295) or an unsigned bigint (18446744073709551615). Probably the unsigned int is enough for your client's requirements and it uses 4 bytes instead of 8 of the big int so the index would do it better in terms of cpu and memory.

ALTER TABLE test MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;

The current solution works because, you are reusing the ids of the erased rows, the duplicate key error will happen again depending on how may rows were deleted from the table previously.

Adrian, which MySQL version are you using? I'm seeing this same problem occuring on a 5.0.51a MySQL InnoDB table. I'll ask my database admin to rebuild the table as you described. Unfortunately, I haven't found an official bug report of this, probably because it's hard to reproduce this problem.

JP: My problematic table's primary key is an unsigned int and the primary key count is currently at 1000199, so nowhere near the upper boundary.

Hi Adrian,

I'm seeing sort of a similar problem over here yet im not convinced that restoring the db is the only solution.

You could:

# use different auto_incr offset:
auto_increment_increment = 10
auto_increment_offset = 3

If the previous offset was 1 for example, you can never get a clash again. This just requires a server restart. However, I dont see them going away even after restart so it happens nomatter of what data was already stored.

An alter table AUTO_INCREMENT= <old> * 10 for example should also do the trick, yet this is so slow once mysql feels like it has to use a temp table to do so. Whenever you use partitions in mysql that seems to happen a lot when altering these tables later. With huge tables/db's you don't have the choice of backup/restore or even use ALTER on every affected one...

My search continues...

Yip the SQL dump worked for me