1

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

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
Matt Morrison
  • 1,540
  • 1
  • 14
  • 19
  • 'this was a common problem when the primary key ' - nope a primary key is a unique key – P.Salmon Feb 19 '21 at 09:15
  • Do you have any triggers? Incidentally, columns called price are not normally DOUBLE. It's why DECIMAL was invented. – Strawberry Feb 19 '21 at 09:15
  • Could you try without specifying the id column in the columns list (and of course the null in the values list)? – Eduard Uta Feb 19 '21 at 09:23
  • Try to remove column `id` (i.e., primary key) in the insert statement MySql will handle and update the unique id without duplicates. – HariKishore Feb 19 '21 at 09:25
  • 1
    @EduardUta and @HariKishore - tried this, unfortunately still the same error even without id specified: `Duplicate entry '2545' for key 'PRIMARY'` Interestingly, each time I refresh the ID it is attempted to insert (and failing on) increments by 1 – Matt Morrison Feb 19 '21 at 11:19

1 Answers1

1

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.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
  • 1
    Absolutely fantastic Vladimir, thank you. Reminds me why I try and provide as much detail as possible when asking a question - and thank you so much for not only reading my question but also reading my responses to other people's comments to find that information. This worked perfectly, and was really easy to fix. I'll try and make myself better acquainted with these sort of functions in the future. No idea how this happened still, no one has access to the database other than me... and I suppose the hosting company... anyway, thanks again! – Matt Morrison Feb 19 '21 at 15:02