Magento

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

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.

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.

This should fix the issue on 1.5.0.1:

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.

youderian - 28 November 2011 11: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.

thanks !! works great

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.

Duncan R. - 22 April 2012 01: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!

Duncan R. - 22 April 2012 01: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

This is perfect.

Had this problem all morning.

Thankyou for this, it worked perfect!

@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.