Magento 1.6.2 不能重新索引产品平面数据

我们的 magento 1.6.2 不能重新索引产品平面数据.它有时还会显示重新索引过程出现问题".

我从其他用户的经验中尝试了许多解决方案.没有结果.我们进口了大宗产品，但我们不确定这是重新索引问题的原因.

理想的解决方案是什么?

这是我在shell中使用的命令:

php/usr/local/lsws/fcgi-bin/lsphp5/home/user/domains/my domain../public_html/shell/indexer.php

这是异常日志中的错误

2012-08-09T14:55:43+00:00 DEBUG (7):异常消息:SQLSTATE[23000]:完整性约束违规:1452无法添加或更新子行:外键约束失败(`galagearbox/#sql-1662_1fc419`，约束`FK_CAT_PRD_FLAT_4_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_TEELE`) ON CA D跟踪:#0/home/corecolu/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)#1/home/corecolu/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)#2/home/corecolu/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)#3/home/corecolu/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)#4/home/corecolu/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ca...', Array)#5/home/corecolu/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ca...', Array)#6/home/corecolu/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(327): Varien_Db_Adapter_Pdo_Mysql->query('ALTER TABLE `ca...')#7/home/corecolu/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(2548): Varien_Db_Adapter_Pdo_Mysql->raw_query('ALTER TABLE `ca...')#8/home/corecolu/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(816): Varien_Db_Adapter_Pdo_Mysql->addForeignKey('FK_CAT_PRD_FLAT...', 'catalog_product...', 'entity_id', 'catalog_product...', 'entity_id', 'CASCADE', 'CASCADE')#9/home/corecolu/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(1390): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prepareFlatTable(4)#10/home/corecolu/public_html/app/code/core/Mage/Catalog/Model/Product/Flat/Indexer.php(296): Mage_Catalog_Model_Resource_Product_Flat_Indexer->reindexAll()#11/home/corecolu/public_html/app/code/core/Mage/Catalog/Model/Product/Indexer/Flat.php(336): Mage_Catalog_Model_Product_Flat_Indexer->reindexAll()#12/home/corecolu/public_html/app/code/core/Mage/Index/Model/Process.php(207): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()#13/home/corecolu/public_html/app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()#14/home/corecolu/public_html/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()#15/home/corecolu/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(420): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()#16/home/corecolu/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')#17/home/corecolu/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))#18/home/corecolu/public_html/app/code/core/Mage/Core/Model/App.php(349): Mage_Core_Controller_Varien_Front->dispatch()#19/home/corecolu/public_html/app/Mage.php(640): Mage_Core_Model_App->run(Array)#20/home/corecolu/public_html/index.php(83): Mage::run('', 'store')#21 {主要}

解决方案

在 Magento 中重新索引错误可能有很多原因，所以恐怕永远不会有一个理想"的解决方案涵盖所有可能的错误.

由于您发布了非技术性错误消息，我猜您已经尝试使用管理后端重新索引产品平面数据并在那里看到错误消息.

我建议再次尝试重新索引，但这次使用 shell/indexer.php:

/usr/bin/php -f/abs/path/to/shell/indexer.php -- --reindex catalog_product_flat

这样，如果重新索引失败，您将获得更有意义的技术错误消息.

这是一个列表，显示了哪个参数用于哪个索引:

catalog_product_attribute 产品属性catalog_product_price 产品价格catalog_url 目录 URL 重写catalog_product_flat 产品平面数据catalog_category_flat 类别平面数据catalog_category_product 分类产品catalogsearch_fulltext 目录搜索索引cataloginventory_stock 库存状态tag_summary 标签聚合数据

更新

您在帖子中添加的例外表明，

catalog_product_flat_4.entity_id

和

catalog_product_entity.entity_id

发现已损坏.

通常这意味着某些记录在 catalog_product_entity.entity_id 字段中包含 null，它们应该包含现有的 entity_id代码>s.

在尝试解决此问题之前，请先备份您的数据库.

现在，要查找损坏的记录，请使用以下命令查询您的数据库:

SELECTa.entity_id从catalog_product_flat_4 作为左加入catalog_product_entity AS b ON a.entity_id = b.entity_id在哪里ISNULL(b.entity_id)

本次查询找到的entity_id是catalog_product_flat_4表中的entity_id，已损坏，需要删除.>

