Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Can’t re-index new products after upgrading to ver 1.6
 
shani1351
Sr. Member
 
Total Posts:  156
Joined:  2010-01-24
 

Hello,
I’ve upgraded my site from version 1.4.0.1 to ver 1.6.
I’ve deleted all the products from the website and did reindex all that finished successfully.
When I try to create a new product and than do a re-index 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?

Thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
shani1351
Sr. Member
 
Total Posts:  156
Joined:  2010-01-24
 

Anyone? I’m really stuck

 
Magento Community Magento Community
Magento Community
Magento Community
 
hankzh
Member
 
Total Posts:  56
Joined:  2011-02-08
 

Do you mind post your create table code for catalog_product_entity and catalog_product_index_eav

such as this:
CREATE TABLE `catalog_product_entity` (
`entity_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT \’Entity ID\’,
`entity_type_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT \’0\’ COMMENT \’Entity Type ID\’,
`attribute_set_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT \’0\’ COMMENT \’Attribute Set ID\’,
`type_id` VARCHAR(32) NOT NULL DEFAULT \’simple\’ COMMENT \’Type ID\’,
`sku` VARCHAR(64) NULL DEFAULT NULL COMMENT \’SKU\’,
`has_options` SMALLINT(6) NOT NULL DEFAULT \’0\’ COMMENT \’Has Options\’,
`required_options` SMALLINT(5) UNSIGNED NOT NULL DEFAULT \’0\’ COMMENT \’Required Options\’,
`created_at` TIMESTAMP NULL DEFAULT NULL COMMENT \’Creation Time\’,
`updated_at` TIMESTAMP NULL DEFAULT NULL COMMENT \’Update Time\’,
`exclude_from_supply_needs` TINYINT(4) NOT NULL DEFAULT \’0\’,
PRIMARY KEY (`entity_id`),
INDEX `IDX_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE_ID` (`entity_type_id`),
INDEX `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
INDEX `IDX_CATALOG_PRODUCT_ENTITY_SKU` (`sku`),
CONSTRAINT `FK_CAT_PRD_ENTT_ATTR_SET_ID_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `eav_attribute_set` (`attribute_set_id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COMMENT=\’Catalog Product Table\’
COLLATE=\’utf8_general_ci\’
ENGINE=InnoDB
AUTO_INCREMENT=2????;

 
Magento Community Magento Community
Magento Community
Magento Community
 
shani1351
Sr. Member
 
Total Posts:  156
Joined:  2010-01-24
 

You mean to export it and past here the create table? If so -

--
-- Table structure for table `catalog_product_entity`
--

CREATE TABLE IF NOT EXISTS `catalog_product_entity` (
`entity_id` int(10) unsigned NOT NULL auto_increment COMMENT ‘Entity ID’,
`entity_type_id` smallint(5) unsigned NOT NULL default ‘0’ COMMENT ‘Entity Type ID’,
`attribute_set_id` smallint(5) unsigned NOT NULL default ‘0’ COMMENT ‘Attribute Set ID’,
`type_id` varchar(32) NOT NULL default ‘simple’ COMMENT ‘Type ID’,
`sku` varchar(64) default NULL COMMENT ‘SKU’,
`has_options` smallint(6) NOT NULL default ‘0’ COMMENT ‘Has Options’,
`required_options` smallint(5) unsigned NOT NULL default ‘0’ COMMENT ‘Required Options’,
`created_at` timestamp NULL default NULL COMMENT ‘Creation Time’,
`updated_at` timestamp NULL default NULL COMMENT ‘Update Time’,
PRIMARY KEY (`entity_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE_ID` (`entity_type_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_SKU` (`sku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’Catalog Product Table’ AUTO_INCREMENT=2 ;

--
-- Dumping data for table `catalog_product_entity`
--

INSERT INTO `catalog_product_entity` (`entity_id`, `entity_type_id`, `attribute_set_id`, `type_id`, `sku`, `has_options`, `required_options`, `created_at`, `updated_at`) VALUES
(1, 4, 4, ‘simple’, ‘test1’, 0, 0, ‘2011-11-15 14:45:43’, ‘2011-11-15 14:45:43’);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `catalog_product_entity`
--
ALTER TABLE `catalog_product_entity`
ADD CONSTRAINT `FK_CAT_PRD_ENTT_ATTR_SET_ID_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_CAT_PRD_ENTT_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Table structure for table `catalog_product_index_eav`
--

CREATE TABLE IF NOT EXISTS `catalog_product_index_eav` (
`entity_id` int(10) unsigned NOT NULL default ‘0’ COMMENT ‘Entity ID’,
`attribute_id` smallint(5) unsigned NOT NULL default ‘0’ COMMENT ‘Attribute ID’,
`store_id` smallint(5) unsigned NOT NULL default ‘0’ COMMENT ‘Store ID’,
`value` int(10) unsigned NOT NULL default ‘0’ COMMENT ‘Value’,
PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`),
KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID` (`entity_id`),
KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’Catalog Product EAV Index Table’;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `catalog_product_index_eav`
--
ALTER TABLE `catalog_product_index_eav`
ADD CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD 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 CASCADE ON UPDATE CASCADE;

 
Magento Community Magento Community
Magento Community
Magento Community
 
superheld
Sr. Member
 
Total Posts:  106
Joined:  2010-05-13
 

hi!

did you solve the problem? i did make the upgrade before a couple of days and have now the same problem.

 
Magento Community Magento Community
Magento Community
Magento Community
 
olivenet
Jr. Member
 
Total Posts:  3
Joined:  2011-01-31
 

Did either of you solve this?  I have exactly the same problem.

 
Magento Community Magento Community
Magento Community
Magento Community
 
olivenet
Jr. Member
 
Total Posts:  3
Joined:  2011-01-31
 

I think I have this cracked.  Hopefully this will help anyone else with the same problem.

Magento was trying to add two products in catalog_product_index_eav that didn’t exist any more, so the foreign key to catalog_product_entity was broken.  An attempt to reindex failed on this unless I removed the foreign key constraint temporarily.  This error was most obvious and easily to see when running the indexer from the command line.

So I removed the foreign key ( FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID ) using a DB manager tool.  I then reindexed.  This added my problem products to catalog_product_index_eav and I was able to look for them.

To find my trouble products I used:

SELECT * FROM `catalog_product_entity` a RIGHT JOIN `catalog_product_index_eav` b ON a.`entity_id` = b.`entity_id` WHERE a.entity_id IS NULL;

once I found them I could delete the rows in catalog_product_index_eav, but re-indexing would add them again (if the foreign key was removed allowing it) or fail with the same constraint error.

So, next step was to find them in the rest of the DB.  I just searched through the tables looking for entity_ids, product_ids, parent_ids and child_ids (depending on table) in any table that contains product info.  I found reference to my two trouble products in:

catalog_product_website

catalog_category_product

catalog_product_entity_media_gallery

catalog_product_index_eav_idx

catalog_product_link

catalog_product_relation

catalog_product_super_attribute

there could be more or less tables with an entry for different product types and site configurations, so it’s worth running through all likely tables running selects on your trouble product ids, eg:

SELECT * FROM `catalog_category_product` WHERE `product_id` = 364 OR `product_id` = 421;

as I mentioned above, sometimes ids will be ‘entity_id’s, sometimes ‘product_id’s and sometimes parent and child ids.  It’s fairly common sense, but time consuming.  Lots of likely tables are empty in my shop so worth checking if there’s anything at all in a table before running a select statement.

After I deleted all entries referring to my two products I added the foreign key constraint to catalog_product_index_eav again:

alter table `thistribe`.`catalog_product_index_eav` add 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 CASCADE ON UPDATE CASCADE

and reindexed everything successfully.

Finally it is worth mentioning that one of my two trouble products was a configurable product, and I was left with orphan simple products which didn’t show in the catalog.  I deleted these using the normal admin controls, then recreated the configurable product

__________

An error that also occurred during this problem was:

Cannot add or update a child row: a foreign key constraint fails (`thistribe`.<result 2 when explaining filename ‘#sql-640_c’>, CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entit)

I add that for search purposes as it might help someone find this thread

 
Magento Community Magento Community
Magento Community
Magento Community
 
Vijay Sharma
Jr. Member
 
Total Posts:  22
Joined:  2009-03-29
 

Thanks Olivenet,

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
e tailors nl
Jr. Member
 
Avatar
Total Posts:  1
Joined:  2011-02-22
Heerlen, the Netherlands
 

@olivenet

you’re a hero wink solved my problems. it was giving me major headaches.

thanx!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Gabriel Queiroz
Sr. Member
 
Total Posts:  135
Joined:  2008-05-28
Brasília, Brazil
 

@olivenet: Great way to fix a messed up database! Thanks a lot!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Eddyzoe
Jr. Member
 
Total Posts:  2
Joined:  2009-10-19
The Netherlands, Alkmaar
 

Thnx Vijay Sharma.

In my case the error was:
exception PDOException with message SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`dbname`.<result 2 when explaining filename #sql-59d8_4a1fd >, CONSTRAINT FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_enti)

the solution:

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

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top