An unexpected glitch occurred today on a MySQL server that’s been running for ages. It contains a database used by a content management system, and that CMS stores a visitor record for each visitor that enters the website. So far, it had stored 311669 records. And on inserting yet another record, it failed with
#Error 1062 – Duplicate entry ‘311670’ for key ‘PRIMARY'.
As it turns out, the primary key for the visitors table was of type INT(11), and apparently INT(11) refuses to accept numbers larger than 311669 for AUTO_INCREMENT – even when you don’t rely on AUTO_INCREMENT but provide an actual key value (311670) yourself.
I used to think that the maximum value for INT(11) should be about 4 billion (2^32), but apparently it’s quite a bit less (311669, apparently) which could be a problem for other integer values elsewhere in my databases. The solution to this particular problem was to alter the table and set the type of the primary key column to BIGINT(20). That way, it’ll probably keep on running until the end of the universe.