Magento

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.

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.

mageupdate - 24 November 2012 09: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.

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

Thanks.

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

Awesome, good man!

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

mageupdate - 25 November 2012 08: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.