magento reindexing just the prices tried every solution

Question

I tried everything I could think of to fix this issue and have been going through everything i could find on fixing reindexing issue... Ill take any advice possible, I tried everything through SSH/Shell, I tried truncating everything possible, deleted my var/locks file cleared the cache, and also running the database repair tool (some issues were fixed) to no avail... still having the same issue of not being able to reindex product prices.

[~/public_html/magento]# php shell/indexer.php --
reindexall
Product Attributes index was rebuilt successfully
Product Prices index process unknown error:
exception 'PDOException' with message 'SQLSTATE[21S01]: Insert value list doe
s not match column list: 1136 Column count doesn't match value count at row 1
' in /home/mystuff/public_html/magento/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/mystuff/public_html/magento/lib/Zend/Db/Statement/Pdo.php(228): PDO
Statement->execute(Array)
#1 /home/mystuff/public_html/magento/lib/Varien/Db/Statement/Pdo/Mysql.php(1
10): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/mystuff/public_html/magento/lib/Zend/Db/Statement.php(300): Varien_
Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/mystuff/public_html/magento/lib/Zend/Db/Adapter/Abstract.php(479):
Zend_Db_Statement->execute(Array)
#4 /home/mystuff/public_html/magento/lib/Zend/Db/Adapter/Pdo/Abstract.php(23
8): Zend_Db_Adapter_Abstract->query('INSERT INTO `sm...', Array)
#5 /home/mystuff/public_html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(419
): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `sm...', Array)
#6 /home/mystuff/public_html/magento/app/code/local/Innoexts/StorePricing/Mo
del/Mysql4/Downloadable/Indexer/Price.php(125): Varien_Db_Adapter_Pdo_Mysql->
query('INSERT INTO `sm...')
#7 /home/mystuff/public_html/magento/app/code/core/Mage/Downloadable/Model/R
esource/Indexer/Price.php(47): Innoexts_StorePricing_Model_Mysql4_Downloadabl
e_Indexer_Price->_prepareFinalPriceData()
#8 /home/mystuff/public_html/magento/app/code/core/Mage/Catalog/Model/Resour
ce/Product/Indexer/Price.php(385): Mage_Downloadable_Model_Resource_Indexer_P
rice->reindexAll()
#9 /home/mystuff/public_html/magento/app/code/core/Mage/Index/Model/Indexer/
Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindex
All()
#10 /home/mystuff/public_html/magento/app/code/core/Mage/Index/Model/Process
.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll()
#11 /home/mystuff/public_html/magento/app/code/core/Mage/Index/Model/Process
.php(255): Mage_Index_Model_Process->reindexAll()
#12 /home/mystuff/public_html/magento/shell/indexer.php(158): Mage_Index_Mod
el_Process->reindexEverything()
#13 /home/mystuff/public_html/magento/shell/indexer.php(198): Mage_Shell_Com
piler->run()
#14 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[21S01]: I
nsert value list does not match column list: 1136 Column count doesn't match
value count at row 1' in /home/mystuff/public_html/magento/lib/Zend/Db/State
ment/Pdo.php:234
Stack trace:
#0 /home/mystuff/public_html/magento/lib/Varien/Db/Statement/Pdo/Mysql.php(1
10): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/mystuff/public_html/magento/lib/Zend/Db/Statement.php(300): Varien_
Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/mystuff/public_html/magento/lib/Zend/Db/Adapter/Abstract.php(479):
Zend_Db_Statement->execute(Array)
#3 /home/mystuff/public_html/magento/lib/Zend/Db/Adapter/Pdo/Abstract.php(23
8): Zend_Db_Adapter_Abstract->query('INSERT INTO `sm...', Array)
#4 /home/mystuff/public_html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(419
): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `sm...', Array)
#5 /home/mystuff/public_html/magento/app/code/local/Innoexts/StorePricing/Mo
del/Mysql4/Downloadable/Indexer/Price.php(125): Varien_Db_Adapter_Pdo_Mysql->
query('INSERT INTO `sm...')
#6 /home/mystuff/public_html/magento/app/code/core/Mage/Downloadable/Model/R
esource/Indexer/Price.php(47): Innoexts_StorePricing_Model_Mysql4_Downloadabl
e_Indexer_Price->_prepareFinalPriceData()
#7 /home/mystuff/public_html/magento/app/code/core/Mage/Catalog/Model/Resour
ce/Product/Indexer/Price.php(385): Mage_Downloadable_Model_Resource_Indexer_P
rice->reindexAll()
#8 /home/mystuff/public_html/magento/app/code/core/Mage/Index/Model/Indexer/
Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindex
All()
#9 /home/mystuff/public_html/magento/app/code/core/Mage/Index/Model/Process.
php(209): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /home/mystuff/public_html/magento/app/code/core/Mage/Index/Model/Process
.php(255): Mage_Index_Model_Process->reindexAll()
#11 /home/mystuff/public_html/magento/shell/indexer.php(158): Mage_Index_Mod
el_Process->reindexEverything()
#12 /home/mystuff/public_html/magento/shell/indexer.php(198): Mage_Shell_Com
piler->run()
#13 {main}
Catalog URL Rewrites index was rebuilt successfully
Product Flat Data index was rebuilt successfully
Category Flat Data index was rebuilt successfully
Category Products index was rebuilt successfully
Catalog Search Index index was rebuilt successfully
Stock Status index was rebuilt successfully

