Magento sql errors

Question

Do you have an idea about this error

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1421062-85-1-2' for key 1

it appears when trying to save a product (BO and FO)

Answer 1

I've had this same problem when upgrading a 1.3 to 1.4: it turned out that there was a lot of duplicates entries (yay, "that's what she said"). Solution was to locate and eliminate them. Here the code to locate them:

SELECT `orig`.`entity_id`, 
   `orig`.`attribute_id`, 
   `orig`.`store_id`, 
   `orig`.`value` as `original_value`, 
   `duplicated`.`value` as `duplicated_value`
FROM `catalog_product_entity_decimal` as `orig` 
INNER JOIN `catalog_product_entity_decimal` as `duplicated` 
    ON (
         `orig`.`entity_id` = `duplicated`.`entity_id` 
         AND `orig`.`store_id` = `duplicated`.`store_id` 
         AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
         AND `orig`.`value_id` > `duplicated`.`value_id`
   )

and then add the unique keys:

ALTER TABLE `catalog_product_entity_decimal` ADD UNIQUE KEY `IDX_BASE` `entity_type_id`,`entity_id`,`attribute_id`,`store_id`);
ALTER TABLE `catalog_product_entity_int`  ADD UNIQUE KEY `IDX_BASE` `entity_type_id`,`entity_id`,`attribute_id`,`store_id`);
ALTER TABLE `catalog_product_entity_varchar` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`);
ALTER TABLE `catalog_product_entity_text` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`);
ALTER TABLE `catalog_product_entity_datetime` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`);

/* Add unique keys to customer eav tables */
ALTER TABLE `customer_entity_decimal` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_entity_int`  ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_entity_varchar` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_entity_text` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_entity_datetime` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);

/* Add unique keys to customer address eav tables */
ALTER TABLE `customer_address_entity_decimal` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_address_entity_int`  ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_address_entity_varchar` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_address_entity_text` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_address_entity_datetime` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);

And take Jonathan's comment into account :)

Answer 2

Your syntax is wrong on the ALTER TABLE code. It's missing two ('s in the first two lines. Here's updated:

ALTER TABLE `catalog_product_entity_decimal` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`);
ALTER TABLE `catalog_product_entity_int`  ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`);
ALTER TABLE `catalog_product_entity_varchar` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`);
ALTER TABLE `catalog_product_entity_text` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`);
ALTER TABLE `catalog_product_entity_datetime` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`);

/* Add unique keys to customer eav tables */
ALTER TABLE `customer_entity_decimal` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_entity_int`  ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_entity_varchar` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_entity_text` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_entity_datetime` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);

/* Add unique keys to customer address eav tables */
ALTER TABLE `customer_address_entity_decimal` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_address_entity_int`  ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_address_entity_varchar` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_address_entity_text` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);
ALTER TABLE `customer_address_entity_datetime` ADD UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`);