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

Page 1 of 3
Unable to reindex Product Flat Data - Stuck on Processing. ‘SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row
 
james_oneill
Jr. Member
 
Total Posts:  4
Joined:  2010-09-17
 

Hi

I am setting up a new store - running on Magento CE 1.6.0.0

Recently the Product Flat Data indexing process is stuck on Processing.

When I run the php indexer.php --reindex catalog_product_flat within the shell directory through SSH I am presented with this error:

[~/public_html/shell]# php indexer.php --reindex catalog_product_flat
Product Flat Data 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 (`shop4it_mage/#sql-49a0_657d`, CONSTRAINT `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_ id`) ON DELETE CASCADE ON UPDAT)' in /home/shop4it/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace
:
#0 /home/shop4it/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/shop4it/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db _Statement_Pdo->_execute(Array)
#2 /home/shop4it/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement _Pdo_Mysql->_execute(Array)
#3 /home/shop4it/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/shop4it/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ca...', Array)
#5 /home/shop4it/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ca...', Array)
#6 /home/shop4it/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(327): Varien_Db_Adapter_Pdo_Mysql->query('ALTER TABLE `ca...')
#7 /home/shop4it/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(2492): Varien_Db_Adapter_Pdo_Mysql->raw_query('ALTER TABLE `ca...')
#8 /home/shop4it/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(806): Varien_Db_Adapter_Pdo_Mysql->addForeignKey('FK_CAT_PRD_FLAT...', 'catalog_product...', 'entity_id', 'catalog_product...', 'entity_id', 'CASCADE', 'CASCADE')
#9 /home/shop4it/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(125): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prepareFlatTable(1)
#10 /home/shop4it/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(115): Mage_Catalog_Model_Resource_Product_Flat_Indexer->rebuild('1')
#11 /home/shop4it/public_html/app/code/core/Mage/Catalog/Model/Product/Flat/Indexer.php(73): Mage_Catalog_Model_Resource_Product_Flat_Indexer->rebuild(NULL)
#12 /home/shop4it/public_html/app/code/core/Mage/Catalog/Model/Product/Indexer/Flat.php(303): Mage_Catalog_Model_Product_Flat_Indexer->rebuild()
#13 /home/shop4it/public_html/app/code/core/Mage/Index/Model/Process.php(159): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#14 /home/shop4it/public_html/app/code/core/Mage/Index/Model/Process.php(187): Mage_Index_Model_Process->reindexAll()
#15 /home/shop4it/public_html/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#16 /home/shop4it/public_html/shell/indexer.php(198): Mage_Shell_Compiler->run()
#17 {main}

I have ensured that all the relevant tables are in InnoDB as per other threads.

I deleted all the locks and cache from the /var/ directory and used the options within Cache Management to clear out other caches.

I do have a third party module installed which imports products and data from all my suppliers - which also creates the categories etc.

All of the other reindexing processes work fine without error.

In my .mycnf I have the settings to increase buffer and timeouts:

wait_timeout 34000

innob_buffer_pool_size 
900MB

The error message above also references the table #sql-49a0_657d which does not exist in my database…

Does anyone know how I can resolve this or anything else I can try?

 
Magento Community Magento Community
Magento Community
Magento Community
 
tbacrom
Member
 
Total Posts:  32
Joined:  2011-04-18
 

I am having a similar problem, running on 1.5.1.0, all other indexing works, on the Product Flat Data is still hanging on the processing button and indicates that it cannot initalize he indexer process.

Any help please.

 
Magento Community Magento Community
Magento Community
Magento Community
 
maryz
Jr. Member
 
Total Posts:  2
Joined:  2010-08-15
 

Interesting! I am also having this issue. We are running 1.5.1.0 we did recently added a bulk upload extension. Wondering if either of you did as well.

My “Product Prices” reindex is stuck in” processing and I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘6667-0-1-37162’ for key 1

 
Magento Community Magento Community
Magento Community
Magento Community
 
Bertrand
Jr. Member
 
Total Posts:  3
Joined:  2010-06-14
 

