0

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)

  • Make sure that your tables are using the INNODB storage engine instead of MyISAM. I've had that bite me in the ass with a site I took over from another agency which was getting this error all the time. – Joseph Leedy Mar 17 '16 at 12:23
3

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;
    

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.