Call us Toll-Free:
1-800-218-1525
Live ChatEmail us

 Sponsors

How to fix: MySQL Duplicate entry for key PRIMARY on Auto_Increment

Adrian Singer, 08-20-2009
We encountered an interesting error on a client database this past few days, where MySQL was issuing random errors, complaining about:

Duplicate entry '1' for key 'PRIMARY'

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

CREATE TABLE test (id int auto_increment, somevalue int, primary key (id));

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:


mysqldump
-uUSER -pPASSWORD DBNAME > /tmp/my_backup.sql

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


drop database DBNAME
;
create database DBNAME;

Step 3

Reload the import


mysql
-uUSER -pPASSWORD DBNAME < /tmp/my_backup.sql

jp, 08-22-2009
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.

Ben, 09-16-2009
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.

Glenn, 12-03-2009
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...

ryan, 03-03-2010
Yip the SQL dump worked for me

Pushpinder Bagga, 04-30-2010
jp here gave the solution in a second

I was getting the error as

Duplicate entry '127' for key 'PRIMARY'

and the reason was my primary key id auto_increment field set to tinyint which has the maximum value of 127

so replacing it to medium int solved the error in seconds...

cheers!

Echo51, 05-02-2010
I had the same trouble on 127 on a TinyINT autoincrement, which i fixed by making it a INT instead, it seems kinda random, but that fixed it..

David, 06-28-2010
I'm having this problem but in what I assume is a VARCHAR column. The error is occurring when I update a Drupal databse to the latest version. My exact error is: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'user-menu' for key 'PRIMARY'

A few people here have mentioned InnoDB tables... that cuts down the number of tables I have to check significantly so I guess I just have to try and work it out by hand... backup and restore definitely isn't working.

lkajsdf, 07-09-2010
This problem happened to me when I used ssh to remotely import a 1.1gb SQL dump. SSH timed out midway and upon trying to start importing the data where it left off, I was getting this error.

"id" was a medium int (9).. I made it unsigned int (10) as per jp's comment. Worked like a charm!

crist, 09-19-2010
Actually the problem seems deeper, i am having the same problem, in on a fresh DB. my key is declared as BIGINT(20), after in the insert of one row, the second row complain on 'duplicate key value 0'; and the key is declared auto_inc!!

gamer, 09-22-2010
easier fix then that unmark primary key for that value and voila perm fix

Full Hyperion, 11-02-2010
repairing the table might also work (it did for me)

j5, 12-15-2010
In my case the table had a row with a value of '0' for the autoincrement column. Updating it to a number different from 0 (greater than 0, to be more precise) solved the problem.

LarryK, 01-01-2011
Thank you so much for the great post...
1, 2, 3 worked like a charm.... this article is golden !! Just what i was looking for.

Manis, 01-12-2011
I am facing similar issue in my company's QA DB. is there a simpler solution than reloading DB?

Reloading DB will not a viable solution for production DB (Some tables contains millions of rows). So I am trying to find an answer which is good enough for production DB.

Kussin, 01-25-2011
it's ill-considered when the backuper
manual...

:]

Kussin, 01-26-2011
phpmyadmin always err for this

Jezer, 02-14-2011
There is another reason why this error can come up, and it took me ages to find it but I finally got it.

My co-worker, and thank goodness it's his last day today... had added a trigger to audit some DB operations. The trigger attempted to log the operation being undertaken to another table. He was inserting the primary key as 0 however there was already a record with the primary key of zero in the secret auditing table!

So CHECK YOUR TRIGGERS! Sometimes the error is not caused by the table your trying to change, but the table your trigger is trying to change! It's very deceptive.

I was pulling my hair out, the SQL was working on other databases, my column size was perfectly ok, I flushed and regenerated the tables and indexes of course no luck! TRIGGERS!

Alien, 03-11-2011
mysqlcheck --auto-repair=true -uroot -p <dbname>

kirk roberts, 03-17-2011
the mysqlcheck... worked for me! Thanks

jim, 03-18-2011
Changing the auto increment field from smallint to medium int worked. Thanks for the posts

Alexander Vassbotn R√łyne-Helgesen, 03-25-2011
You might want to check your indexes ;) Delete un-needed keys ;)

Emanuel, 04-05-2011
Eso me paso a mi, lo que pasaba es que en la tabla donde tengo las llaves foraneas tengo como index los campos foraneos, pero en el Index Type lo tenia como Unique y lo cambie a Normal y se soluciono el problema.

Suerte

joko wandiro, 05-16-2011
autoincrement field have reached limit.
So you can backup and start your data from beginning and it's work very well for me..

WendyMcF, 07-06-2011
jp's post applied to my situation:

> The duplicate key entry happens when you reach the upper limit of the auto increment field

I'm using a table that I update once a month, deleting all existing rows. Periodically, the Auto_increment primary key reaches its limit so I reset it.

