CODEWITHSUNDEEP
mysqlmagento
KarenB
KarenB

Reputation: 27

Magento reindex Category Flat Data not working

Having finally got a local version of our client's Magento site working I've now been able to get a bit closer to fixing their problem. We are unable to reindex the Category Flat Data.

Looking at the logs there's an issue with creating a foreign key when the reindexing takes place, and this wipes out the mage_catalog_category_flat_store_1 table causing the entire site to break.

However, I'm not quite sure why the foreign key can't be created, as the db user has full access rights. The log file contains the following:

2015-02-10T14:38:27+00:00 DEBUG (7): Exception message: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
Trace: #0 Z:\XAMPP\htdocs\sites\Providore\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 Z:\XAMPP\htdocs\sites\Providore\lib\Zend\Db\Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 Z:\XAMPP\htdocs\sites\Providore\lib\Zend\Db\Adapter\Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 Z:\XAMPP\htdocs\sites\Providore\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('CREATE TABLE `m...', Array)
#4 Z:\XAMPP\htdocs\sites\Providore\lib\Varien\Db\Adapter\Pdo\Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('CREATE TABLE `m...', Array)
#5 Z:\XAMPP\htdocs\sites\Providore\lib\Varien\Db\Adapter\Pdo\Mysql.php(2039): Varien_Db_Adapter_Pdo_Mysql->query('CREATE TABLE `m...')
#6 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Catalog\Model\Resource\Category\Flat.php(604): Varien_Db_Adapter_Pdo_Mysql->createTable(Object(Varien_Db_Ddl_Table))
#7 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Catalog\Model\Resource\Category\Flat.php(1418): Mage_Catalog_Model_Resource_Category_Flat->_createTable('1')
#8 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Catalog\Model\Resource\Category\Flat.php(1431): Mage_Catalog_Model_Resource_Category_Flat->_createTables()
#9 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Catalog\Model\Category\Indexer\Flat.php(246): Mage_Catalog_Model_Resource_Category_Flat->reindexAll()
#10 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Index\Model\Process.php(209): Mage_Catalog_Model_Category_Indexer_Flat->reindexAll()
#11 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Index\Model\Process.php(255): Mage_Index_Model_Process->reindexAll()
#12 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Index\controllers\Adminhtml\ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#13 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Core\Controller\Varien\Action.php(419): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#14 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Core\Controller\Varien\Router\Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#15 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Core\Controller\Varien\Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#16 Z:\XAMPP\htdocs\sites\Providore\app\code\core\Mage\Core\Model\App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#17 Z:\XAMPP\htdocs\sites\Providore\app\Mage.php(683): Mage_Core_Model_App->run(Array)
#18 Z:\XAMPP\htdocs\sites\Providore\index.php(90): Mage::run('', 'store')
#19 {main}

Can anyone explain what I need to do to get the reindexing to work? Until we can get this working the categories in the back-end of the site don't match the categories in the nav on the front-end of the site.

Thanks in advance

Upvotes: 0

Views: 2317

Answers (1)

Guerra
Guerra

Reputation: 2790

Disable flat tables:

System > Configuration > Catalog > Frontend 
Disable Use Flat Catalog Category and Use Flat Catalog Product.

Truncate your flat table.

It should work fine now, without flat. Now you can enable flat again, and reindex to build it up.

Upvotes: 2

Related Questions