Reindex error magento 1.9.X.X

Question

2016-04-14T04:50:53+00:00 DEBUG (7): Exception message: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento_repair_db`.`catalog_product_index_eav`, CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`)), query was: INSERT INTO `catalog_product_index_eav` (`entity_id`, `attribute_id`, `store_id`, `value`) SELECT `catalog_product_index_eav_idx`.`entity_id`, `catalog_product_index_eav_idx`.`attribute_id`, `catalog_product_index_eav_idx`.`store_id`, `catalog_product_index_eav_idx`.`value` FROM `catalog_product_index_eav_idx` ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `attribute_id` = VALUES(`attribute_id`), `store_id` = VALUES(`store_id`), `value` = VALUES(`value`)

I have issue during attribute reindexing for layered navigation filter.

How can i solve this issue?

Answer 1

Well I think your issue is due to the products which are creating hindrance in the Index process.

You have to run the below queries for re-indexing to work:

delete FROM `catalog_product_entity_datetime` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_decimal` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_gallery` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_group_price` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_int` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_media_gallery` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_text` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_tier_price` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_varchar` where entity_id not in (select entity_id from catalog_product_entity);
#originals with formatting fixed:
delete from `catalog_category_product` WHERE product_id not in(select entity_id from catalog_product_entity);
delete from `catalog_category_product` WHERE category_id not in(select entity_id from catalog_category_entity); 
delete from `catalog_product_website` WHERE product_id not in(select entity_id from catalog_product_entity); 
delete from `catalog_product_index_eav_idx` WHERE entity_id not in(select entity_id from catalog_product_entity); 
delete from `catalog_product_index_eav` WHERE entity_id not in(select entity_id from catalog_product_entity); 
delete from `catalog_product_link` WHERE product_id not in(select entity_id from catalog_product_entity); 
delete from `catalog_product_relation` WHERE parent_id not in(select entity_id from catalog_product_entity);

Hope this helps you out