Magento 1.8 reindex all

Question

Since a few weeks our reindex will not work anymore and crash with the following error:

Product Attributes 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 (`hcomputers_mag`.`catalog_product_index_eav`, 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)' in /var/www/hcomputers/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/hcomputers/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/hcomputers/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/hcomputers/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/hcomputers/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /var/www/hcomputers/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#5 /var/www/hcomputers/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#6 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#7 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(48): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_product...', Array, false)
#8 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(153): Mage_Index_Model_Resource_Helper_Mysql4->insertData(Object(Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source), Object(Varien_Db_Select), 'catalog_product...', Array, false)
#9 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_product...', 'catalog_product...', false)
#10 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): Mage_Index_Model_Resource_Abstract->syncData()
#11 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll()
#12 /var/www/hcomputers/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll()
#13 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll()
#14 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#15 /var/www/hcomputers/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()
#16 /var/www/hcomputers/shell/indexer.php(212): Mage_Shell_Compiler->run()
#17 {main}

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 (`hcomputers_mag`.`catalog_product_index_eav`, 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)' in /var/www/hcomputers/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /var/www/hcomputers/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/hcomputers/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /var/www/hcomputers/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /var/www/hcomputers/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#4 /var/www/hcomputers/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#5 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#6 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(48): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_product...', Array, false)
#7 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(153): Mage_Index_Model_Resource_Helper_Mysql4->insertData(Object(Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source), Object(Varien_Db_Select), 'catalog_product...', Array, false)
#8 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_product...', 'catalog_product...', false)
#9 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): Mage_Index_Model_Resource_Abstract->syncData()
#10 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll()
#11 /var/www/hcomputers/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll()
#12 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll()
#13 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#14 /var/www/hcomputers/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()
#15 /var/www/hcomputers/shell/indexer.php(212): Mage_Shell_Compiler->run()

Things I've tried:

Truncate all the catalog_category_flat_store_# (1 - 40) tables
Truncate all the catalog_product_flat_# (1 - 40) tables
Tried, as suggested somewhere else to delete the constraints.
Ran the Reindexer while apache was turned off.

The weird thing is, that the "Product Prices" index's status is still Processing while there is no indexer running. When I try to remove the lock files in var/locks they are immediately generated again like there is still something trying to index, which isn't.

Any help'd be appreciated, since we're running a blank here!

Have you tried this solution: magento.stackexchange.com/questions/9091/… — mageUz, Commented Jan 4, 2014 at 9:18

Alana Storm · Accepted Answer · 2014-01-03 22:12:35Z

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!

Thanks for your answer, I appreciate the work you did. Unfortunately all entity_id rows in both the catalog_product_index_eav and catalog_product_index_eav_idx tables are present in catalog_product_entity. — Robert van der Meulen, Commented Jan 4, 2014 at 8:53

Ron · Accepted Answer · 2014-01-06 20:55:14Z

One of the best ways to resolve DB corruption issues such as these is by running the DB Repair Tool. There is a wiki page that describes how to use it on Magento.

Essentially you make a copy of your corrupt DB and compare it to a clean DB instance of the same version of Magento as to what you are currently running. The tool even offers automatic correction for any core tables and gives a nice report at the end of the process.

A couple of notes:

Always use a staging server or a separate DB server for this process.
Make sure you have a powerful enough DB server that can handle this operation it is very heavy.
Document your steps and the results as it will simplify the process of getting your live server back in order.

Community · Accepted Answer · 2017-05-23 12:37:13Z

0

I've used this one quite a bit recently as an integrity check.

https://stackoverflow.com/a/12085689/462935

edited May 23, 2017 at 12:37

CommunityBot

1

answered Jan 6, 2014 at 15:42

Wilson Sheldon

3014 silver badges13 bronze badges

Add a comment |

Robert van der Meulen · Accepted Answer · 2014-01-04 11:34:40Z

-2

I'd like to answer my own question. http://www.magentocommerce.com/boards/viewthread/265847/

The reply from Olivned helped me out.

Thanks for your help!

answered Jan 4, 2014 at 11:34

Robert van der Meulen

11 silver badge3 bronze badges

Add a comment |

Stack Exchange Network

Magento 1.8 reindex all

4 Answers 4

Your Answer

Not the answer you're looking for? Browse other questions tagged
magento-1.8
indexing
reindex
or ask your own question.

Linked

Hot Network Questions

Magento 1.8 reindex all

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged magento-1.8indexingreindex or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
magento-1.8
indexing
reindex
or ask your own question.