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

Page 1 of 2
Can’t re-index product prices
 
markf
Sr. Member
 
Total Posts:  145
Joined:  2007-09-20
 

Upgraded from 1.3.2.4 to 1.4.0.1 and everything went fine. 

However, in the admin I am unable to re-index product prices.  (All others ok)

Because of this, no products show on the frontend.  It shows all categories as empty.  If I open a product in admin and re-save, it will then appear on the frontend.

Any hints on this issue? 

I tried empty cache, permissions, etc.

 
Magento Community Magento Community
Magento Community
Magento Community
 
DCAlmeida
Sr. Member
 
Avatar
Total Posts:  164
Joined:  2008-12-28
PRT
 

Check this page: http://www.magentocommerce.com/boards/viewthread/76540/P45/

 
Magento Community Magento Community
Magento Community
Magento Community
 
markf
Sr. Member
 
Total Posts:  145
Joined:  2007-09-20
 

Thanks, I tried running this in phpmyadmin in the SQL box:

SELECT sku, COUNT(sku) AS NumOccurrences
FROM catalog_product_entity
GROUP BY sku
HAVING ( COUNT(sku) > 1 )

All I got from it was that there was 28 occurrences.  Nothing more specific so I’m not sure what to do now.

 
Magento Community Magento Community
Magento Community
Magento Community
 
DCAlmeida
Sr. Member
 
Avatar
Total Posts:  164
Joined:  2008-12-28
PRT
 

You have duplicate skus??? You cannot have!

 
Magento Community Magento Community
Magento Community
Magento Community
 
markf
Sr. Member
 
Total Posts:  145
Joined:  2007-09-20
 

No I do not. But now I just realized that I have some with no SKU, I looked and it is exactly 28.  I am going to try and delete those products and see if that fixes it.

 
Magento Community Magento Community
Magento Community
Magento Community
 
markf
Sr. Member
 
Total Posts:  145
Joined:  2007-09-20
 

Deleted the 28 prods with no sku.... did not help.  still cant index product prices.

 
Magento Community Magento Community
Magento Community
Magento Community
 
markf
Sr. Member
 
Total Posts:  145
Joined:  2007-09-20
 

Looked in the exception log and found this:

2010-02-23T19:25:59+00:00 DEBUG (7): Exception message: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`allsncom_staging/catalog_product_index_tier_price`, CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_TIER_PRICE_ENTITY` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELE)
Trace: #0 /home/allsncom/public_html/staging/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/allsncom/public_html/staging/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /home/allsncom/public_html/staging/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’INSERT INTO `ca...’, Array)
#3 /home/allsncom/public_html/staging/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query(’INSERT INTO `ca...’, Array)
#4 /home/allsncom/public_html/staging/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price.php(412): Varien_Db_Adapter_Pdo_Mysql->query(’INSERT INTO `ca...’)
#5 /home/allsncom/public_html/staging/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price.php(353): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price->_prepareTierPriceIndex()
#6 /home/allsncom/public_html/staging/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price->reindexAll()
#7 /home/allsncom/public_html/staging/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#8 /home/allsncom/public_html/staging/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#9 /home/allsncom/public_html/staging/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#10 /home/allsncom/public_html/staging/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#11 /home/allsncom/public_html/staging/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch(’reindexProcess’)
#12 /home/allsncom/public_html/staging/app/code/core/Mage/Core/Controller/Varien/Front.php(177): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#13 /home/allsncom/public_html/staging/app/code/core/Mage/Core/Model/App.php(304): Mage_Core_Controller_Varien_Front->dispatch()
#14 /home/allsncom/public_html/staging/app/Mage.php(596): Mage_Core_Model_App->run(Array)
#15 /home/allsncom/public_html/staging/index.php(78): Mage::run(’’, ‘store’)
#16 {main}

Not sure what action to take based on this info.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Paine
Member
 
Total Posts:  73
Joined:  2010-01-25
 

Did you ever get this problem solved? We have a nearly identical problem.

 
Magento Community Magento Community
Magento Community
Magento Community
 
markf
Sr. Member
 
Total Posts:  145
Joined:  2007-09-20
 

Yes, but I don’t remember the exact solution. It had to do with some ba entries in one of the DB tables, once we deleted the bad records, it started working again. I think there was some date left behind from products that were deleted.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Paine
Member
 
Total Posts:  73
Joined:  2010-01-25
 

Were they lines in the product_price_index table with null values for price?
Can we just wipe the table completely and have the indexer rebuild it?

 
Magento Community Magento Community
Magento Community
Magento Community
 
markf
Sr. Member
 
Total Posts:  145
Joined:  2007-09-20
 

I really dont recall, but I would recommend doing some trial and error on a staging server to find that bad data causing this error.  Thats how I ended up fixing mine.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Paine
Member
 
Total Posts:  73
Joined:  2010-01-25
 

Grr, I don’t have direct access to the sever, I was hoping for a “go to this table, clear this” type of fix. The only person in our company with direct access to the server is very protective of “his” baby, but since Magento was my project I’m the one expected to know how to fix it.... without access to the files or DB.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Paine
Member
 
Total Posts:  73
Joined:  2010-01-25
 

This is what the magento DB repair tool tells us, does anyone know how to fix this?

Error #1005: Can't create table 'magentonew.#sql-834_30dc2' (errno: 121) on SQL: ALTER TABLE `catalog_product_index_price` ADD CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP` FOREIGN KEY (`customer_group_id`) REFERENCES `customer_group` (`customer_group_id`) ON DELETE CASCADE ON UPDATE CASCADE

Error #1005: Can't create table 'magentonew.#sql-834_30dc2' (errno: 121) on SQL: ALTER TABLE `catalogindex_price` ADD CONSTRAINT `FK_CATALOGINDEX_PRICE_ATTRIBUTE` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
 
Magento Community Magento Community
Magento Community
Magento Community
 
Paine
Member
 
Total Posts:  73
Joined:  2010-01-25
 

We need help with this. Its going on 2 weeks now that we cannot add items to the frontend! We even updated to 1.4.1 with all its issues hoping it would help, but it didn’t.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Nilands55
Sr. Member
 
Avatar
Total Posts:  169
Joined:  2010-03-18
Laurinburg NC
 

I used the above SQL and found 46 duplicate sku’s and deleted them, but I still can’t reindex product prices. anyone have an idea how to find the rest of duplicate records?
Thanks James Niland
http://nilandsplace.com

 
Magento Community Magento Community
Magento Community
Magento Community
 
Paine
Member
 
Total Posts:  73
Joined:  2010-01-25
 

Just wanted to put an update on this. Turns out we had a store with a hyphen \"-\" in the url. This was the source of all our problems. Delete this store/site and a few other unused ones and the problem cleared right up.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2