Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

1.4.1 -> 1.4.2 Upgrade, Category Products indexer slow
 
Keebz
Jr. Member
 
Total Posts:  14
Joined:  2010-08-05
 

After upgrading the ‘category products’ indexer has been running very slow.  I execute it from the built in magento shell script, and this particular index would complete within 30-60minutes before upgrading.  Now it has been running for over a day with mysql showing heavy cpu load.  Other indexes run fine, and the Catalog URL Rewrites index now completes surprisingly fast.

I’ve tried the magento database repair tool and no errors were found.  I upgraded via magento connect manager, and believe it completed properly(taking nearly half a day).  Is there a way of fixing the category products index without flushing all the products and reimporting them back in?

[update]
The index process finally errored out (after 23 hours running):

Category Products index process unknown error:
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint vio
lation: 1452 Cannot add or update a child row: a foreign key constraint fails (`
madeupaccount_cart/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PROD_
IDX_PROD_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity`
(`entity_id`) ON DELETE CASCADE)' 
in /home/madeupaccount/public_html/store/lib/Zend/Db/St
atement
/Pdo.php:228
Stack trace
:
#0 /home/madeupaccount/public_html/store/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement
->execute(Array)
#1 /home/madeupaccount/public_html/store/lib/Zend/Db/Statement.php(300): Zend_Db_Statemen
t_Pdo->_execute(Array)
#2 /home/madeupaccount/public_html/store/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_S
tatement->execute(Array)
#3 /home/madeupaccount/public_html/store/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_
Db_Adapter_Abstract->query('INSERT INTO cat...', Array)
#4 /home/madeupaccount/public_html/store/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO cat...', Array)
#5 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(159): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO cat...')
#6 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(134): Mage_Index_Model_Mysql4_Abstract->insertFromSelect('SELECT * FROM c...', 'catalog_categor...', Array, false)
#7 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(88): Mage_Index_Model_Mysql4_Abstract->insertFromTable('catalog_categor...', 'catalog_categor...', false)
#8 /home/madeupaccount/public_html/store/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php(598): Mage_Index_Model_Mysql4_Abstract->syncData()
#9 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Category_Indexer_Product->reindexAll()
#10 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#11 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#12 /home/madeupaccount/public_html/store/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#13 /home/madeupaccount/public_html/store/shell/indexer.php(198): Mage_Shell_Compiler->run()
#14 {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 (`madeupaccount_cart/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_PROD_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE)' in /home/madeupaccount/public_html/store/lib/Zend/Db/Statement/Pdo.php:234
Stack trace
:
#0 /home/madeupaccount/public_html/store/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/madeupaccount/public_html/store/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /home/madeupaccount/public_html/store/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO cat...', Array)
#3 /home/madeupaccount/public_html/store/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO cat...', Array)
#4 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(159): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO cat...')
#5 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(134): Mage_Index_Model_Mysql4_Abstract->insertFromSelect('SELECT * FROM c...', 'catalog_categor...', Array, false)
#6 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(88): Mage_Index_Model_Mysql4_Abstract->insertFromTable('catalog_categor...', 'catalog_categor...', false)
#7 /home/madeupaccount/public_html/store/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php(598): Mage_Index_Model_Mysql4_Abstract->syncData()
#8 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Category_Indexer_Product->reindexAll()
#9 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /home/madeupaccount/public_html/store/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#11 /home/madeupaccount/public_html/store/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#12 /home/madeupaccount/public_html/store/shell/indexer.php(198): Mage_Shell_Compiler->run()
#13 {main}
 
Magento Community Magento Community
Magento Community
Magento Community
 
Keebz
Jr. Member
 
Total Posts:  14
Joined:  2010-08-05
 

I\’ve tried a couple of things but no solution yet.  First to make sure that I\’m not missing any important files, I uploaded all the files from lib and app/code/core from the 1.4.2.tgz download.  I ran the catalog_category_product index again but stopped it after 2 hours of running. 

I then used the SQL code found on this post to remove all products and categories (I\’ve used this once before as well during initial testing in 1.4.1.1):  link

It seemed to remove all the categories and products, so I imported about 4000 products and ran the catalog_category_product index again.  This time it completed in about 4 minutes and so I went on to import another 70k skus.  It looks like I get the same error occurs after about ~19hours running:

