Magento reindexing problems – Solutions

Reading Time: 2 minutes

If you have Magento store sure you have seen “There was a problem with reindexing process” error message.If you try to reindex from magento admin panel, all options are not reindexing.There are may be category flat data,product flat data and product prices reindex errors.Here I’ll describes how to solve this reindex problem in the magento store.

Please keep backup files before do these methods.

What are the reasons for magento reindexing error

• The number of products
• The number of stores
• The apache/lighttpd timeout settings
• There are references to deleted products in catalog_product_flat_% tables
• The php.ini maximum execution time

Method-1

1-Backup the table “catalog_category_product_index” with “Disable foreign key checks” marked.
2-Drop that table from the database.
3-Open that mysql file of the table and remove the constraints at the bottom, except “SET FOREIGN_KEY_CHECKS=1;” .Then it look like this.

    "ALTER TABLE `catalog_category_product_index`
    ADD CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_CATEGORY_ENTITY` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `FK_CATALOG_CATEGORY_PROD_IDX_PROD_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;"

4-Save and execute that file in that database.
5-Then clear the magento all cache and do the reindexing process in the magento backend.
If not success, do the following Method-2.

Method-2

1-Increase your execution time value in the .htaccess file.

   php_value memory_limit 128M
   php_value max_execution_time 18000

2-Create a file reindexing.php in the root.
3-Copy the following scripts,paste and save that file.

 load($index);
     $process->reindexAll();
 }
 ?>

4-Then clear the magento all cache and run this file as http://mymagento.com/reindexing.php
Note:Sometimes it will take long time because it will reindexing all.If you have shell access do the following method to do this quickly.

php shell/indexer.php reindex all


Method-3

How to solve “An error occurred while saving the URL rewrite” or “cannot initialize indexer process” problems

1-Goto the magento root directory and goto var/locks directory and delete all the files under it.
2-Change the var/locks folder permission to 777.
3-Run the following mysql query in your database.

    DELETE cpop.* FROM catalog_product_option_price AS cpop
    INNER JOIN catalog_product_option AS cpo
    ON cpo.option_id = cpop.option_id
    WHERE
    cpo.type = 'checkbox' OR
    cpo.type = 'radio' OR
    cpo.type = 'drop_down';
    DELETE cpotp.* FROM catalog_product_option_type_price AS cpotp
    INNER JOIN catalog_product_option_type_value AS cpotv
    ON cpotv.option_type_id = cpotp.option_type_id
    INNER JOIN catalog_product_option AS cpo
    ON cpotv.option_id = cpo.option_id
    WHERE
    cpo.type <> 'checkbox' AND
    cpo.type <> 'radio' AND
    cpo.type <> 'drop_down';

4-Clear Magento all cache and try to reindexing process in the Magento backend.

Method-4

1-Log into the phpmyadmin and find “catalog_product_flat_%” tables.Those are

• catalog_product_flat_1
• catalog_product_flat_2
• catalog_product_flat_3

2-Try to truncate/empty those tables.
3-Clear the magento all cache and try to reindexing process in the magento backend.
That’s all.If you have problems,ideas,tips about this put as comments below.