Magento - 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

Solution :

Please carry out the following :

Enable magento DB logging via: lib/Varien/Db/Adapter/Pdo/Mysql.php . Down around line 86, you’ll see the following class variables:

protected $_debug = false; 
protected $_logAllQueries = false; 
protected $_logCallStack = false; 
protected $_debugFile = ‘var/debug/sql.txt’;

Change all to true and then run the re-index. Then check the log file specified above.

The log file indicates which table in the database is causing the issue and then compare the table structure of this table with the same one here: http://www.magereverse.com/index/magento-database-diagram/version/1-7-0-2

Remove the column that shouldn't be there. The issue will be fixed. Regards.

Answer 2

i got the same issue, the logs said something like this

exception ‘PDOException’ with message ‘SQLSTATE[21S01]: Insert value list does not match column list ...

when i traced down the exceptions and the queries that were causing the error, i found that upgrade was not done properly, some columns were missing in database related to product index group price. i had to add them again (by running the setup file again related to product attribute group price) and it worked like a charm.