~>  time php -/home/muacc/php-cli.ini /home/muacc/public_html/store/shell/indexer.php --reindex catalog_category_product 
Category Products 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 (`muacc_cart/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_PROD_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE)\' in /home/muacc/public_html/store/lib/Zend/Db/Statement/Pdo.php:228
Stack trace
:
#0 /home/muacc/public_html/store/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/muacc/public_html/store/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/muacc/public_html/store/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#3 /home/muacc/public_html/store/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(\'INSERT INTO cat...\', Array)
#4 /home/muacc/public_html/store/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query(\'INSERT INTO cat...\', Array)
#5 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(159): Varien_Db_Adapter_Pdo_Mysql->query(\'INSERT INTO cat...\')
#6 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(134): Mage_Index_Model_Mysql4_Abstract->insertFromSelect(\'SELECT * FROM c...\', \'catalog_categor...\', Array, false)
#7 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(88): Mage_Index_Model_Mysql4_Abstract->insertFromTable(\'catalog_categor...\', \'catalog_categor...\', false)
#8 /home/muacc/public_html/store/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php(598): Mage_Index_Model_Mysql4_Abstract->syncData()
#9 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Category_Indexer_Product->reindexAll()
#10 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#11 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Process.php(167)
Mage_Index_Model_Process->reindexAll()
#12 /home/muacc/public_html/store/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#13 /home/muacc/public_html/store/shell/indexer.php(198): Mage_Shell_Compiler->run()
#14 {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 (`muacc_cart/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_PROD_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE)\' in /home/muacc/public_html/store/lib/Zend/Db/Statement/Pdo.php:234
Stack trace
:
#0 /home/muacc/public_html/store/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/muacc/public_html/store/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /home/muacc/public_html/store/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(\'INSERT INTO cat...\', Array)
#3 /home/muacc/public_html/store/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query(\'INSERT INTO cat...\', Array)
#4 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(159): Varien_Db_Adapter_Pdo_Mysql->query(\'INSERT INTO cat...\')
#5 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(134): Mage_Index_Model_Mysql4_Abstract->insertFromSelect(\'SELECT * FROM c...\', \'catalog_categor...\', Array, false)
#6 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(88): Mage_Index_Model_Mysql4_Abstract->insertFromTable(\'catalog_categor...\', \'catalog_categor...\', false)
#7 /home/muacc/public_html/store/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php(598): Mage_Index_Model_Mysql4_Abstract->syncData()
#8 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Category_Indexer_Product->reindexAll()
#9 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /home/muacc/public_html/store/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#11 /home/muacc/public_html/store/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#12 /home/muacc/public_html/store/shell/indexer.php(198): Mage_Shell_Compiler->run()
#13 {main}

real    1148m27.604s
user    0m0.226s
sys     0m0.060s

I guess the \’mass delete\’ code is missing a table?  Especially since I\’ve used the same code prior to 1.4.2… Is there more complete code for truncating product or category tables?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Keebz
Jr. Member
 
Total Posts:  14
Joined:  2010-08-05
 

Success! ...kinda.

Category products was able to reindex after doing some more table cleaning to take care of the foreign key problem listed above.  The slowness has not gone away though.  Indexing with 30,000 products took about 2 hours, but with ~80,000 (simple)products it still took about 20 hours to finish. 

What kind of indexer run times are other people seeing with a large number of products on 1.4.2?

 
Magento Community Magento Community
Magento Community
Magento Community
 
gunnar
Member
 
Avatar
Total Posts:  37
Joined:  2008-05-27
 

hey,

i have the same problem. the reindexin is very very slow after my update. at the moment it is running for 4 days now. in the catalog_category_product table are 3.7 million entries and now the temporary index table has reached 1.7 millions. there is still a way to go.

do you have reached any improvement yet?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Keebz
Jr. Member
 
Total Posts:  14
Joined:  2010-08-05
 
gunnar - 03 March 2011 11:17 PM

hey,

i have the same problem. the reindexin is very very slow after my update. at the moment it is running for 4 days now. in the catalog_category_product table are 3.7 million entries and now the temporary index table has reached 1.7 millions. there is still a way to go.

do you have reached any improvement yet?

Hello,

Yes, adding the index as described here has solved the problem for me:
http://www.magentocommerce.com/bug-tracking/issue?issue=10808

 
Magento Community Magento Community
Magento Community
Magento Community
 
gunnar
Member
 
Avatar
Total Posts:  37
Joined:  2008-05-27
 

thanks a lot for this tip.

the bugfix works great.

 
Magento Community Magento Community
Magento Community
Magento Community
 
bscaturro
Jr. Member
 
Avatar
Total Posts:  1
Joined:  2011-05-30
 

A little late to the party. Keebz, did that posted bugfix get rid of:

Category Products index process unknown error:
exception \'PDOException\' with message \'SQLSTATE[23000]: Integrity constraint vio
lation: 1452 Cannot add or update a child row: a foreign key constraint fails (`
madeupaccount_cart/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PROD_
IDX_PROD_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity`
(`entity_id`) ON DELETE CASCADE)\' 
in /home/madeupaccount/public_html/store/lib/Zend/Db/St
atement
/Pdo.php:228

If not were the tables you cleaned up what did the trick? What tables did you clean out?

Much appreciated!

Thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top