Fixing reindexing price errors in Magento

I’ve upgraded a couple of Magento CE (1.4) to Magento EE (1.11) using the traditional (and effective) way. But I found that there was an important issue when reindexing prices that prevented Magento from doing it.

Reading this post on Magento Boards I found that the problem (in my case) was related to an error with the catalog_product_entity_tier_price table where some of the entries were related to products that didn’t exist anymore. This was the actual error and when trying to reindex product prices, Magento would throw this message:

There was a problem with reindexing process.

How to solve it

So you want to solve this. You have to delete all the entries on the catalog_product_entity_tier_price table that are related to a product that doesn’t exist anymore. You can do it manually or you can use the following script I created. It’s really easy to use.

In that file you’ll find:

  • The script: clean_tier_prices.php
  • A library to connect in a really easy way to the db

Follow this steps:

  1. Make a copy of your database (just in case)
  2. Uncompress the zip file in your magento base directory
  3. Edit the lib/db.php file and put there your connection settings (db user, user pass and db)
  4. Open the browser and go to http://yourmagentopath.com/clean_tier_prices.php
  5. You’ll see wich references have been deleted from the catalog_product_entity_tier_price table
  6. Try reindexing again in your Magento backoffice

Important note: You use this script at your own risk. Make a copy of your database before applying it. If you improve this, please share it with me and the rest of the world!

 

1 comment

    • Mike on October 11, 2013 at 11:56 am

    Reply

    You can also run this query to see if there are tier prices that are linked to unknown products (and then delete these records).

    SELECT `t1`.`entity_id`
    FROM `catalog_product_entity_tier_price` AS `t1`
    LEFT JOIN `catalog_product_entity` AS `t2`
    ON `t1`.`entity_id` = `t2`.`entity_id`
    WHERE `t2`.`entity_id` IS NULL

Leave a Reply

Your email address will not be published.