SQL Integrity constraint violation: 1062 Duplicate entry

Question

When I'm updating products in my Magento-store externally from my accounting software I'm receiving following error in the logs:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '727-0-4-0'
for key 'CC12C83765B562314470A24F2BDD0F36', query was: INSERT INTO
`catalog_product_entity_group_price` (`entity_id`, `all_groups`,
`customer_group_id`, `value`, `website_id`) VALUES (?, ?, ?, ?, ?)

How do I fix this?

Answer 1

Whenever there is an issue related to "SQL Integrity constraint", I have tried doing the below and this has worked for me very well:

Each time, you plan to update products using Magento Admin Panel or Another source in Magento.

Magento Enterprise Edition

Navigate to System > Configuration > Advanced > Index Management > Index Options > Set all the options to "Update when scheduled"

Magento Community Edition

Navigate to System > Index Management > Select all > Actions > Change Index Mode > Manual Update > Save

These settings are to avoid any SQL Integrity Constraint errors during Bulk/Single Product Updates.

After products are updated, revert the changes back as below:

Magento Enterprise Edition

Navigate to System > Configuration > Advanced > Index Management > Index Options > Set all the options to "Update on Save" > Save

Magento Community Edition

Navigate to System > Index Management > Select all > Actions > Change Index Mode > Update on Save > Save

Note :: Make sure the /var/locks is removed before setting these and before doing a bulk product update.

Let me know if this helps.

Happy Coding...