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

Posted Oct 09, 2013

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.

Discussions

Comments are currently turned off

Latest Discussions

  • Carlos: Thank you!
    May 16, 2014 | Replies (0)
  • Jessica: Great info, thanks!
    May 15, 2014 | Replies (1)
  • Walter: Thanks, you totally saved me a bunch of searching around just now. I was setting up a percona xtradb...
    Feb 25, 2014 | Replies (0)
  • Avi: I've found the reason of the problem in my case. It's a bug as described here :http://bugs.mysql.com/bug.php?id=68892Shortly:...
    Dec 12, 2013 | Replies (0)
  • Avi: I've also met that behavior twice on 5.6.12 slaves. However, i didn't find the root cause of that....
    Dec 12, 2013 | Replies (0)