Magento: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ’65535′ for key 1

Background

Good old C programmers (like me) immediately can see something suspicious about the number 65535 – instant flag that somebody’s used a short where a long was wanted. (Back in the days when a short was 16 bits… I am dating myself here).

In any case, this has been going on for too long with my Magento install and I finally tracked the darn thing down. The problem as noted here, in Magento 1.4.0.0-rc1 release notes, fix #17720. While it’s been fixed in a newer release, do you think there’s a snowball’s chance in Hades that I’m upgrading my production server before the holiday season is over?

Error Message

The error messages is displayed (in our particular case) when updating products. It reads “SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ’65535′ for key 1″, and the screen looks (partially) like this:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '65535' for key 1
Error Message as it appears in Magento Admin

The Problem

The Magento table “core_flag” has a flag_id field defined as (you guessed it) a small int. So, I used phpMyAdmin (I’m hosted on nexcess) to alter the field to a … ta-da! … a BIG INT:

The Solution

ALTER TABLE `core_flag`
     CHANGE `flag_id` `flag_id`
     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

Your Mileage May Vary

As always, remember what you paid for this advice, and be sure to test it out on your Test Server!

DataFlow Bonus Today!

In tracking the problem down, Nexcess advised me to review this document, which, while it didn’t fix the problem, did help me get some performance improvements and a better understanding of Magentos Import/ Export issues.

Facebook comments: