We started having a product flat data re indexing error out of no where. We didnt install any new extensions, the only thing we have done was added new products.

running re-index via ssh we see the following error:

Product Flat Data index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1005 Can't create table 'databasename.#sql-4e0_3486' (errno: 121)' in /public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ca...', Array)
#5 /public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ca...', Array)
#6 /public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(347): Varien_Db_Adapter_Pdo_Mysql->query('ALTER TABLE `ca...')
#7 /public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(2731): Varien_Db_Adapter_Pdo_Mysql->raw_query('ALTER TABLE `ca...')
#8 /public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(815): Varien_Db_Adapter_Pdo_Mysql->addForeignKey('FK_CAT_PRD_FLAT...', 'catalog_product...', 'entity_id', 'catalog_product...', 'entity_id', 'CASCADE', 'CASCADE')
#9 /public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(1389): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prepareFlatTable(1)
#10 /public_html/app/code/core/Mage/Catalog/Model/Product/Flat/Indexer.php(296): Mage_Catalog_Model_Resource_Product_Flat_Indexer->reindexAll()
#11 /public_html/app/code/core/Mage/Catalog/Model/Product/Indexer/Flat.php(350): Mage_Catalog_Model_Product_Flat_Indexer->reindexAll()
#12 /public_html/app/code/core/Mage/Index/Model/Process.php(212): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#13 /public_html/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#14 /public_html/shell/indexer.php(167): Mage_Index_Model_Process->reindexEverything()
#15 /public_html/shell/indexer.php(215): Mage_Shell_Compiler->run()
#16 {main}

I tried truncating the "catalog_product_flat_1 table" and then reindex but it still didnt work. I had to turn off "Use Flat Catalog Product" because the search box wasnt working. Please help.

  • Hi, please see "How do I format my posts using Markdown or HTML?" to format your question. Commented Mar 3, 2017 at 3:45
  • did you get my last message? for some reason all the comments were removed from this page.
    – Jackel2017
    Commented Mar 3, 2017 at 5:47
  • @rafaelOrtegaBueno did you get my response?
    – Jackel2017
    Commented Mar 3, 2017 at 5:53
  • @rafael-ortega-bueno
    – Jackel2017
    Commented Mar 3, 2017 at 5:53
  • i did notice when i view the relationships under catalog product flat 1, all the fields are blank
    – Jackel2017
    Commented Mar 3, 2017 at 5:55

1 Answer 1


I was able to do a database dump, create a new database then import the dump and it fixed the problem. everything else didnt work. including optimize table function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.