I've a problem with re-indexing data in magento. When I click reindex data , It returns this error :

There was a problem with reindexing process. SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_shop`.`catalog_category_product_index`, CONSTRAINT `catalog_category_product_index_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASC)

So I've searched around for a solution for hours and days ...
But I couldn't find a way to fix it.
I think there's a value in database in product_id which is not in entity_id , That's the cause.
So I went into phpmyadmin and ran these queries :

# Determine incorrect product associations
SELECT * FROM `catalog_category_product` WHERE
product_id not in (select entity_id from catalog_product_entity);

# Determine incorrect categories
SELECT * FROM `catalog_category_product` WHERE 
category_id not in (select entity_id from catalog_category_entity);

To find and delete records which is not in both tables, But these queries returns 0 results !
So how can I fix this problem ?


You should use Database Repair tool.

This will

- Adds missing tables, or repairs them so they have the same engine type and charset
- Adds missing fields or repairs them
- Removes inconsistent data from tables with broken foreign key references
- Adds missing foreign keys and indexes

Check here for more : http://merch.docs.magento.com/ce/user_guide/magento/database-repair-tool.html

  • Are you sure this is the right way to fix this issue ? I think this happens just for a missing record not having problems with whole database. – Mostafa Safarian May 7 '16 at 15:46
  • @MostafaSafarian: does this helps ? – Anil Suthar Jul 1 '16 at 12:32

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.