Magento 1: Truncate catalog_product_super_link

Question

I am not a very experienced (Magento) Developer, and I'm running into a problem with an

Integrity constraint violation: 1062 Duplicate entry '244344-244359' for key 'UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID'

I've found that the table catalog_product_super_link is used for this and just truncated it.

I am not sure if this is 'safe' to do and what could go wrong? After truncating this everything started working again.

Answer 1

If you 'got away' with truncating the table, its likely that there were no foreign keys, or it was a data storage table (such is your case by deleting the super links, you've ruined all the information linking your child products to the parents). (See @Luke Rodgers comment).

Provided you backed up the database, just restore the table and it should be alright.

I want to comment that after working with Magento for close to 5 years now, I can say that if you find yourself working directly on the database, you are probably not doing it right. Magento (without heavy modification) is pretty adept at maintaining its own database.

I don't mean to sound rude, but truncating a table is like cutting your hand off because its dirty. It would be somewhat easy to track the foreign keys 'backwards' from the truncated table to the master table (namely, the main product entity table) and start by removing a row or two from there. The delete(s) will cascade through all of the child tables with keys associated, thus leaving the database somewhat functional.

I also suggested above, try to use a virtual machine with snapshot capability and whenever you get something working the way you like, make a snapshot. That way, when you do something and the system blows up, you can spend 5 minutes getting coffee while you wait for it to restore your last snapshot vs spending days trying to understand what tiny little thing broke and how to fix it.

EDIT: It just dawned on my you are using Magento 1.0. Here is what you should do to attempt to fix this. First to understand the problem you need to be able to dig into what the UNIQUE KEY is calling out to be unique.

I've uploaded a screenshot of my "dig" into how the key is set up.

You can see that the Unique Key is saying that the catalog_product_super_link table cannot have more than one row with the same product_id and parent_id in it.

You also have 2 Foreign keys on that table, pointing to catalog_product_entity, showing how each is relatively created.

Upon a little further digging, i've found that the catalog_product_relation table seems to be where this relational data is stored. This table's foreign keys reference catalog_product_entity as well, suggesting that they are all combined to create the configurable product.

The possible fix:

First, run this sql query:

SELECT parent_id, count(child_id) childCount FROM PRODCOPY.catalog_product_relation GROUP BY child_id HAVING childCount > 1;

What it does is check the catalog_product_relation table and query all parent products individually, count the children, and find children that have more than one entry per parent (utilizing GROUP BY and the math "greated than one". I am all speculating at this point, but this query should come back with 0 results. If you find that any results come back, find the entity_id in magento admin, and try to reconfigure and save that product's associated products.

Once done with all that, reindex, clear cache, and run the sql command again. If you still find that it returns a value, heck select on that parent id and delete it from the catalog_product_relation table. This should disconnect the child product from the parent at the least. You have backups :) You may get a key constraint error, and at that point your only recourse may be to manually delete the parent product by entity_id from catalog_product_entity. This will 'chain' the deletes properly throughout the database because of all the foreign keys. Then recreate it manually in the admin. The child products should be unaffected, but I've been wrong before.

I hope this helps you, or anyone out trying to diagnose these stupid random integer mysql errors in magento.

Answer 2

I am know this is a quite old question, but I just recently faced with the same issue. After a little digging I found that - in my case - OrganicInternet_SimpleConfigurableProducts module is highly outdated - latest version based on v1.5 and unable to properly pick up the associated products (there is no visible associated product for configurable products in backend) - calling original getUsedProducts function instead of the override shows product w/o problem and I can save configurable product again with no issue.

public function getUsedProducts is responsible for getting associated products - so if you - or anyone whom might will facing the same issue in the future - do not have any associated product in the backend, check if any module wrongly tampering with this method.

Easiest way to do it to insert return parent::getUsedProducts($requiredAttributeIds, $product); as the first line of overrider.