Magento – Catalog Flat Category reindex issue

catalogindexing

I am using magento 1.7. I came up with the following issue 'cannot create table column without comments'.I have attached the log

2013-07-26T08:33:24+00:00 DEBUG (7): Exception message: Cannot create table without columns comments
Trace: #0 D:\xampp\htdocs\munch4me\app\code\core
\Mage\Catalog\Model\Resource\Category\Flat.php(604): Varien_Db_Adapter_Pdo_Mysql->         createTable(Object(Varien_Db_Ddl_Table))
#1 app\code\core\Mage\Catalog\Model\Resource\Category\Flat.php(1418): Mage_Catalog_Model_Resource_Category_Flat->_createTable('1')
#2 app\code\core\Mage\Catalog\Model\Resource\Category\Flat.php(1431): Mage_Catalog_Model_Resource_Category_Flat->_createTables()
#3 app\code\core\Mage\Catalog\Model\Category\Indexer\Flat.php(246): Mage_Catalog_Model_Resource_Category_Flat->reindexAll()
#4 app\code\core\Mage\Index\Model\Process.php(209): Mage_Catalog_Model_Category_Indexer_Flat->reindexAll()
#5 app\code\core\Mage\Index\Model\Process.php(255): Mage_Index_Model_Process->reindexAll()
#6 app\code\core\Mage\Index\controllers\Adminhtml\ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#7 app\code\core\Mage\Core\Controller\Varien\Action.php(419):    Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#8 app\code\core\Mage\Core\Controller\Varien\Router\Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#9 app\code\core\Mage\Core\Controller\Varien\Front.php(176):  Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#10 app\code\core\Mage\Core\Model\App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#11 app\Mage.php(683): Mage_Core_Model_App->run(Array)
#12 index.php(87): Mage::run('', 'store')
#13 {main}

Best Answer

General info
In the DDL creation of tables Magento does not allow (for some reason still unknown to me) the creation of columns without a comment.
Specific info
The flat category tables are creating by adding one column for each column in the catalog_category_entity table and one column for each category attribute.
All columns from catalog_category_entity are retrieved by this method Mage_Catalog_Model_Resource_Category_Flat::_getStaticColumns but I doubt the problem is in there, because, when retrieving these columns, the comment for the column is the same as the column name. ('comment' => $column['COLUMN_NAME'])
So most probably the issue is with one of the attributes.
The attribute columns are generated in Mage_Catalog_Model_Resource_Category_Flat::_getEavColumns() and the comment for the column is the frontend_label of the attribute.
In conclusion most probably you have (at least) one category attribute that has a null value for the frontend label.
You can identify the attribute(s) by running this query on your database:

SELECT e.* 
FROM  
    eav_attribute e
LEFT JOIN  eav_entity_type et 
   ON e.entity_type_id = et.entity_type_id
WHERE 
   et.entity_type_code =  'catalog_category' AND 
   e.frontend_label IS NULL

If you find one, just add a frontend label (or make it an empty string) and it should work. The only constraint is that it shouldn't be NULL.

[EDIT]
You can debug this issue but adding this piece of code: echo "

"; print_r($columnEntry);exit; 
inside the method Varien_Db_Adapter_Pdo_Mysql::createTable() right before the exception is thrown.


if (empty($columnEntry['COMMENT'])) {
    echo "<pre>"; print_r($columnEntry);exit; //add this heare
    throw new Zend_Db_Exception("Cannot create table without columns comments");
}

Now, instead of the exception you should see how the $columnEntry looks like and get an idea on what is the problematic attribute.

Related Topic