Problem with reindexing process SQLSTATE[23000]?

Question

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 ?

Answer 1

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