Cannot Initialize the Indexer Process in Magento

 Jun, 07 - 2012   12 comments   ecommerceWeb Development


This phrase gave me nightmares for days as I was building the new BeltBuckle.com website.  We installed a ton of extensions to automate the website, and somewhere along the line some database tables got corrupted, so I had to start a what seemed like an endless game of cat and mouse chasing down what was really wrong with the site.  I’ve compiled a list of everything I tried along the way to fix the issue:

    1. Make sure the var/locks folder is writable.  Test out full permissions (chmod 777).
    2. Delete all of the .locks files from the var/locks folder.
    3. Backup your database, then run this query in phpmyadmin: DELETE FROM catalog_category_product_index;
    4. Try the magento cleanup tool 
    5. Try the magento database repair tool
    6. Backup your database and run this query in phpmyadmin:
      1. DELETE cpop.* FROM catalog_product_option_price AS cpop
      2. INNER JOIN catalog_product_option AS cpo
      3. ON cpo.option_id = cpop.option_id
      4. WHERE
      5. cpo.type = ‘checkbox’ OR
      6. cpo.type = ‘radio’ OR
      7. cpo.type = ‘drop_down’;DELETE cpotp.* FROM catalog_product_option_type_price AS cpotp
      8. INNER JOIN catalog_product_option_type_value AS cpotv
      9. ON cpotv.option_type_id = cpotp.option_type_id
      10. INNER JOIN catalog_product_option AS cpo
      11. ON cpotv.option_id = cpo.option_id
      12. WHERE
      13. cpo.type <> ‘checkbox’ AND
      14. cpo.type <> ‘radio’ AND
      15. cpo.type <> ‘drop_down’;
      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';

After running all of these options and still not having any luck in the admin, I decided to try and reindex by command line.  Just SSH into your site, and run this command:

  1. php /path/to/magento/shell/indexer.php reindexall
php /path/to/magento/shell/indexer.php reindexall

Magento will then start to run the reindex process, updating the command line as it progresses.  When it hits the error mark, it will show you what the actual error is.  In my case, this is what I got:

 

  1. Product Flat Data index process unknown error:
  2.  
  3. exception ‘PDOException’ with message ‘SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`alleight_bbcom/#sql-6d5d_2cb103`, CONSTRAINT `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON )’ in /home/alleight/public_html/bbcom/lib/Zend/Db/Statement/Pdo.php:228
Product Flat Data index process unknown error:

exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`alleight_bbcom/#sql-6d5d_2cb103`, CONSTRAINT `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON )' in /home/alleight/public_html/bbcom/lib/Zend/Db/Statement/Pdo.php:228

In the error, you can see that there’s a problem with the catalog product flat data.  We simply truncated the table catalog_flat_product_1, and voila.  Reindex successful.


Related articles

 Comments 12 comments

  • Adam says:

    I hate you (and secretly love you) for introducing me to beltbuckle.com – I just went and spent $100 there on some cool tin. Thanks and :P

    :)

    Oh, and cheers for the Magento solve…

  • Pankaj Sharma says:

    Thanks for sharing the complete process. I solved my problem using SSH
    php /path/to/magento/shell/indexer.php reindexall
    It showed some error :
    Product Prices index process unknown error:

    So i truncated catalog_product_entity_group_price table and it worked.
    Thanks for Share.

  • I am at my wits end, I have followed your steps.
    Was stuck with the database repair tool. Do I need to install a fresh copy of magento in database3 step so I have a clean version of same version of magento for it to compare.

    If so I did this and then it turned my repaired database into the default magento template instead of my template. Think Im going just a little bit insane!

    • admin says:

      Magento has a tendency to make you go a little insane.

      No, you don’t have to install a fresh copy in step 3. I mentioned the backup since you’re editing the database directly so it’s always good to do a backup in case something funky happens when you’re running that query.

      So if you installed a fresh copy of Magento, did you upload your theme again? You might just have to upload your theme again and change the design in the admin.

  • Tudor says:

    I have your problem … but in catalog_product_flat_3

    should I delete all the entries from here????
    I won’t delete my products??

    t

    • admin says:

      You can truncate catalog_product_flat tables without deleting your entire product catalog. However, I highly recommend doing a backup of your database before doing any direct database edits.

  • anwar says:

    I have problem in Product flat data reindex. I have truncate catalog_product_flat_1,2,3,4 but can’t reindex.

    Before i insert group price with sql and some layered navigation information i insert into catalog_product_entity_varchar table with sql. highly impressed if you suggest me any solution.

  • Alex says:

    Hey I had the same problem and nothing fixed it. WIth Mage 1.7 , I simply went to the admin/system/configuration/catalog then “frontend”, switched Use Flat Catalog Product to “Yes”. Reindex worked. Then back there, switched it back to “no”. Reindex again, it worked. Hope it helps :D

    PS : I don’t know if it is related, but I did truncate the catalog_product_fat tables, and empty var folder before (it didn’t change anything until I did what’s up there, tho)

    PS 2 : Problem initially happened after my Reindex timed out.

  • Nadine says:

    My Magento couldn’t reeindex either, but I had a slighty different PDOException (1005 could’ create database …)

    The suggestiosn above couldn’t fix it.

    Because of your hint with catalog_product_flat_1 I tried to delete catalog_product_flat_1,2,3,4 .
    Magento creates them again when you reindex afterwards – and it worked for me!

    Maybe it also helps somebody else – and thank you for your help :)


  • Leave a Reply

    Your email address will not be published. Fields with * are mandatory.

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code lang=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" extra="">