Unable to reindex Product Prices - Foreign Key Constraint Fails

Question

I've just recently upgraded my magento store from 1.4.2 to 1.7.0.2.. And The upgrade went smoothly but when I try to reindex data after the upgrade.. It fails on the Product Prices..

I've tried using php shell/indexer.php --reindexall from SSH and it fails at the Product Prices with these errors (I use pastebin as the error are quite long):

http://pastebin.com/iV2tuzDX

From what I can see, the table catalog_product_index_tier_price is empty.. and I'm not sure if that is causing any issue?

I've tried many different ways of solving it but to no avail.. Things like:

• Can't re-index new products after upgrading to version 1.6 <- I've tried the answer by Vijay Kumar
• http://www.magentocommerce.com/boards/v/viewthread/312438/#t429612 <- I've tried flushing /site/var/locks
• Magento: a foreign key constraint fails after update 1.4 -> 1.7 <- I've tried the answer by Tobias Zander
-

None of them worked.. I am not an expert in MySQL but I do know a little bit.. So I hope someone is able to find out what is causing the error and help me out and I believe it might also help others too who are experiencing the same issue as me :)

Thanks!~

Answer 1

1) Remove the LOCKS directory from the var/ folder 2) Remove the cache directory in the var/ folder

If you still are having a problem you can truncate your index tables for price

Some examples are : (You can view your index tables and add or remove)

truncate catalog_product_index_price_bundle_idx;
truncate catalog_product_index_price_bundle_opt_idx;
truncate catalog_product_index_price_bundle_sel_idx;
truncate catalog_product_index_price_cfg_opt_agr_idx;
truncate catalog_product_index_price_cfg_opt_idx;
truncate catalog_product_index_price_downlod_idx;
truncate catalog_product_index_price_final_idx;
truncate catalog_product_index_price_idx;
truncate catalog_product_index_price_opt_agr_idx;
truncate catalog_product_index_price_opt_idx;

I would impress that you DO NOT DO THIS ON LIVE! Make sure you fully test all MySQL queries before you do anything on production. Once you truncate it is GONE!

Answer 2

Have you tried the Magento Database Repair tool? I've used it to fix a similar problem with an index in the past.

http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/db-repair-tool