ALTER TABLE my_freshly_deleted_table AUTO_INCREMENT = 1;

To see the next Autoindex:
SHOW TABLE STATUS LIKE 'my_freshly_deleted_table';

Martin (Inverness), 10-10-2011
I had this issue on a client's site and found that whoever suggested this:-

"In my case the table had a row with a value of '0' for the autoincrement column. Updating it to a number different from 0 (greater than 0, to be more precise) solved the problem."

Was absolutely correct. WHY an auto_increment field can't start at ZERO I don't know, but mySQL happily added in a third row once the first entry in the database was, in fact, 1 and not 0.

So thank you for that.

sohbet, 10-21-2011
Wery nice project thanks

Kent-Rune Saetrum, 11-10-2011
Removing the PRIMARY KEY index worked for me.

Wateva, 12-17-2011
Thanks guys.. Managed to fix my forum with that..
Had a backup from 2hs before installing PHPNuke.. The portal 'nuked' the database with only "nuke_" tables. Not sure why it happened. It shouln't, at all. Anyway, only dropping the existing tables and importing the 2hs old backup did the trick.. Thanks for the tutorial.

PS: Tried importing with PHPMyAdmin through the ./upload folder and got errors all around. Got to go telnet. Didn't use SU at all! And that was great cuz I had no SU access.

Thanks again... Your tips were a life saver!

davel, 12-27-2011
i had this same problem with dupe entry s i created a php script that updates the hidden field with an index value. this is not a fix but its another option.

mr-magento, 01-04-2012
I changed The key EAA51B56FF092A0DCB795D1CEF812B7B from unique to Index. Is this going to cause issues down the road?

Adorjan Princz, 02-21-2012
Same issue and the solution for me was to disable and re-enable the auto increment.

I can't imagine what caused the problem because it was a newly created table and I encountered the issue when I was trying to insert the second row.

Greg, 03-28-2012
I was having the same problem. I changed the attributes on the column. Unchecked auto increment, change int to varchar save the changes. insert two rows into the database which worked fine. Then delete the records entered, reset the attributes back to int and auto increment.

worked fine for me...

isfahan, 04-14-2012
hi.. I am facing a problem. I have a online form. when I fill this form and submit. my form submited well without any error. but when I went to the C-Panel for to check and Grab out this form data. I am finding values 0,0,0,0 in all fields.for example.
I have 6 fields created in my form.
1 NAME 2.CITY NAME 3.E-mail id 4.Contact No 5.Pass and 6.Comments Area in my form. but when I go the C-panel for to get out the data from above field after completing form. I received values 0,0,0,0,0, in all fields. please help me out.

web development, 06-04-2012
Hi,

I had the case with MySQL 5.1. I checked, analyzed and optimized the table and the issue was fixed. From my experience as DBA I guess either the analyze table or the optimize table does the trick since it is related to indexes.

Rgds
Joe

Renato, 06-08-2012
Jezer, thank you for sharing your solution.
I had the same problem here and would NEVER associate the duplicate key error with the trigger!

Kris, 06-12-2012
I am seeing an error like this every now and then in the logs

SqlExceptionHelper:144 - Duplicate entry '1449-8798' for key 'PRIMARY'

But when I do a dump of the database I can't find the key '1449-8798' at all

Vadim Sachno, 06-21-2012
The way I fixed such a problem was making a new primary key index and deleting the "PRIMARY" index. Just try something like

ALTER TABLE `table_name` DROP INDEX `index_name`

Jai vyas, 07-14-2012
Thanks its work for me

Vincent, 08-14-2012
My solution was different but super easy. For me this error happened immediately after a server crash. It seems it must've crashed in the middle of a row insert so the db thought the new id had already been used but really it hadn't.

So all I did was look at the options for the table using MySQL Workbench where it shows the autoincrement value. It turns out the next value was exactly the one it was trying to insert, so I just increased this value by 1 and that solved the problem.

I'm sure there's a command line way to do that as well but I used MySQL Workbench.

miryam, 10-17-2012
the UNSIGNED thing worked for me, thanks!

yamara, 11-20-2012
Thnks a lot..
Your tips work very well..

Ski, 11-30-2012
Make sure the column DEFAULT is NULL rather than 0 or 1.

manoj damkondwar, 01-07-2013
thanks for notes, i was very sad for this error

WhiteSites, 02-19-2013
I just did a full repair on the table with MySQL administrator and problem fixed. Row Count was updated from 0 to big number. Tells me that for some reason the row count value got corrupt. Almost makes me want to not use auto-increment fields and just manually set all values.
Enjoyed this post?

Subscribe Now to receive new posts via Email as soon as they come out.

 Comments
Post your comments












Note: No link spamming! If your message contains link/s, it will NOT be published on the site before manually approved by one of our moderators.



About Us  |  Contact us  |  Privacy Policy  |  Terms & Conditions