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!
The error is because the transaction is reaching its timeout period. If you did not set this in your my.cnf
the default is 50 seconds I would highly recommend you look at your sever settings to ensure that they are optimized for your database using tools such as MySQLTuner
I have the innodb_lock_wait_timeout
set to 7200
(2 hours) in a store with 500k products with 2 store views.
Example to add or change in my.cnf
innodb_lock_wait_timeout = 7200
DO NOT TRUNCATE.... yet
When you "moved around or reorganised a category" Magento will keep the references to these original links (hence the massive amount of links and 4 store views as well) and redirect them for visitors / bots / search engines that have book marked or indexed these pages.This can negatively affect your SEO, when you truncate
they will receive a error 404
message. My personal recommendation is BEFORE doing major changes to the structure, ensure that nothing of the sort has been done for at least a month, then truncate
then do your restructuring. This will cut down enormously on the size of your URL Rewrites
Since you've already re-arranged your categories, the best thing to do now is wait for a month to give visitors / bots / search engines the time to update the links --- then purge.
Also checkout:Magento core_url_rewrite table excessively large
Best Answer
The question is vague as it is about a configuration issue and for these the exact reason needs to be known which (often) requires insight into the concrete system. So bare with me as an answer can only be limited.
I personally could handle the error message by raising the default (no value configured, it was 16M effectively) to
tmp_table_size = 64M
/max_heap_table_size = 64M
and it solved that problem.That was Magento EE 2.1.0.
A co-worker had this problem as well, but raising to 64M, 128M, 256M, 512M etc. up to 2G did not solve the problem. Just saying. The only way the problem could be solved while re-indexing was to load a (new/other/different) db-dump which did not cause that issue. Would be interesting to learn what could cause this error as well. As it was a development version, we invested no further research on the issue then.