I have to mass import all products of a given website by script using models. My client has made some errors while building the configurables in his ERP and we had to flush all the products using this query:
DELETE FROM `catalog_product_entity` WHERE `catalog_product_entity`.`entity_id` IN (SELECT `catalog_product_website`.`product_id` from `catalog_product_website` WHERE `website_id` = 1);
The we restarted the import task. While runnning the script (max 500 products every 30 minutes, indexation disabled), most of the products are imported nicely but we get many Integrity constraint violation errors like this one:
Error: SQLSTATE: Integrity constraint violation: 1062 Duplicate entry '18433-150' for key 'UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID', query was: INSERT INTO `catalog_product_super_attribute` (`product_id`, `attribute_id`, `position`) VALUES (?, ?, ?)
I can see by the given product_id that it is a new product but when I look in the table catalog_product_super_attribute, I don't see this product_id. This ID is not listed in the admin either. We will fix the product creation but where is the missing ID? I see 18431, 18432, and then 18434! That happened quite a few times, but not on every configurable creation.
It's as if Magento has prepared itself to save a new configurable, failed to do so, rolled back, discarded the ID but a reference to this ID lies somewhere.
Have you seen such a behavior? Is there a way to prevent this?