MySQL: SQL Error (1062): Duplicate entry '16777215' for key 'PRIMARY'

I was performing an update similar to this one:

UPDATE table1 
INNER JOIN table2 ON table2.id = table1.id
INNER JOIN table3 On table3.id= table2.id
INNER JOIN table4 On table4.id = table3.id
INNER JOIN table5 ON table5.id= table4.id
SET table5.testId = table1.testId,
WHERE table1.areaId = 0;

And receiving this error:

/* SQL Error (1062): Duplicate entry '16777215' for key 'PRIMARY' */

My first thought was I reached the limit of one of my primary keys. The only table I was updating is table5. I looked at table5 and the largest value for the Primary key wasn't even close to 16777215. It was only 225.

I looked at each table for the max Primary keys. Still nothing.

I turned the UPDATE into an equivalent SELECT so I could see the data, and I still didn't see any values coming close to 16777215.

Then after looking at each table in a SQL browser, I realized I had a Trigger assigned to table5. The Trigger was recording updates made to table5 to another table and reached the limit of its PRIMARY KEY, which was MEDIUMINT. MEDIUMINT has an unsigned limit of 16777215, which is what I set that field to and coincides with the error.

I ALTER'd the update table's Primary Key to an INT and the problem was fixed:

ALTER TABLE `table5_updates` CHANGE COLUMN `id` `id` INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;

I could prevent this if I ever get around to creating a script to detect if a table is reaching its Primary Key's limit.

Comments

New Comment