Same kind of problem… I updated to 1.6.1.0, and then “Cannot initialize the indexer process.” on “Product Flat Data” indexation.
Exception.log gives the following error :

2011-10-26T15:53:59+00:00 DEBUG (7): Exception messageSQLSTATE[23000]Integrity constraint violation1452 Cannot add or update a child rowa foreign key constraint fails (`omswitom/#sql-a54_190356`, CONSTRAINT `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_IDFOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE )
I searched in the database structure, and could not find any “FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID”. I should find it, should’nt I ?
 
Magento Community Magento Community
Magento Community
Magento Community
 
Francesco2008
Member
 
Total Posts:  67
Joined:  2008-03-26
 

I Solved with this post:

http://www.rohde-christian.de/magento-fehler-beim-indexer-sqlstate23000-integrity-constraint-violation-a-foreign-key-constraint-fails/

I cancelled in “catalog_product_flat_1” the rows of the products ID obtained with this query in PhpMyAdmin:

SELECT pf1.entity_id
FROM catalog_product_flat_1 pf1
LEFT JOIN catalog_product_entity p ON pf1
.entity_id p.entity_id
WHERE ISNULL
p.entity_id );

In my case: ID were 1 and 2 (so two bad rows to delete).

Then from CLI:

php shell/indexer.php --reindex catalog_product_flat

and I got, finally: “Product Flat Data index was rebuilt successfully” !!!

Francesco

 
Magento Community Magento Community
Magento Community
Magento Community
 
MageClub
Guru
 
Avatar
Total Posts:  386
Joined:  2009-07-25
India
 

Hey mate, that SQL query returns

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0006 sec )

SELECT pf1.entity_id
FROM catalog_product_flat_1 pf1
LEFT JOIN catalog_product_entity p ON pf1.entity_id = p.entity_id
WHERE ISNULL( p.entity_id )
LIMIT 0 , 30

Francesco2008 - 08 November 2011 03:12 PM

I Solved with this post:

http://www.rohde-christian.de/magento-fehler-beim-indexer-sqlstate23000-integrity-constraint-violation-a-foreign-key-constraint-fails/

I cancelled in “catalog_product_flat_1” the rows of the products ID obtained with this query in PhpMyAdmin:

SELECT pf1.entity_id
FROM catalog_product_flat_1 pf1
LEFT JOIN catalog_product_entity p ON pf1
.entity_id p.entity_id
WHERE ISNULL
p.entity_id );

In my case: ID were 1 and 2 (so two bad rows to delete).

Then from CLI:

php shell/indexer.php --reindex catalog_product_flat

and I got, finally: “Product Flat Data index was rebuilt successfully” !!!

Francesco
 
Magento Community Magento Community
Magento Community
Magento Community
 
faIsaL_bhaTti
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2011-11-22
Lahore
 

Mine too .
the query returned “MySQL returned an empty result set (i.e. zero rows). (Query took 0.0006 sec)”

SELECT pf1.entity_id
FROM catalog_product_flat_1 pf1
LEFT JOIN catalog_product_entity p ON pf1.entity_id = p.entity_id
WHERE ISNULL( p.entity_id ) ;

LIMIT 0 , 30

Now what to do ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
gmolittieri
Jr. Member
 
Total Posts:  3
Joined:  2011-09-20
 

This happens to our Magento installation often because we are constantly importing products and deleting them from PHPMyAdmin.

The following fixes this error every time.

First, search for “catalog_product_flat” there should only be 1 matching table. For us, its catalog_product_flat_1

Then run under SQL:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE catalog_product_flat_1

Then in SSH, run:

php path/to/shell/indexer.php --reindex catalog_product_flat

Hope this helps someone!

 
Magento Community Magento Community
Magento Community
Magento Community
 
ethos
Jr. Member
 
Total Posts:  9
Joined:  2011-02-09
 

Hi,

The catalog_product_flat_# table is a flat table containing data from EAV product catalog tables.
If you have problem with reindexing the catalog, truncate (drop the content) that table and reindex your data.

It seems that Magento doesn’t clean that table when you delete some information.

In my case, that table was full of old deleted products.

 
Magento Community Magento Community
Magento Community
Magento Community
 
tomharding
Member
 
Avatar
Total Posts:  63
Joined:  2011-03-24
 
gmolittieri - 19 December 2011 07:06 AM

This happens to our Magento installation often because we are constantly importing products and deleting them from PHPMyAdmin.

The following fixes this error every time.

First, search for “catalog_product_flat” there should only be 1 matching table. For us, its catalog_product_flat_1

Then run under SQL:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE catalog_product_flat_1

Then in SSH, run:

php path/to/shell/indexer.php --reindex catalog_product_flat

Hope this helps someone!

Thanks mate, that worked a charm!

 
Magento Community Magento Community
Magento Community
Magento Community
 
moleygunn
Member
 
Total Posts:  73
Joined:  2008-11-25
 
ethos - 06 February 2012 07:48 AM

Hi,

The catalog_product_flat_# table is a flat table containing data from EAV product catalog tables.
If you have problem with reindexing the catalog, truncate (drop the content) that table and reindex your data.

It seems that Magento doesn’t clean that table when you delete some information.

In my case, that table was full of old deleted products.

Thank you! This worked for me in Magento 1.7. I used the Empty option in myPHPAdmin.

PLEASE NOTE: Disabling foreign key checks as recommended by gmolittieri above will probably fix the problem, but can result in huge database problems later on, which can break a site beyond repair.

 
Magento Community Magento Community
Magento Community
Magento Community
 
sneako
Member
 
Total Posts:  48
Joined:  2009-12-05
 
ethos - 06 February 2012 07:48 AM

Hi,

The catalog_product_flat_# table is a flat table containing data from EAV product catalog tables.
If you have problem with reindexing the catalog, truncate (drop the content) that table and reindex your data.

It seems that Magento doesn’t clean that table when you delete some information.

In my case, that table was full of old deleted products.

Fixed it for me on 1.7.0.2.

Thanks!!

 
Magento Community Magento Community
Magento Community
Magento Community
 
BionicSquid
Jr. Member
 
Total Posts:  3
Joined:  2010-03-31
 

Thank God! I’ve just spent two days searching for an answer to this problem and came up with all sorts of recommendations that didn’t work. I had emptied the table but not dropped it, and finally this worked when I did totally drop catalog_product_flat_1. I didn’t set foreign keys to 0 and it did still work, so perhaps it’s safer that way? Anyway, thanks.

 
Magento Community Magento Community
Magento Community
Magento Community
 
BlueMe
Jr. Member
 
Total Posts:  5
Joined:  2012-10-02
 

Can someone please provide a stept by step instructions.. this is very annoying, I’m right at my deadline for this project, I’ve finished the website and now I can’t add products after this “feature” error.

I’ve read your answers but I don’t know how to go about.

 
Magento Community Magento Community
Magento Community
Magento Community
 
BlueMe
Jr. Member
 
Total Posts:  5
Joined:  2012-10-02
 

Don’t want to be rude, but is this forum dead ?  LOL

I didn’t manage to finish my project in time because of this stupid “feature”, and now I have to start all over, luckily is not more than a full day of work ohh

 
Magento Community Magento Community
Magento Community
Magento Community
 
M i c h a
Jr. Member
 
Avatar
Total Posts:  12
Joined:  2009-09-07
Netherlands
 

Hi, i had the same issue last week. Thanks to this topic i fixed the problem. Please make a DB backup! Switch off the Use Flat Product and Category options in Magento and switch the Index mode from the Product Flat Data to Manual update. Now go into your DB. You can delete the catalog_product_flat_xx table(s) now. You must reindex the catalog via SSH. Please ask your host how to do that. It looks like this:

usr/local/bin/php public_html/shell/indexer.php --reindex catalog_product_flat

Now Magento create new tables. For every shop one. Can take a few seconds also longer. When it\’s done you can activate the use Flat products option. Please check if Magento can reindex the data also.

MiC

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 3