I am having Magento CE, and, on both of these websites, 'category products' cannot be reindex. I saw a post here and followed then can reindex it. I thought my problem was resolved and start to upload a thousand items. Afterward the same problem came back.

I try to reindex it with shell, but no luck thus far. I need help to fix this issue permanently. Bellow is the error logs:

2016-12-29T11:35:33+00:00 DEBUG (7): Exception message: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`microwavedb`.`catalog_category_product_index`, CONSTRAINT `FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DEL), query was: INSERT INTO `catalog_category_product_index` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `catalog_category_product_index_idx`.`category_id`, `catalog_category_product_index_idx`.`product_id`, `catalog_category_product_index_idx`.`position`, `catalog_category_product_index_idx`.`is_parent`, `catalog_category_product_index_idx`.`store_id`, `catalog_category_product_index_idx`.`visibility` FROM `catalog_category_product_index_idx` ON DUPLICATE KEY UPDATE `category_id` = VALUES(`category_id`), `product_id` = VALUES(`product_id`), `position` = VALUES(`position`), `is_parent` = VALUES(`is_parent`), `store_id` = VALUES(`store_id`), `visibility` = VALUES(`visibility`)

Thank you in advance for your time and help.


1) magento programatically re index :

ID      Code

1       catalog_product_attribute

2       catalog_product_price

3       catalog_url

4       catalog_product_flat

5       catalog_category_flat

6       catalog_category_product

7       catalogsearch_stock

8       cataloginventory_stock

9       tag_summary
for ($i = 1; $i <= 9; $i++) {
    $process = Mage::getModel('index/process')->load($i);

2) It seems Magento did not clean the table when you have deleted some informations; so you need to clean it manually, using this SQL query:

TRUNCATE TABLE ´catalog_product_flat_1´;

Then, run reindex process.

It's okay to empty that table; since Magento uses EAV tables to rebiuld (reindex) it again.

  • the magento programatically or SSH give me no luck. I follow your second point and truncate table 'catalog_product_flat_1, I have flat_2 and 3 too, don;t know what to do in it, do I have to? I am without luck so far. – VL Thlan Dec 29 '16 at 18:20

The var/locks directory is created whenever you start an indexing process. Magento writes out a lock file to this directory so removing the lock files may resolve issues with reindexing.

Locate var/locks directory and remove all files under this directory. This will clear all the locks for re-indexing to take place again.

if the directory isn't there! Magento hasn't created it. If this is the case, then you should look for other solutions for fixing the reindexing issue.

Maybe your var directory doesn't have the right permissions. Magento needs sufficient permissions to be able to create the var/locks directory and write out the lock files. Try setting permissions for var to (at least) 755.

Try to reindex via SSH.

Otherwise try this solution but read carefully. http://www.magikcommerce.com/blog/how-to-resolve-magento-reindexing-errors-in-your-magento-store/

  • thank you for your time. I got no 'locks' inside var folder, my var folder permission is 755. I manually create locks folder and give 755 permission(do you meant for 'log' folder which I had?). I already used SSH to reindex without luck. The url you provided and my tables are not the same. – VL Thlan Dec 29 '16 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.