0

I'm trying to manually reindex my categories via SSH since it doesn't work in the Magento Admin panel.

Here's what I've done and checked so far from suggestions I found on the net:

• Cleared tmp folder
• Cleared var/locks folder
• Deleted catalog_category_flat entry in db (there was no catalog_category_flat_1)
• Server isn't full - there's 2gb of hosting space left
• User has all access rights for the database
• Used ssh to reindex (php shell/indexer.php --reindex catalog_category_flat)

When I try to reindex via ssh, this is the error I get:

/home/mysitehere/public_html/shell$ php indexer.php --reindex catalog_category_flat
Category Flat Data index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1005 Can't create table 'db_name.catalog_category_flat_store_1' (errno: 150)' in /home/mysitehere/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/mysitehere/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/mysitehere/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/mysitehere/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/mysitehere/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/mysitehere/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('CREATE TABLE `c...', Array)
#5 /home/mysitehere/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('CREATE TABLE `c...', Array)
#6 /home/mysitehere/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(2039): Varien_Db_Adapter_Pdo_Mysql->query('CREATE TABLE `c...')
#7 /home/mysitehere/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(604): Varien_Db_Adapter_Pdo_Mysql->createTable(Object(Varien_Db_Ddl_Table))
#8 /home/mysitehere/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1418): Mage_Catalog_Model_Resource_Category_Flat->_createTable('1')
#9 /home/mysitehere/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1431): Mage_Catalog_Model_Resource_Category_Flat->_createTables()
#10 /home/mysitehere/public_html/app/code/core/Mage/Catalog/Model/Category/Indexer/Flat.php(246): Mage_Catalog_Model_Resource_Category_Flat->reindexAll()
#11 /home/mysitehere/public_html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Catalog_Model_Category_Indexer_Flat->reindexAll()
#12 /home/mysitehere/public_html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#13 /home/mysitehere/public_html/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#14 /home/mysitehere/public_html/shell/indexer.php(198): Mage_Shell_Compiler->run()
#15 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 1005 Can't create table 'db_name.catalog_category_flat_store_1' (errno: 150)' in /home/mysitehere/public_html/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /home/mysitehere/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/mysitehere/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/mysitehere/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/mysitehere/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('CREATE TABLE `c...', Array)
#4 /home/mysitehere/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('CREATE TABLE `c...', Array)
#5 /home/mysitehere/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(2039): Varien_Db_Adapter_Pdo_Mysql->query('CREATE TABLE `c...')
#6 /home/mysitehere/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(604): Varien_Db_Adapter_Pdo_Mysql->createTable(Object(Varien_Db_Ddl_Table))
#7 /home/mysitehere/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1418): Mage_Catalog_Model_Resource_Category_Flat->_createTable('1')
#8 /home/mysitehere/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1431): Mage_Catalog_Model_Resource_Category_Flat->_createTables()
#9 /home/mysitehere/public_html/app/code/core/Mage/Catalog/Model/Category/Indexer/Flat.php(246): Mage_Catalog_Model_Resource_Category_Flat->reindexAll()
#10 /home/mysitehere/public_html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Catalog_Model_Category_Indexer_Flat->reindexAll()
#11 /home/mysitehere/public_html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#12 /home/mysitehere/public_html/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#13 /home/mysitehere/public_html/shell/indexer.php(198): Mage_Shell_Compiler->run()
#14 {main}

Any help will be greatly appreciated. I've been stuck on this for a few days now.

Server details:

Apache version 2.2.26
PHP version 5.3.27
MySQL version 5.5.36-cll

2

Problem solved.

The tables were MyISAM instead of InnoDB.

http://www.fortwaynewebdevelopment.com/magento-general-error-1005-cant-create-table-fixworkaround/

0
0

The table is not created because that error 150 MySQL occurs:

http://forums.mysql.com/read.php?20,398982,399084#msg-399084

I dumped the foreign constraint key on this table from a Magento database:

ALTER TABLE `catalog_product_flat_1`
   ADD CONSTRAINT `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY 
       (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Both columns entity_id in both tables should be the same: https://stackoverflow.com/questions/1085001/mysql-creating-table-with-fk-error-errno-150 . You either modified column entity_id on table catalog_product_entity or Magento doesn't generate the table using DDL statements correctly.

1
  • Hi, I followed your instructions and got the following error in phpmyadmin: "Can't create table 'testwebsite_db.#sql-68f_29076b' (errno: 121)". Any ideas?
    – zekia
    Mar 10 '15 at 10:43

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.