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

reindexing
 
Loic_LC
Sr. Member
 
Avatar
Total Posts:  194
Joined:  2007-12-19
Paris, France
 

Hello,

I have updated successfully to V1.5.0.1 and I couldn’t reindex Category Products (Indexed category/products association) via my admin panel
So I tried via SSH using php ./shell/indexer.php reindexall

And I got the following, but I don’t have any idea what I should do with this…
Any help is greatly appreciated
Thanks

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 (`pp_shop/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_CATEGORY_ENTITY` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CAS)' in /home/admin/petiteparis/shop/lib/Zend/Db/Statement/Pdo.php:228
Stack trace
:
#0 /home/admin/petiteparis/shop/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/admin/petiteparis/shop/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/admin/petiteparis/shop/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/admin/petiteparis/shop/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO cat...', Array)
#4 /home/admin/petiteparis/shop/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO cat...', Array)
#5 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(170): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO cat...')
#6 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(141): Mage_Index_Model_Mysql4_Abstract->insertFromSelect('SELECT * FROM c...', 'catalog_categor...', Array, false)
#7 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(95): Mage_Index_Model_Mysql4_Abstract->insertFromTable('catalog_categor...', 'catalog_categor...', false)
#8 /home/admin/petiteparis/shop/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php(766): Mage_Index_Model_Mysql4_Abstract->syncData()
#9 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Category_Indexer_Product->reindexAll()
#10 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#11 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#12 /home/admin/petiteparis/shop/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#13 /home/admin/petiteparis/shop/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 (`pp_shop/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_CATEGORY_ENTITY` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CAS)' in /home/admin/petiteparis/shop/lib/Zend/Db/Statement/Pdo.php:234
Stack trace
:
#0 /home/admin/petiteparis/shop/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/admin/petiteparis/shop/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/admin/petiteparis/shop/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO cat...', Array)
#3 /home/admin/petiteparis/shop/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO cat...', Array)
#4 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(170): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO cat...')
#5 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(141): Mage_Index_Model_Mysql4_Abstract->insertFromSelect('SELECT * FROM c...', 'catalog_categor...', Array, false)
#6 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(95): Mage_Index_Model_Mysql4_Abstract->insertFromTable('catalog_categor...', 'catalog_categor...', false)
#7 /home/admin/petiteparis/shop/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php(766): Mage_Index_Model_Mysql4_Abstract->syncData()
#8 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Category_Indexer_Product->reindexAll()
#9 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /home/admin/petiteparis/shop/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#11 /home/admin/petiteparis/shop/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#12 /home/admin/petiteparis/shop/shell/indexer.php(198): Mage_Shell_Compiler->run()
#13 {main}
 
Magento Community Magento Community
Magento Community
Magento Community
 
SimpleHelixcom
Enthusiast
 
Avatar
Total Posts:  906
Joined:  2007-08-31
Huntsville, AL
 

Hello,

Sounds like you have some buggy constraints! Be sure and take a full backup of your files and database before trying anytrhing.

The way we’ve semi-fixed this in the past was to use PHPMyAdmin to dump the database, edit the dump to remove all constraints (these will be at the bottom) or just the one for this table, and import this dump into a new database.

Finally, pair Magento with the new database by editing app/etc/local.xml and run with it. We have yet to see any adverse effects of this method.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Loic_LC
Sr. Member
 
Avatar
Total Posts:  194
Joined:  2007-12-19
Paris, France
 

Thanks for your answer.
I’m not sure I can do that.
When I go to phpmyadmin, I see the catalog_category_product_index tble is empty while it has more than 3000 records on my staging site.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Amasty
Mentor
 
Avatar
Total Posts:  3988
Joined:  2009-11-10
 

This should fix the issue on 1.5.0.1:

