Error when reindexing "Category Products" - Integrity constraint violation: 1452

Question

I cannot reindex "Category Products" from admin - get an error "Cannot initialize the indexer process". Then I printed exception message:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row:
 a foreign key constraint fails (`catalog_category_product_index`, CONSTRAINT `FK_OZSS_CAT_CTGR_PRD_IDX_PRD_ID_OZSS_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`),
 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`)

What I already tried: find the "deleted" products in database as suggested in other questions(but that sql queries return 0 rows for me), truncating the product_flat_data tables (they are empty)

Answer 1

I found the answer that works for me in another thread here https://stackoverflow.com/a/34810007/2810565I :

1. Go to PhpMyAdmin and run this query:

   ALTER TABLE catalog_category_product_index DROP FOREIGN KEY FK_CAT_CTGR_PRD_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID
   

2. Run Reindexing from Magento Admin Panel or from CLI;

3. After the reindexing is complete, run this query from PhpMyAdmin:

   DELETE FROM catalog_category_product_index WHERE product_id not in (select entity_id from catalog_product_entity); 
   ALTER TABLE catalog_category_product_index ADD CONSTRAINT FK_CAT_CTGR_PRD_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (product_id) REFERENCES catalog_product_entity (entity_id) ON DELETE CASCADE ON UPDATE CASCADE;