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

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!

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..

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.

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!

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!!

easier fix then that unmark primary key for that value and voila perm fix

repairing the table might also work (it did for me)

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.

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.

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.

it's ill-considered when the backuper
manual...

:]

phpmyadmin always err for this

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!

mysqlcheck --auto-repair=true -uroot -p <dbname>

the mysqlcheck... worked for me! Thanks

Changing the auto increment field from smallint to medium int worked. Thanks for the posts

You might want to check your indexes ;) Delete un-needed keys ;)

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

autoincrement field have reached limit.
So you can backup and start your data from beginning and it's work very well for me..

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';

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.

Wery nice project thanks

Removing the PRIMARY KEY index worked for me.

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!

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.

I changed The key EAA51B56FF092A0DCB795D1CEF812B7B from unique to Index. Is this going to cause issues down the road?

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.

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...

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.

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

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

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

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`

Thanks its work for me

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.

the UNSIGNED thing worked for me, thanks!

Thnks a lot..
Your tips work very well..

Make sure the column DEFAULT is NULL rather than 0 or 1.

thanks for notes, i was very sad for this error

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.

You solved my problem

Thanks

thanks for this ... solve my problem now..

mysqlcheck --auto-repair=true -uroot -p <dbname>

I diont know how to resove this problem in Blogger.

i have duplicate entry 138903 for key primary problem can any one help me to fix this?? thanks a lot

on SQL Benchmark, simply leave the PRIMARY field empty. MySQL will fill them up automatically.

Thanks. It works for me :)

The problem occurs when trying to inject a value into a field that is an auto_increment.

An auto_increment in MySQL can have a minimum value of 1.

Attempting to insert a zero value will cause MySQL to override the value and set it to 1 therefore when you try and insert the record you wanted to have a value of 1 you will get the duplicate error message.

The solution is to set the @@SQL_MODE global variable before your statements and reset it afterwards.

SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

{ Your insert statements}

SET [email protected]_SQL_MODE;

Informative article ! my business partners recently encountered https://goo.gl/4sOmJy, to sign pdf ! It's quite uncomplicated to try out and it's useful , I received notice that they are offering a free promotion now

Rattling nice pattern and good subject matter, hardly anything else we need D. cdkaabbaegefcfka

Mine was caused by having zero in the csv file autoincrement field

REPAIR TABLE wp_postmeta

It worked for me.

Php memory was okay, Apache memory was okay, htaccess was okay, etc etc.

The table was corrupted somehow (probably an interruption when someone was uploading an image on the backend and say, navigating from the web page, breaking the complete "INSERT" command WP does. So, just repair the table and voila! Works.