This phrase gave me nightmares for days as I was building the new 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’;



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


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:
  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


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.


No comments yet.

Leave a Reply