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!
Best Answer
I found the answer that works for me in another thread here https://stackoverflow.com/a/34810007/2810565I :
Go to PhpMyAdmin and run this query:
Run Reindexing from Magento Admin Panel or from CLI;
After the reindexing is complete, run this query from PhpMyAdmin: