Problem with Reindex Data Category Products SQLSTATE[23000]

Question

After adding some products to the magento , I'm now getting this error when i try to reindex "category products" :

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 (`domain_shop`.`catalog_category_product_index`, CONSTRAINT `catalog_category_product_index_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CAS)

I've searched for answers here , and I tried some queries in phpmyadmin , but none of them worked . Also , I don't have access to SSH of my website . Control panel is DirectAdmin. Any helps would be appreciated.

Answer 1

Somehow you've managed to break your database integrity. That error message is telling you that there is a value in product_id that's not in catalog_product_entity.entity_id, which makes it look like you've deleted a product, and it's didn't get deleted from catalog_category_product or catalog_category_product_index like what it's supposed to.

If that's the case then these queries will list the ids for you

select product_id from catalog_category_product ccp left join catalog_product_entity cpe ON product_id = entity_id where entity_id is null;
select product_id from catalog_category_product_index ccpi left join catalog_product_entity cpe ON product_id = entity_id where entity_id is null;

I think you'll be able to delete the rows that have those product_ids and then you'll be able to rebuild the index.

Answer 2

Execute this query:

TRUNCATE `sales_flat_quote`; 
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1; 
TRUNCATE `sales_flat_quote_address`; 
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1; 
TRUNCATE `sales_flat_quote_address_item`; 
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1; 
TRUNCATE `sales_flat_quote_item`; 
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1; 
TRUNCATE `sales_flat_quote_item_option`; 
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;
TRUNCATE `sales_flat_quote_payment`; 
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;

TRUNCATE `log_customer`;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;
TRUNCATE `log_quote`;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;
TRUNCATE `log_summary`;
ALTER TABLE `log_summary` AUTO_INCREMENT=1;
TRUNCATE `log_summary_type`;
ALTER TABLE `log_summary_type` AUTO_INCREMENT=1;
TRUNCATE `log_url`;
ALTER TABLE `log_url` AUTO_INCREMENT=1;
TRUNCATE `log_url_info`;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
TRUNCATE `log_visitor`;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
TRUNCATE `log_visitor_info`;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
TRUNCATE `report_event`;
ALTER TABLE `report_event` AUTO_INCREMENT=1;

Then reindex again.