Magento Reindex problem

Question

I am having Magento CE 1.9.2.4, and 1.9.3.0, 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.

Answer 1

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);
    $process->reindexAll();
}

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.

Answer 2

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/