Magento – Is it safe to truncate the catalog_category_product table

catalogindexingMySQL

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?

Best Answer

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.

Related Solutions

Magento – Magento 1.8 reindex all

The catalog_product_index_eav table has a foreign key contraint named

FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

Looking at this table's definition

CREATE TABLE `catalog_product_index_eav` (
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Value',
  PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID` (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`),
  CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product EAV Index Table';

we can see the foreign key definition is

CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` 
FOREIGN KEY (`entity_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) 
ON DELETE CASCADE ON UPDATE CASCADE

This means for every entity_id row in catalog_product_index_eav, there needs to be an identical, corresponding entity_id value in catalog_product_entity.

The root of your problem is for some reason (either a rouge extension, errors caused by randomly typing in SQL from the internet, or performing data updates with disabled indexes), Magento's indexing attempts to update data in catalog_product_index_eav that violates this rule. The next step is identifying what Magento's doing so you can fix the data.

If we look at your call stack, this looks like a good place to start debugging

#10 app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): 
Mage_Index_Model_Resource_Abstract->syncData()

Jumping to that source file, we see the following bit of code

public function syncData()
{
    $this->beginTransaction();
    try {
        /**
         * Can't use truncate because of transaction
         */
        $this->_getWriteAdapter()->delete($this->getMainTable());
        $this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
        $this->commit();
    } catch (Exception $e) {
        $this->rollBack();
        throw $e;
    }
    return $this;
}

As part of its indexing process, Magento tries to sync data from an "index table" (getIdxTable), to a "source" (getMainTable) table.

public function insertFromTable($sourceTable, $destTable, $readToIndex = true)
{
    //...
}

For this particular index, the index table is catalog_product_index_eav_idx, and the source table is catalog_product_index_eav.

Note: Be careful with your terminology around here, things are confusingly named. The "Source" table is the table we're copying to. (I believe it's called the source table because it's the "source" a normal Magento system will query from when it needs information)

So, Magento is trying to sync a row from catalog_product_index_eav_idx to the table catalog_product_index_eav. However, this causes the previously mentioned foreign key error. This leads us to two possible conclusions

The catalog_product_index_eav_idx has entity_id rows that do not exist in catalog_product_entity.
The catalog_product_index_eav table has (through previous manipulation with index checks turned off) entity_id rows that do not exist in catalog_product_entity.

So, your mission here is to figure out which entity_id rows in catalog_product_index_eav and catalog_product_index_eav_idx don't exist in catalog_product_entity, and manually delete said rows (from catalog_product_index_eav and catalog_product_index_eav_idx).

If it were me, and my catalog_product_entity table wasn't too large, I'd start with the following queries (these are untested, as I don't have any Magento tables with the above invalid data states)

SELECT * 
FROM catalog_product_index_eav_idx 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

SELECT * FROM catalog_product_index_eav 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

Good luck!

Magento – Index error after upgrade to EE 1.14.2.0: table catalog_product_entity_tmp_indexer doesn’t exist

There's a Magento patch SUPEE-5984, which addresses these kind of Enterprise Refresh Index problems.

Best Answer

Related Solutions

Magento – Magento 1.8 reindex all

Magento – Index error after upgrade to EE 1.14.2.0: table catalog_product_entity_tmp_indexer doesn’t exist

Related Topic