Magento 1.6.2 Wont Reindex Product Flat Data

Question

I can no longer get magento to reindex correctly as it states there was a problem with the reindexing process. I have used SSH access to try and manually reindex the data. Using SSH access i see the error but unsure as to how to resolve the error log is below.

'pdoexception' with message 'SQLSTATE [23000] : Intergrity constraints violation; 1452 cannot add or update a child row: a foreign key constraint fails ....... <result 2 when explaing filename '#sql-c45_91e313'>, CONSTRAINT 'FK_MAGE_MAGE_CAT_PRD_FLAT_1_ENTT_ID_MAGE_CAT_PRD_ENTT_ENTT_ID' FOREIGN KEY ('entity_id') REFERENCES 'mage_ca)' in home/.../public_html/lib/Zend/Db/Statement/Pdo.php:228

Any help would be appreciated

score 2 · Answer 1 · answered Feb 20 '12 at 09:28

2

Try delete all product flat tables. They will be recreated by indexer.

answered Feb 20 '12 at 09:28

Dmytro Zavalkin

5,265
1
30
34

Would i be correct in assuming these are mage_catalog_product_flat_1 mage_catalog_product_flat_2 and so on – user1220486 Feb 20 '12 at 09:31
Flat table names have next pattern: `_flat_`. That's why you can delete table of needed store only. – Dmytro Zavalkin Feb 20 '12 at 10:36
Hi, got the same problem again today, do you think that there is another solution? – user1220486 Feb 22 '12 at 07:28
Hi, i have again used SSH access to read the logs, the error that I am recieving today is 1118 row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. – user1220486 Feb 22 '12 at 07:51
How many non-core extensions do you have? Or maybe you created a lot of custom text product attributes? – Dmytro Zavalkin Feb 22 '12 at 10:19
This mysql error message says that row length (sum of lengths of non-blob fields in product flat table) is too big. – Dmytro Zavalkin Feb 22 '12 at 10:30
I have quite a few non core extensions installed around 15 or so, I also have alot of text product attributes at least 30 – user1220486 Feb 23 '12 at 06:34
So, this is the answer. Read first 2 paragraphs here: http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html – Dmytro Zavalkin Feb 23 '12 at 09:31
Hi thanks for the reply, how should I rectify the problem. I have uninstalled as many non core extensions as I do not use them that much – user1220486 Feb 23 '12 at 12:19
Unfortunately, magento extensions don't have uninstall scripts. You should delete unused eav attributes from db by hands. – Dmytro Zavalkin Feb 23 '12 at 12:52
Hi, i may be going wrong here but i only have around 15 tables with EAV in the title is this right – user1220486 Feb 23 '12 at 13:33

Akshay · Answer 2 · 2013-12-11T11:26:49.513

Have you tried disabling Flat_catalog_category and Flat_catlaog_product and then truncating the catalog_product_flat tables.Be sure to disable foreign key constraints checks by doing SET FOREIGN_KEY_CHECKS = 0; before truncating. Once doing that you should be able to manually reindex via ssh . A well documented solution for this issue is here : http://binarythoughts21.blogspot.in/2013/12/reindexing-problems-in-magento.html

Magento 1.6.2 Wont Reindex Product Flat Data

2 Answers2