MySQL reached max integer limit

Whenever anything generates a MySQL error at work, the whole technical team gets an email about it, with full debug info. We recently got one for an area that I look after, so it was up to me to investigate. The title of the error was a slightly cryptic

Error: Duplicate entry ‘0’ for key 1

Clearly this had something to do with the primary key, which was a simple unique integer ID. As this is a pretty large table, I had a feeling that we had reached the upper limit of what could be stored for the field type (MySQL’s MEDIUMINT, signed). Looking at the maximum value for the ID, I saw it was 8388607; according to this table on the MySQL website, this value is the maximum that can be stored in that type of field, so this was clearly the problem. (N.B. an auto-incrementing field shouldn’t ever be defined as signed, as you’ll never go into the negative indices, but that was done before I came along!)

The solution? Surely a simple readjusting of the key to be unsigned, or to further future proofing, changing to the larger INT… That’s what I thought and quickly (in test first, of course!) did a

This should then change the maximum value allowed in the ID field to 16777215. So, to test this, I went to insert a row, specifying NULL for the primary key value. However, I still got the same error. Doing a DESC on the table told me that the ID field had been changed to MEDIUMINT UNSIGNED correctly, so that wasn’t the issue. After further research I determined that what happend was that MySQL’s internal counter for that auto increment field was still set to 0, due to the rollover caused after reaching the maximum integer value. To overcome this, you need to point the internal counter back to where it should be, i.e. the current maximum value of your ID field, as follows:

So, you set it to a value that’s one greater than the current maximum ID.