Is it safe to truncate the catalog_category_product table?

Question

I was trying to reindex Category Products and I kept getting "There was a problem with reindexing process" error message. When I into SSH and tried 'php indexer.php --reindex catalog_category_product', I got:

Category Products index process unknown error:

exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (account_mage142.mg_catalog_category_product_index, CONSTRAINT FK_MG_CAT_CTGR_PRD_IDX_CTGR_ID_MG_CAT_CTGR_ENTT_ENTT_ID FOREIGN KEY (category_id) REFERENCES mg_catalog_category_entity)' in /home/account/public_html/magento/lib/Zend/Db/Statement/Pdo.php:228

and

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]:

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (account_mage142.mg_catalog_category_product_index, CONSTRAINT FK_MG_CAT_CTGR_PRD_IDX_CTGR_ID_MG_CAT_CTGR_ENTT_ENTT_ID FOREIGN KEY (category_id) REFERENCES mg_catalog_category_entity), query was: INSERT INTO mg_catalog_category_product_index (category_id, product_id, position, is_parent, store_id, visibility) SELECT mg_catalog_category_product_index_idx.category_id, mg_catalog_category_product_index_idx.product_id, mg_catalog_category_product_index_idx.position, mg_catalog_category_product_index_idx.is_parent, mg_catalog_category_product_index_idx.store_id, mg_catalog_category_product_index_idx.visibility FROM mg_catalog_category_product_index_idx ON DUPLICATE KEY UPDATE category_id = VALUES(category_id), product_id = VALUES(product_id), position = VALUES(position), is_parent = VALUES(is_parent), store_id = VALUES(store_id), visibility = VALUES(visibility)' in /home/account/public_html/magento/lib/Zend/Db/Statement/Pdo.php:235

I was advised to truncate the mg_catalog_category_product table but I was wondering if it is safe to do so.. will removing the data in this table have any adverse effects to the data in the Magento store?

Answer 1

It took some digging, but I eventually found this article. Problem solved. :) http://support.createhosting.co.nz/knowledgebase.php?action=displayarticle&id=70

EDIT:

Mark as answered, details from link below for future reference:

This error occurs when trying to run the Category Products index, and is due to the indexing process attempting to insert a row into the catalog_category_product_index table, where either the product_id or the category_id don't exist. This causes the "Intergrity Constraint Violation".

This can sometimes happen when a database gets corrupt, often from 3rd party desktop type software or a badly written extension.

To fix this, first check that this is indeed the issue. Backup your database and then run the following SQL to see if any product and/or category associations are incorrect:

# Determine incorrect product associations
SELECT * FROM `catalog_category_product` WHERE
product_id not in (select entity_id from catalog_product_entity);

# Determine incorrect categories
SELECT * FROM `catalog_category_product` WHERE 
category_id not in (select entity_id from catalog_category_entity);

If that returns a record, you'll know exactly which product is in a non-existent category or which category has a non-existent product. Run the following SQL to then remove those invalid records:

# Delete incorrect product associations
DELETE FROM `catalog_category_product` WHERE 
product_id not in (select entity_id from catalog_product_entity);

# Delete incorrect categories
DELETE FROM `catalog_category_product` WHERE 
category_id not in (select entity_id from catalog_category_entity);

Now log back into Magento Admin and reindex the Category Products index.

Answer 2

DO NOT TRUNCATE THE TABLE catalog_category_product TABLE. It is the table that binds your product with different categories.

Your error gives the hint that, Magento is trying to insert a new row in the table mg_catalog_category_product_index which has a category_id value that is not present in mg_catalog_category_entity.

Now catalog_category_entity table will have an entry corresponds to every categories in your site. So a category id which is not present in this table is an "unknown" entity as far as Magento concerned. Magento enforces this by defining a foreign key in catalog_category_product_index table.

Errors like this very hard to debug. I am now quiet busy. If I find some fair time, I will update this answer and will try to help you.

Answer 3

In word, it is not safe to truncate the table

In magento, products and there categories relation has been saved at table catalog_category_product .catalog_category_product is the main table for mapped between them.

If you delete the record from this table then the relation between product and category will be lost.

So,please donot truncate the records.

[Edit]

You can may be resolved the issue by delete the those records:

1. product ids which does not exit in catalog_product_entity(main product table).

Query:

DELETE FROM catalog_category_product WHERE product_id not in (select entity_id from catalog_product_entity)

2. category ids which does not exit in catalog_cateory_entity(main category table).

Query:

DELETE FROM `catalog_category_product` WHERE 
category_id not in (select entity_id from catalog_category_entity)