删除catalog_product_flat_4中的相应记录，然后再次尝试重新索引.

Our magento 1.6.2 cant not reindex product flat data. It also sometimes shows 'There was a problem with reindexing process.'

I tried many solutions from the experience of other users. No result. We imported bulk products but we are not sure it's the cause of reindexing problem.

What could be the ideal solutions?

This is the command i used in shell:

php /usr/local/lsws/fcgi-bin/lsphp5 /home/user/domains/my domain../public_html/shell/indexer.php 

This is the error from exception.log

2012-08-09T14:55:43+00:00 DEBUG (7): Exception message: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`galagearbox/#sql-1662_1fc419`, CONSTRAINT `FK_CAT_PRD_FLAT_4_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCA)
Trace: #0 /home/corecolu/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/corecolu/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/corecolu/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/corecolu/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/corecolu/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ca...', Array)
#5 /home/corecolu/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ca...', Array)
#6 /home/corecolu/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(327): Varien_Db_Adapter_Pdo_Mysql->query('ALTER TABLE `ca...')
#7 /home/corecolu/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(2548): Varien_Db_Adapter_Pdo_Mysql->raw_query('ALTER TABLE `ca...')
#8 /home/corecolu/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(816): Varien_Db_Adapter_Pdo_Mysql->addForeignKey('FK_CAT_PRD_FLAT...', 'catalog_product...', 'entity_id', 'catalog_product...', 'entity_id', 'CASCADE', 'CASCADE')
#9 /home/corecolu/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(1390): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prepareFlatTable(4)
#10 /home/corecolu/public_html/app/code/core/Mage/Catalog/Model/Product/Flat/Indexer.php(296): Mage_Catalog_Model_Resource_Product_Flat_Indexer->reindexAll()
#11 /home/corecolu/public_html/app/code/core/Mage/Catalog/Model/Product/Indexer/Flat.php(336): Mage_Catalog_Model_Product_Flat_Indexer->reindexAll()
#12 /home/corecolu/public_html/app/code/core/Mage/Index/Model/Process.php(207): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#13 /home/corecolu/public_html/app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#14 /home/corecolu/public_html/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#15 /home/corecolu/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(420): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#16 /home/corecolu/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#17 /home/corecolu/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#18 /home/corecolu/public_html/app/code/core/Mage/Core/Model/App.php(349): Mage_Core_Controller_Varien_Front->dispatch()
#19 /home/corecolu/public_html/app/Mage.php(640): Mage_Core_Model_App->run(Array)
#20 /home/corecolu/public_html/index.php(83): Mage::run('', 'store')
#21 {main}

解决方案

Reindexing errors in Magento can have many reasons, so I'm afraid there never will be an "ideal" solution covering all possible errors.

Since you've posted a non-technical error message, I guess you've tried to reindex the product flat data using the admin backend and saw the error message there.

I'd recommend to try to reindex again, but using the shell/indexer.php this time:

/usr/bin/php -f /abs/path/to/shell/indexer.php -- --reindex catalog_product_flat

This way you'll get more meaningful technical error messages, if reindexing fails.

Here's a list that shows which parameter to use for which index:

catalog_product_attribute     Product Attributes
catalog_product_price         Product Prices
catalog_url                   Catalog URL Rewrites
catalog_product_flat          Product Flat Data
catalog_category_flat         Category Flat Data
catalog_category_product      Category Products
catalogsearch_fulltext        Catalog Search Index
cataloginventory_stock        Stock Status
tag_summary                   Tag Aggregation Data

UPDATE

The exception you've added to your post tells, that the referential integrity between

catalog_product_flat_4.entity_id

and

catalog_product_entity.entity_id

was found being corrupted.

Usually this means some record(s) contain(s) null in the field catalog_product_entity.entity_id, where they are expected to contain existing entity_ids.

Before trying to fix this, please backup your database first.

Now, to find the corrupted record(s), query your database with:

SELECT
    a.entity_id
FROM
    catalog_product_flat_4 AS a
LEFT JOIN
    catalog_product_entity AS b ON a.entity_id = b.entity_id
WHERE
    ISNULL(b.entity_id)

The entity_ids found by this query are entity_ids in the table catalog_product_flat_4, which are corrupted and need to be deleted.

Delete the appropriate records in catalog_product_flat_4, and then try to reindex again.