CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Matt Morrison
Matt Morrison

Reputation: 1550

Duplicate entry for key 'PRIMARY' - even though it is set to AUTO_INCREMENT

Bit of a strange one - I haven't changed the config, database, PHP code for this site for about 8 years. The whole time the guy I made it for has been adding, removing, changing stock with no issue, and suddenly today gets this error:

Duplicate entry '2541' for key 'PRIMARY'

executing (inserted generic values for the texts):

INSERT INTO stock (id,title,category,description,price,last_update,sold) VALUES(NULL,'Item name','72','Item description','0',1613723525,'no')

Searching around seemed to suggest this was a common problem when the primary key is not set to auto increment - makes sense. However, checking it out through phpMyAdmin, it definitely is.

enter image description here

Is there something in the index being set to primary rather than unique?

There are 5 other tables on the database but none are linked (as in hard links through the SQL, PHP handles all the cross-table stuff).

I checked and indeed there IS an item in the stock table with ID 2541 already. So why is that NULL AUTO_INCREMENT value converting to an existing id?

enter image description here

EDIT

I noticed that a table I created more recently (via MySQL Workbench probably) has a different setup for the indexes, with the addition of an id_UNIQUE index - do I need one of these on the stock table that is causing issues?

enter image description here

Upvotes: 1

Views: 5511

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Based on your description and your comment "Interestingly, each time I refresh the ID it is attempted to insert (and failing on) increments by 1", I suspect that somehow the seed for the autoincrement for that table got changed to some value that was inserted at some time before.

How exactly that could happen I don't know.

Now, each time you attempt to insert a record this internal counter increments, so you see in the error message that the number increases (2541, 2542, ...) When you attempt to insert a row the internal counter increments regardless of whether the transaction is committed to the database or not. In your case the insert operation is rolled back, because the generated value violates the unique constraint and the internal counter keeps growing.

To change the seed you can run the following statement:

ALTER TABLE stock AUTO_INCREMENT=1234567;

You'll need to set it to the current MAX value that exists in the table, so that new entries that the system attempts to insert do not conflict.

See also How to set initial value and auto increment in MySQL?

This answer shows how to change the autoincrement seed in MySQL Workbench and in PhpMyAdmin. Maybe somebody did this accidentally and didn't notice.

Upvotes: 2

Related Questions