Error when reindexing flat product data

Question

I receive this error "There was a problem with reindexing process." in Magento when I try to Reindex Data at Product Flat Data. I use Magento 1.4.1.1, and I have over 50 000 itemes in database.

Is a way to fix this?

Here are the errors what I find in exception.log:

2011-04-14T19:24:05+00:00 DEBUG (7): Exception message: SQLSTATE[HY000]: General error: 1005 Can't create table 'magentonou.catalog_product_flat_3' (errno: 150)
Trace: #0 E:\Wamp\www\includes\src\__default.php(48621): Zend_Db_Statement_Pdo->_execute(Array)
#1 E:\Wamp\www\includes\src\__default.php(40291): Zend_Db_Statement->execute(Array)
#2 E:\Wamp\www\includes\src\__default.php(41312): Zend_Db_Adapter_Abstract->query('CREATE TABLE `c...', Array)
#3 E:\Wamp\www\includes\src\__default.php(42062): Zend_Db_Adapter_Pdo_Abstract->query('CREATE TABLE `c...', Array)
#4 E:\Wamp\www\includes\src\Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer.php(544): Varien_Db_Adapter_Pdo_Mysql->query('CREATE TABLE `c...')
#5 E:\Wamp\www\includes\src\Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer.php(122): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer->prepareFlatTable('3')
#6 E:\Wamp\www\includes\src\Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer.php(115): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer->rebuild('3')
#7 E:\Wamp\www\includes\src\Mage_Catalog_Model_Product_Flat_Indexer.php(64): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer->rebuild(NULL)
#8 E:\Wamp\www\includes\src\Mage_Catalog_Model_Product_Indexer_Flat.php(301): Mage_Catalog_Model_Product_Flat_Indexer->rebuild()
#9 E:\Wamp\www\includes\src\Mage_Index_Model_Process.php(139): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#10 E:\Wamp\www\includes\src\Mage_Index_Model_Process.php(167): Mage_Index_Model_Process->reindexAll()
#11 E:\Wamp\www\app\code\core\Mage\Index\controllers\Adminhtml\ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#12 E:\Wamp\www\includes\src\__default.php(11748): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#13 E:\Wamp\www\includes\src\__default.php(15669): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#14 E:\Wamp\www\includes\src\__default.php(15271): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#15 E:\Wamp\www\includes\src\__default.php(17470): Mage_Core_Controller_Varien_Front->dispatch()
#16 E:\Wamp\www\app\Mage.php(596): Mage_Core_Model_App->run(Array)
#17 E:\Wamp\www\index.php(80): Mage::run('', 'store')
#18 {main}

Answer 1

I remember a database repair tool you could download from Varien.

Yeah, checked it. It still exists: Link

I used that tool about a year ago with Magento 1.3.x and was able to repair a database with it successfully.

I do not know, if it can be used for 1.4.x, though. They unfortunately do not state for which versions the tool can be used. Maybe it's stated in the source code.

EDIT:

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

Source: dev.mysql.com

Answer 2

Solution

Take a look at the catalog_eav_attribute and eav_attribute tables. Update the used_in_product_listing field to 0 for all user defined fields that are set to 1. Before running this, use a SELECT clause and see what fields its pulling out. Also, run on a test environment first!!

update `catalog_eav_attribute` as cea left join eav_attribute as ea on cea.attribute_id = ea.attribute_id set cea.used_in_product_listing = 0 where cea.used_in_product_listing = 1 and is_user_defined = 1

…and Fixed!