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

[SOLVED] Category Products Index fails due to Integrity Constraint Violation
 
Magnify Studio
Jr. Member
 
Avatar
Total Posts:  6
Joined:  2009-11-01
Orlando, FL
 

Hello Magento Community,

We’re attempting an upgrade from 1.3.2.4 to 1.4.2. We have staged the upgrade and have seen success. The same resolution is not working in the production environment.

The problem is category products index is failing. The `catalog_category_product_index` is empty and no products are showing up on the front-end.

Below is the exception we are getting:

2011-08-15T01:52:52+00:00 DEBUG (7): Exception messageSQLSTATE[23000]Integrity constraint violation1452 Cannot add or update a child rowa foreign key constraint fails (`magento/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_PROD_ENTITYFOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE C)
Trace#0 /home/magento/public_html/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/magento/public_html/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /home/magento/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO cat...', Array)
#3 /home/magento/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO cat...', Array)
#4 /home/magento/public_html/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(159): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO cat...')
#5 /home/magento/public_html/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/magento/public_html/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(88): Mage_Index_Model_Mysql4_Abstract->insertFromTable('catalog_categor...', 'catalog_categor...', false)
#7 /home/magento/public_html/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php(598): Mage_Index_Model_Mysql4_Abstract->syncData()
#8 /home/magento/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Category_Indexer_Product->reindexAll()
#9 /home/magento/public_html/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /home/magento/public_html/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#11 /home/magento/public_html/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#12 /home/magento/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#13 /home/magento/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(253): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#14 /home/magento/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))
#15 /home/magento/public_html/app/code/core/Mage/Core/Model/App.php(304): Mage_Core_Controller_Varien_Front->dispatch()
#16 /home/magento/public_html/app/Mage.php(596): Mage_Core_Model_App->run(Array)
#17 /home/magento/public_html/index.php(96): Mage::run('', 'store')
#18 {main}

We tried what feels like everything. Below is a short list:

- Reimport database with “Disable foreign key checks” checked
- Reset cache including locks
- Used Magento’s database repair tool
- Used clean up tool

In the staging environment, reimporting the database with foreign key checks disabled did the trick. It’s simply not working in the production environment.

Any ideas?

Best Regards,

Magnify Agency

 
Magento Community Magento Community
Magento Community
Magento Community
 
Magnify Studio
Jr. Member
 
Avatar
Total Posts:  6
Joined:  2009-11-01
Orlando, FL
 

Anybody out there? Thoughts? Help?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Magnify Studio
Jr. Member
 
Avatar
Total Posts:  6
Joined:  2009-11-01
Orlando, FL
 

Hello Magento Community,

We found a resolution!

We discovered that an alternative way of getting products to show up (without rebuilding this specific index) was by performing a mass product attribute update reassigning products to one or all desired sites. This is accomplished by doing the following within the backend:

1. Login to Magento backend
2. Go to: Catalog -> Manage Products
3. Click “Select All” to the left
4. Choose “Update Attributes” under “Actions” to the right. Click “Submit”.
5. Click “Websites” to the left
6. Select one or more websites
7. Click “Save” in the upper right hand corner

This filled the `catalog_category_product_index` table. All of the products showed back up on the website. Problem solved? Temporarily. The issue is; however, the “Catalog Products” index still fails (with same exception as originally described).

Getting to this point ultimately lead to the resolution. Below is the process we used to solve the reindexing issue:

1. Log into phpMyAdmin
2. Go to `catalog_category_product_index`
3. Click “Export”.
4. Export only structure with “Disable foreign key checks” enabled.
5. Drop the table `catalog_category_product_index`
6. With the SQL that was exported, remove the constraints at the bottom of the export. Be sure to keep the “SET FOREIGN_KEY_CHECKS” statements.
7. Click “SQL”, paste in modified SQL and click “Go”
8. Login to Magento backend
9. Go to: System -> Index Management
10. Click “Reindex Data” next to “Category Products” index

Taking another look at the `catalog_category_product_index` table shows Magento reapplied the constraints and the table is filled. Now we’re seeing the index work without exceptions being thrown.

Hopefully this is helpful for others.

Best Regards,

Magnify Agency

 
Magento Community Magento Community
Magento Community
Magento Community
 
kamaljoshi
Member
 
Avatar
Total Posts:  46
Joined:  2011-01-11
Ahmedabad
 

Thx man, you really did well. grin Really really helpful tips for reindexing issue of category product.

Magnify Agency - 20 August 2011 02:35 PM

Hello Magento Community,

We found a resolution!

We discovered that an alternative way of getting products to show up (without rebuilding this specific index) was by performing a mass product attribute update reassigning products to one or all desired sites. This is accomplished by doing the following within the backend:

1. Login to Magento backend
2. Go to: Catalog -> Manage Products
3. Click “Select All” to the left
4. Choose “Update Attributes” under “Actions” to the right. Click “Submit”.
5. Click “Websites” to the left
6. Select one or more websites
7. Click “Save” in the upper right hand corner

This filled the `catalog_category_product_index` table. All of the products showed back up on the website. Problem solved? Temporarily. The issue is; however, the “Catalog Products” index still fails (with same exception as originally described).

Getting to this point ultimately lead to the resolution. Below is the process we used to solve the reindexing issue:

1. Log into phpMyAdmin
2. Go to `catalog_category_product_index`
3. Click “Export”.
4. Export only structure with “Disable foreign key checks” enabled.
5. Drop the table `catalog_category_product_index`
6. With the SQL that was exported, remove the constraints at the bottom of the export. Be sure to keep the “SET FOREIGN_KEY_CHECKS” statements.
7. Click “SQL”, paste in modified SQL and click “Go”
8. Login to Magento backend
9. Go to: System -> Index Management
10. Click “Reindex Data” next to “Category Products” index

Taking another look at the `catalog_category_product_index` table shows Magento reapplied the constraints and the table is filled. Now we’re seeing the index work without exceptions being thrown.

Hopefully this is helpful for others.

Best Regards,

Magnify Agency

 
Magento Community Magento Community
Magento Community
Magento Community
 
viastudio
Jr. Member
 
Total Posts:  2
Joined:  2011-03-16
 

Note that this works because you have dropped the Foreign Key Constraints. After following your steps, I did not see that Magento rebuilt them after a successful reindex as you claimed.

I am inclined to leave them off since the Category Products index runs successfully. However, it should be noted that the reason this solution works is because it removed the Foreign Keys. Be mindful that could come at a cost.

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