ALTER TABLE `catalog_category_product_indexDROP FOREIGN KEY 
FK_CATALOG_CATEGORY_PROD_IDX_PROD_ENTITY
 
Magento Community Magento Community
Magento Community
Magento Community
 
youderian
Jr. Member
 
Total Posts:  20
Joined:  2009-07-15
 

SOLUTION!

After upgrading Magento from 1.4.0.1 to 1.6.1, I COULD NOT get my Product Catalog index to build successfully!  I tried everything - the DB repair tool, resetting permissions and the locks folders - all to no avail.  Finally, I saw simpleHelix’s recommendation on removing Constraints which WORKED!  Here’s what I did to get Product Catalog to build successfully:

1) Dump your existing DB via phpmyadmin and make sure to Disable Foreign Key Checks
2) Search through the dump file for the lines:

ALTER TABLE `catalog_category_product_index`
ADD CONSTRAINT .................

3) Delete this entire entry to remove all constraints on the catalog_category_product_index table
4) Create a new DB and import the dump you just modified into it
5) Update your app/etc/local.xml file to point to the newly modified database
5) Attempt to rebuid your indexes, hopefully with success.

Hopefully this will save someone the hours and hours I spent solving this.

 
Magento Community Magento Community
Magento Community
Magento Community
 
youderian
Jr. Member
 
Total Posts:  20
Joined:  2009-07-15
 
youderian - 28 November 2011 10:27 AM

SOLUTION!

After upgrading Magento from 1.4.0.1 to 1.6.1, I COULD NOT get my Product Catalog index to build successfully!  I tried everything - the DB repair tool, resetting permissions and the locks folders - all to no avail.  Finally, I saw simpleHelix’s recommendation on removing Constraints which WORKED!  Here’s what I did to get Product Catalog to build successfully:


1) Dump your existing DB via phpmyadmin and make sure to Disable Foreign Key Checks
2) Search through the dump file for the lines:

ALTER TABLE `catalog_category_product_index`
ADD CONSTRAINT .................

3) Delete this entire entry to remove all constraints on the catalog_category_product_index table
4) Create a new DB and import the dump you just modified into it
5) Update your app/etc/local.xml file to point to the newly modified database
5) Attempt to rebuid your indexes, hopefully with success.

Hopefully this will save someone the hours and hours I spent solving this.

Also, as a follow-up, if you are having a problem getting the Product Attribute index to index correctly, follow the same steps listed above but for the catalog_product_index_eav table constraints.  With these removed, I was also able to solve a indexing problem with Product Attributes.

 
Magento Community Magento Community
Magento Community
Magento Community
 
rhaps
Jr. Member
 
Total Posts:  29
Joined:  2007-11-24
 

thanks !! works great

 
Magento Community Magento Community
Magento Community
Magento Community
 
Duncan R.
Jr. Member
 
Total Posts:  3
Joined:  2008-12-30
 

The problem is not the constraints, but bad data that violates the specific constraint. I had this problem, and doing the index from the shell was key. I ran the following from an SSH shell:

php ./shell/indexer.php reindexall

...and received this error:

Category Products index process unknown error:
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 (`accou_mgto1/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PRODUCT_INDEX_CATEGORY_ENTITY` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON D)’ in /home/accou/public_html/lib/Zend/Db/Statement/Pdo.php:234

I then went into phpMyAdmin, and found the records that referenced a non-existent category:

SELECT * FROM catalog_category_product
WHERE category_id NOT IN
( SELECT entity_id FROM catalog_category_entity )

Two records existed! I then deleted these two records, and voila! The indexing completed successfully. I suppose that the same problem could have also been caused by the table referencing a non-existent catalog_product_entity table as well, with a slightly different error message. I had done an import of some products, moved around some categories, and deleted a few categories. I am guessing that when the category delete happened, the corresponding delete in the catalog_category_product did not occur for some reason.

 
Magento Community Magento Community
Magento Community
Magento Community
 
insanityinside
Jr. Member
 
Total Posts:  3
Joined:  2011-09-29
 
Duncan R. - 22 April 2012 12:35 PM

SELECT * FROM catalog_category_product
WHERE category_id NOT IN
( SELECT entity_id FROM catalog_category_entity )

I just wanted to say thank you for the above - I was trying to work out why my scheduled indexers were collapsing, and that took me straight to the root cause of the issue. Much appreciated!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Stuart Bowler
Jr. Member
 
Total Posts:  2
Joined:  2010-05-27
 
Duncan R. - 22 April 2012 12:35 PM


SELECT * FROM catalog_category_product
WHERE category_id NOT IN
( SELECT entity_id FROM catalog_category_entity )


Just adding my thanks for this - we’ve been wrestling with indexes not running because of this violation for a while now. Your query allowed us to fix the problem at last

 
Magento Community Magento Community
Magento Community
Magento Community
 
Cameron_iWeb
Member
 
Avatar
Total Posts:  62
Joined:  2012-03-22
 

This is perfect.

Had this problem all morning.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jamie Jackson
Jr. Member
 
Avatar
Total Posts:  23
Joined:  2010-03-05
Sheffield, UK
 

SELECT FROM catalog_category_product
WHERE category_id NOT IN
SELECT entity_id FROM catalog_category_entity )

Thankyou for this, it worked perfect!

 
Magento Community Magento Community
Magento Community
Magento Community
 
kalenjordan
Sr. Member
 
Avatar
Total Posts:  218
Joined:  2011-10-31
Pasadena, CA
 

@youderian, another approach with the initial problem you posted, especially because the constraint appeared to be from a 3rd party module (pp_shop/catalog_category_product_index) would be to simply disable that module, then re-index, then try enabling it and re-indexing.

But more generally, as others have mentioned here, you basically want to make sure that any data integrity issues are dealt with, as opposed to simply disabling constraints.

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