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 […]

This article was posted by Independent Software, a website and database application development company based in Maputo, Mozambique. Our website offers regular write-ups on technical and design issues, ranging from details at code level to 3D Studio Max rendering. Read more about Independent Software's philosophy, or get in touch with Independent Software.

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.

Did this article help you out? Please help us find more time to write useful guides & articles like this by donating a buck or two. It'll keep us coffee-fueled. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *