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] Unable to re-index Product Prices after upgrade | Tried several solutions
 
mgrm43
Jr. Member
 
Total Posts:  4
Joined:  2012-11-12
 

Hi All,

We are getting the below error when re-indexing Product Prices after the upgrade from 1.5 to 1.7.0.2.

We have tried several methods of upgrading but currently have the following setup:
1. Fresh install of Magento 1.7.0.2
2. Imported old database
3. Ran upgrade script by going to home page
4. Tried to reindex

Currently my guess is that a module/extension we had installed previously has made some changes to the database which is now causing this issue when re-indexing - At this point I’m really unsure about how to troubleshoot further.

Weeks of troubleshooting have got us no where with this error so any help would be much appreciated!

Product Prices index process unknown error:
exception ‘PDOException’ with message ‘SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn’t match value count at row 1’ in /home/somedays/public_html/testinstall/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/somedays/public_html/testinstall/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/somedays/public_html/testinstall/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/somedays/public_html/testinstall/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/somedays/public_html/testinstall/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/somedays/public_html/testinstall/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’INSERT INTO `ca...’, Array)
#5 /home/somedays/public_html/testinstall/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query(’INSERT INTO `ca...’, Array)
#6 /home/somedays/public_html/testinstall/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(455): Varien_Db_Adapter_Pdo_Mysql->query(’INSERT INTO `ca...’)
#7 /home/somedays/public_html/testinstall/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(379): Mage_Catalog_Model_Resource_Product_Indexer_Price->_prepareTierPriceIndex()
#8 /home/somedays/public_html/testinstall/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexAll()
#9 /home/somedays/public_html/testinstall/app/code/core/Mage/Index/Model/Process.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /home/somedays/public_html/testinstall/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#11 /home/somedays/public_html/testinstall/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#12 /home/somedays/public_html/testinstall/shell/indexer.php(198): Mage_Shell_Compiler->run()
#13 {main}

Next exception ‘Zend_Db_Statement_Exception’ with message ‘SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn’t match value count at row 1’ in /home/somedays/public_html/testinstall/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /home/somedays/public_html/testinstall/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/somedays/public_html/testinstall/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/somedays/public_html/testinstall/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/somedays/public_html/testinstall/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’INSERT INTO `ca...’, Array)
#4 /home/somedays/public_html/testinstall/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query(’INSERT INTO `ca...’, Array)
#5 /home/somedays/public_html/testinstall/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(455): Varien_Db_Adapter_Pdo_Mysql->query(’INSERT INTO `ca...’)
#6 /home/somedays/public_html/testinstall/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(379): Mage_Catalog_Model_Resource_Product_Indexer_Price->_prepareTierPriceIndex()
#7 /home/somedays/public_html/testinstall/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexAll()
#8 /home/somedays/public_html/testinstall/app/code/core/Mage/Index/Model/Process.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll()
#9 /home/somedays/public_html/testinstall/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#10 /home/somedays/public_html/testinstall/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#11 /home/somedays/public_html/testinstall/shell/indexer.php(198): Mage_Shell_Compiler->run()
#12 {main}

SOLUTION:

Enabled 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 ran the reindex. Then checked the log file specified above.

The log file indicated which table in my database was causing the issue and I compared the table structure of this table with the same one here: http://www.magereverse.com/index/magento-database-diagram/version/1-7-0-2

Removed the column that shouldnt be there fixed the issue.

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

I think you may have hit the nail on the head with a module having modified your database schema in a way that’s making the indexer fail.  There is a DB insert being attempted where the insert list doesn’t match the columns in the table, so probably a column was added to the table.

I’m working on a tool to diff the db schema to detect these kinds of modifications.  I may be able to help you if you’re interested.

 
Magento Community Magento Community
Magento Community
Magento Community
 
mgrm43
Jr. Member
 
Total Posts:  4
Joined:  2012-11-12
 
mageupdate - 24 November 2012 08:11 PM

I think you may have hit the nail on the head with a module having modified your database schema in a way that’s making the indexer fail.  There is a DB insert being attempted where the insert list doesn’t match the columns in the table, so probably a column was added to the table.

I’m working on a tool to diff the db schema to detect these kinds of modifications.  I may be able to help you if you’re interested.

Thanks for your response. Any idea how I can go about troubleshooting the issue?

Definitely interested in getting the issue resolved if you have any ideas.

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

Hey @mgrm43,

Well, for starters I would probably flip on Db query logging - it’s a little tricky because there will be a butt ton of queries logged, so you need to be able to find the offending query.  But odds are that execution is halting at the offending query, so it will probably be the last one in the log.

It would probably be hard to diff the core DB without a tool b/c there are so many tables.  But I’d probably peek at the main catalog_product_entity table to see if any columns pop out.

Thanks,
Kalen

 
Magento Community Magento Community
Magento Community
Magento Community
 
mgrm43
Jr. Member
 
Total Posts:  4
Joined:  2012-11-12
 

Thanks.

The clue you provided RE enabled logging helped me solve the issue. I have updated my initial post with the solution.

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

Awesome, good man!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Bates
Jr. Member
 
Total Posts:  17
Joined:  2011-11-20
 

Just done the same but what am I looking for in the log file?

 
Magento Community Magento Community
Magento Community
Magento Community
 
magebay
Sr. Member
 
Avatar
Total Posts:  122
Joined:  2012-10-09
Thai Nguyen
 
mageupdate - 25 November 2012 07:45 PM

Hey @mgrm43,

Well, for starters I would probably flip on Db query logging - it’s a little tricky because there will be a butt ton of queries logged, so you need to be able to find the offending query.  But odds are that execution is halting at the offending query, so it will probably be the last one in the log.

It would probably be hard to diff the core DB without a tool b/c there are so many tables.  But I’d probably peek at the main catalog_product_entity table to see if any columns pop out.

Thanks,
Kalen

Great help. Thanks.

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