score 0 · Answer 1 · edited May 23 '17 at 12:00

0

From your above error log it seems some columns are missing, Insert value list doe s not match column list: 1136 Column count doesn't match value count at row 1.

Check if this answer helps you.

https://stackoverflow.com/a/18548136/857855

Also, check the price index tables and compare the columns with a working magento copy,

list of price index tables, http://makandracards.com/magento/8285-product-price-index

check ans 6 here, http://www.magentocommerce.com/boards/viewthread/280202/#6

Hope it helps!

edited May 23 '17 at 12:00

Community

1
1

answered Jun 21 '14 at 07:19

huzefam

1,201
1
12
21

no still having issues with getting my product prices to reindex / any other ideas?> – user3760917 Jun 27 '14 at 14:14
here is the sql.txt from my debug file when i tried to reindex the prices www.blazerproducts.com/sql.txt – user3760917 Jun 27 '14 at 19:28
In my case this problem was caused by a community extension (OrganicInternet/SimpleConfigurableProducts) which rewrites the product price indexer but is not compatible with current magento's price index (it does not create values for the `group_price` and `base_group_price` columns of `catalog_product_index_price_final_idx table`. So i'd suggest you try to disable community extensions one after the other and try to restart the indexing process. That way you'll find out if it is a magento connect module causing your problems. – Rinda Mar 19 '15 at 13:22

score 0 · Answer 2 · answered May 28 '18 at 14:35

Same issue. I think because of a bugged module, that added some columns in some tables. The way I got it worked, :

Access to the shell (ssh)

I used the shell (ssh) with following command

php /home/mystuff/public_html/magento/shell/indexer.php -reindexall
Read the output

Then, with the help of the output in the shell, I took every table that showed problems. (What I mean here is that I went step by step: I ran a 1st time the command, the output showed a problematic table, I repaired it with the underneath procedure. And then, I ran a 2d time the command, another table was shown etc.)

Delete problematic table

So, for every table, I went to phpmyadmin and deleted it (the whole table: data + structure).

Import a working table from another install

Then I went to a working database of magento (another website) and exported the table. And then, I imported it in the non-working database.

Conclusion

After a while (5 or 6 table deleted this way), the command shell output didn't show anymore errors. It ran successfully the indexer process. And, magical : My products reappared inside their categories.

Additional notes

I'm not sure to be right, but, as I understand it, the structures of the price index tables were damaged. This procedure permitted to reset them with proper ones. No matter if they were non-proper datas in them (which I had), because this is the purpose of the indexer process to rebuild the datas from your product catalog.

Before doing this: Don't forget to make a local copy of both websites' databases (the non-working and the working one). This way you won't be afraid of doing an error.

GOOD LUCK !

magento reindexing just the prices tried every solution

2 Answers2

php /home/mystuff/public_html/magento/shell/indexer.php -reindexall