john.parnefjord.se Reflections & findings

The other day I was struck by a strange error in a MySQL 5.0 database. A PHP-page that I've made to upload files suddenly stopped working with the error message:

Duplicate entry '112' for key 1 

I could quickly pinpoint the table that caused the trouble. Checking the table I found that there were 112 rows and the next id to be used for the auto incrementing primary key was 113. Yet MySQL stubbornly tried to add a new rows with the id 112, which naturally failed. Why use a key that already has been used? For some reason the sequence for the table had become inconsistent. In this particular case the solution was to run a check and repair of the table:

CHECK TABLE mytable
OPTIMIZE TABLE mytable

If you have PhpMyAdmin you can do this operation by choosing database and then selecting the table in question. Under the Operations tab

you will find the heading Table maintenance. Run Check table and Optimize table.

If you face the error Duplicate entry '127' for key 1 your problem might not be solved by this. Most likely this has to do with the data type that the key is using. In this case  tinyint is probably used which will not permit values higher than this. Convert the field to integer or bigint instead.