Some weeks ago  I was trying to import/update some products in Magento 1.7 and I got the following error:

An error occurred while saving this configuration: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-1332892800-0-1-0-209-0' for key 'EAA51B56FF092A0DCB795D1CEF812B7B'

The error message by itself doesn’t help that much and trying to google it didn’t bring any relevant result.

After a long time debugging and trying to figure out where the key ‘EAA51B56FF092A0DCB795D1CEF812B7B’ comes from, I found it in the  “catalogrule_product” table. It is a unique key which makes sure that the values in the columns: “rule_id”, “from_time”, “to_time”, “website_id”, “customer_group_id”, “product_id” and “sort_order” are unique.

WHY THIS PROBLEM HAPPENS?

Two things happen if you try to update a product using the Magento import module (In the backend Menu: System->import/export->import) and you have an active catalog promotion rule, which  applies to that product. First Magento deletes (or at least it tries to delete) all rows, related to the product, in the “catalogrule_product” table. Then Magento inserts the deleted rows again (strange I know, but this is how it works). Due to a bug in Magento core code, the delete step fails.

In the file app/code/core/Mage/CatalogRule/Model/Resource/Rule.php, lines 89-93, Magento tries to delete the rows using the code bellow:

$write->delete(
$this->getTable('catalogrule/rule_product'),
$write->quoteInto('rule_id=?', $ruleId)
. $write->quoteInto('and product_id in (?)', implode(',' , $rule->getProductsFilter()))
);

It generates a delete SQL statement, similar to:

DELETE FROM 'catalogrule_product' WHERE rule_id = '1' AND product_id IN ('1,2,3,4')

However, the correct SQL statement should be:

DELETE FROM 'catalogrule_product' WHERE rule_id = '1' AND product_id IN ('1','2','3','4')

Note the difference in the “IN” clause. Because of that Magento doesn’t delete all the rows that should be deleted and when it tries to insert them again, you get a “Duplicate entry” error.

HOW TO FIX IT?

You can fix the error using one of the following methods:

METHOD 1

Deactivate all catalog promotion rules:

• In the backend menu: Promotions-> Catalog Price Rules
• Set the status of all the rules to inactive
• Apply the rules

Now you can Import/update free of errors.  When the import/update is done, you can active the rules again.

METHOD 2

Using the following SQL command to truncate tables:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE `catalogrule`;
TRUNCATE `catalogrule_affected_product`;
TRUNCATE `catalogrule_customer_group`;
TRUNCATE `catalogrule_group_website`;
TRUNCATE `catalogrule_product`;
TRUNCATE `catalogrule_product_price`;
TRUNCATE `catalogrule_website`;

SET FOREIGN_KEY_CHECKS = 1;