1

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.

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.

  • the first query returns this : > #1146 - Table 'bioimager_shop.catalog_category_product_ccp' doesn't exist – Mostafa Safarian Oct 31 '14 at 14:22
  • oh, sorry, that should have read catalog_category_product ccp with the space in – Richard Oct 31 '14 at 14:27
  • I've run those queries , It's still saying this : > 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) – Mostafa Safarian Oct 31 '14 at 14:34
  • Yeah, I said those queries would list the ids in those tables that were violating the constraints on them, not that it would fix it. I said to fix it you'd then need to delete those rows. – Richard Oct 31 '14 at 14:36
  • I know , but those queries returns zero rows , 0 results ! – Mostafa Safarian Oct 31 '14 at 15:05
-3

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.

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.