CODEWITHSUNDEEP
phpmagento
Shani1351
Shani1351

Reputation: 509

Can't re-index new products after upgrading to version 1.6

I've upgraded my site from version 1.4.0.1 to version 1.6. I've deleted all the products from the website and did "reindex all" that was finished successfully. When I try to create a new product and than do a re-index again I get this error :

Product Attributes index process unknown error:
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`.../catalog_product_index_eav`, CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CAS)' in .../lib/Zend/Db/Statement/Pdo.php:228

I've tried to TRUNCATE TABLE catalog_product_index_eav but it didn't help.

How can I fix this?

Upvotes: 1

Views: 4101

Answers (3)

JaseC
JaseC

Reputation: 3209

This only removes rogue data that shouldn't be there.

I've expanded the above as it may save some hours of digging: 

delete FROM `catalog_product_entity_datetime` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_decimal` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_gallery` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_group_price` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_int` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_media_gallery` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_text` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_tier_price` where entity_id not in (select entity_id from catalog_product_entity);
delete FROM `catalog_product_entity_varchar` where entity_id not in (select entity_id from catalog_product_entity);
#originals with formatting fixed:
delete from `catalog_category_product` WHERE product_id not in(select entity_id from catalog_product_entity);
delete from `catalog_category_product` WHERE category_id not in(select entity_id from catalog_category_entity); 
delete from `catalog_product_website` WHERE product_id not in(select entity_id from catalog_product_entity); 
delete from `catalog_product_index_eav_idx` WHERE entity_id not in(select entity_id from catalog_product_entity); 
delete from `catalog_product_index_eav` WHERE entity_id not in(select entity_id from catalog_product_entity); 
delete from `catalog_product_link` WHERE product_id not in(select entity_id from catalog_product_entity); 
delete from `catalog_product_relation` WHERE parent_id not in(select entity_id from catalog_product_entity);

Upvotes: 17

Vijay Kumar
Vijay Kumar

Reputation: 21

I used following queries to clean catalog tables;

delete from `catalog_category_product` WHERE product_id not in(select entity_id from catalog_product_entity)

delete from `catalog_category_product` WHERE category_id not in(select entity_id from catalog_category_entity) 

delete from ` catalog_product_website` WHERE product_id not in(select entity_id from catalog_product_entity) 

delete from ` catalog_product_entity_media_gallery` WHERE entity_id not in(select entity_id from catalog_product_entity) 

delete from ` catalog_product_index_eav_idx` WHERE entity_id not in(select entity_id from catalog_product_entity) 

delete from ` catalog_product_index_eav` WHERE entity_id not in(select entity_id from catalog_product_entity) 

delete from ` catalog_product_link` WHERE product_id not in(select entity_id from catalog_product_entity) 

delete from ` catalog_product_relation` WHERE parent_id not in(select entity_id from catalog_product_entity)

After this i was able to reindex from admin panel.

Upvotes: 1

Josh Pennington
Josh Pennington

Reputation: 6408

If you are doing this on a live site, you probably need to do the reindex process from the command line with Apache turned off.

http://overlycaffeinated.com/2011/02/when-reindexing-in-magento-fails-use-the-command-line/

This explains how to do this.

Upvotes: 0

Related Questions