Hoping I understood the problem at hand: and the question...
This part of the error message is telling:
startlondon_live`.`#sql-448_15e`, CONSTRAINT `FK_MAG_MAG_CAT_PRD_FLAT_1_ENTT_ID_MAG_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `mag_catalog_product_entity` (`entity_id`)...
What this is telling me is that the column startlondon_live
is referencing a catalog product entity id column (mag_catalog_product_entity
) on the entity field but that entry or field no longer exists. The name of the foreign key is not formatted as the Magento installer would name it, so that tells me it is custom.
In essence the constraint you've created from another table is failing in the indexer because the target column either no longer exists or will be deleted, orphaning the child.
This brings me to a few thoughts:
- When creating new fields in either custom resource models or to extend the Magento catalog it is unnecessary to modify flat tables.
- It may also be unnecessary to create foreign key relationships to entity_id's on flat tables. FK relationships enforce schema and prevent data errors or orphaned content but, at the heart of Magento, EAV makes no effort to enforce this schema. Flat tables are built on-the-fly and attributes which expose themselves as Frontend attributes will automagically create necessary columns for the flat tables.
- In the case that this is a custom resource model you don't need to have a FK relationship to the flat table -- instead create the FK relationship to the
catalog_product_entity
table; after all, that's the entity of record in Magento for a product. You may still define your ON DELETE rules.
In the end you'll need to fix this error. Disabling foreign key checks is a recipe for disaster.
Instead, remove your schema edits from Magento DB. Disable/enable flat catalog - or in the worst-case scenario drop your catalog_product_flat*
tables and rebuild them.
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
Likely, you have missing or malformed foreign keys on your existing tables, and then the indexer creates a temporary table and tries to setup a foreign key relationship with the permeate tables, it errors.
My suggestion is to run the Magento Database Repair Tool, located here: http://www.magentocommerce.com/wiki/doc/db-repair-tool
This involves creating a fresh installation of Magento with another database, using the same version of Magento that you're site currently uses, and then letting this tool compare the two databases for problems.
Make sure to delete this tool when you're done and test it on a non-production environment first.