1

I am trying to rebuild Indexes but it takes forever and does not complete the process. It gets stuck at Product Flat Data with a message PROCESSING with no other error log or message. I have tried cleaning up log and other DB tables as maintenance and optimization but did not help at all. We are on VPS with over 30000 SKUs. Please kindly look into below screenshot and do let me know if there are other options to fix this issue. I have already tried all the suggested option on various Forums.

Index

Command: php indexer.php --status

[thestati@server shell]$ php indexer.php --status
Product Attributes:            Pending
Product Prices:                Pending
Catalog URL Rewrites:          Running
Product Flat Data:             Running
Category Flat Data:            Require Reindex
Category Products:             Require Reindex
Catalog Search Index:          Require Reindex
Stock Status:                  Pending
Tag Aggregation Data:          Require Reindex
Default Values:                Pending

Command: php indexer.php --reindex catalog_category_flat [thestati@server shell]$ php indexer.php --reindex catalog_category_flat I am getting following error:

PS: There is no table called catalog_category_flat_store_1 in the DB though I have catalog_category_flat_store_2 and catalog_category_flat_store_3.

SET FOREIGN_KEY_CHECKS=0;# MySQL returned an empty result set (i.e. zero rows). TRUNCATE TABLE catalog_product_flat_1;# MySQL returned an empty result set (i.e. zero rows). SET FOREIGN_KEY_CHECKS=1;# MySQL returned an empty result set (i.e. zero rows).

Category Flat Data index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1005 Can't create table 'thestati_magento.catalog_category_flat_store_1' (errno: -1)' in /home/thestati/public_html/catalog/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/thestati/public_html/catalog/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/thestati/public_html/catalog/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/thestati/public_html/catalog/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/thestati/public_html/catalog/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/thestati/public_html/catalog/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('CREATE TABLE `c...', Array)
#5 /home/thestati/public_html/catalog/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('CREATE TABLE `c...', Array)
#6 /home/thestati/public_html/catalog/lib/Varien/Db/Adapter/Pdo/Mysql.php(2039): Varien_Db_Adapter_Pdo_Mysql->query('CREATE TABLE `c...')
#7 /home/thestati/public_html/catalog/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(604): Varien_Db_Adapter_Pdo_Mysql->createTable(Object(Varien_Db_Ddl_Table))
#8 /home/thestati/public_html/catalog/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1418): Mage_Catalog_Model_Resource_Category_Flat->_createTable('1')
#9 /home/thestati/public_html/catalog/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1431): Mage_Catalog_Model_Resource_Category_Flat->_createTables()
#10 /home/thestati/public_html/catalog/app/code/core/Mage/Catalog/Model/Category/Indexer/Flat.php(246): Mage_Catalog_Model_Resource_Category_Flat->reindexAll()
#11 /home/thestati/public_html/catalog/app/code/core/Mage/Index/Model/Process.php(209): Mage_Catalog_Model_Category_Indexer_Flat->reindexAll()
#12 /home/thestati/public_html/catalog/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#13 /home/thestati/public_html/catalog/shell/indexer.php(159): Mage_Index_Model_Process->reindexEverything()
#14 /home/thestati/public_html/catalog/shell/indexer.php(199): Mage_Shell_Compiler->run()
#15 {main}
1
  • I've updated my answer, the command was not found due to a typo as Paul has pointed out. Also there is a lot of great information on the link I provided. – SR_Magento Apr 13 '14 at 12:50
0

Try indexing from the command line / ssh. Your VPS should have that functionality.

Execute the following from your /magento_installation/shell

Verifying the status if the indexes:

php indexer.php --status

To reindex the problem index, in your case the Product Flat Data

php indexer.php --reindex catalog_product_flat

Additional information: How to process Magento indexes from the command line

If an error presents itself at that time, there may be a problem with your mysql configuration or with the database itself. Feel free to post the relevant information.

UPDATE BASED ON NEW INFORMATION

I ran into this exact problem last year. You need to create a backup of your database and access via phpmyadmin.

I ended up dropping the table(s) (or you can TRUNCATE them).

catalog_product_flat_1
catalog_product_flat_2 

Then use the indexer.php to reindex.

You can reach more about this here

5
  • [thestati@server shell]$ php indexer.pgp --reindex catalog_product_flat Could not open input file: indexer.pgp – Kevin S Apr 13 '14 at 12:29
  • I have updated my question with the answer. Please kindly look into it. Thanks – Kevin S Apr 13 '14 at 12:31
  • indexer.php, instead of .pgp – Paul Hachmang Apr 13 '14 at 12:42
  • @sr_magento I have updated my question qith the error that I got while trying to reindex catalog_category_flat – Kevin S Apr 13 '14 at 13:10
  • @sr_magento I don't have a table called catalog_product_flat_1 – Kevin S Apr 13 '14 at 14:04

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.