0

I have a problem when I try to reindex product flat data, the reindex of product_flat_data doesn't work. Message says "There was a problem with reindexing process." and I have tried various solutions including:

  • deleting var/locks files
  • truncating table catalog_product_flat_1
  • dropping catalog_product_flat_1
  • using ssh to reindex ( php shell/indexer.php --reindexall )

The problem still occurs. I don't know what to do. Any help would be great.


Edit

Here is the error message in exception.log

2014-04-04T13:48:48+00:00 DEBUG (7): Exception message: SQLSTATE[HY000]: General error: 1005 Can't create table 'mywebsitehere.#sql-34_10bf' (errno: 121)
Trace: #0 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ca...', Array)
#5 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ca...', Array)
#6 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(340): Varien_Db_Adapter_Pdo_Mysql->query('ALTER TABLE `ca...')
#7 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(2569): Varien_Db_Adapter_Pdo_Mysql->raw_query('ALTER TABLE `ca...')
#8 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(816): Varien_Db_Adapter_Pdo_Mysql->addForeignKey('FK_CAT_PRD_FLAT...', 'catalog_product...', 'entity_id', 'catalog_product...', 'entity_id', 'CASCADE', 'CASCADE')
#9 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(1390): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prepareFlatTable(1)
#10 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Catalog/Model/Product/Flat/Indexer.php(296): Mage_Catalog_Model_Resource_Product_Flat_Indexer->reindexAll()
#11 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Catalog/Model/Product/Indexer/Flat.php(336): Mage_Catalog_Model_Product_Flat_Indexer->reindexAll()
#12 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Index/Model/Process.php(209): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#13 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#14 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#15 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#16 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#17 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#18 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#19 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/app/Mage.php(683): Mage_Core_Model_App->run(Array)
#20 /srv/data/web/vhosts/www.mywebsitehere.com/htdocs/index.php(90): Mage::run('', 'store')
#21 {main}
  • 3
    can you post the contents of var/log/system.log and var/log/exception.log (if exists) – Evgeni Ivanov Apr 4 '14 at 13:59
  • 1
    Any error message if you run indexer via ssh? – user487772 Apr 4 '14 at 14:14
  • I edited my question with the error message inside exception. I get the same message in the ssh shell when trying to reindex. – mlclm Apr 5 '14 at 8:49
1

Magento should in theory log the exception thrown by this, see app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php line 133.

You could try dumping the exception straight to output if you wanted.

  • I edited my question with the message in exception.log – mlclm Apr 5 '14 at 8:50
  • As Melvyn suggests - this could be a disk space issue. It could also be a user permissions issue (your MySQL user). Ensure that the MySQL user has all the permissions it requires to make a table on your specified database. – Douglas Radburn Apr 7 '14 at 13:53
  • Thanks for your tips Douglas. I managed to finally make it work by deleting var/locks files again, deleting the table catalog_product_flat_1 again... Maybe not in that order. Hit the "reindex" button from my admin panel and the reindex worked. I have no clue why it worked normally. – mlclm Apr 13 '14 at 8:56
0

Clean out /tmp/, set tmp_dir in MySQL server configuration to something with plenty of space, restart MySQL server and try again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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