Magento – Magento Product Flat Data and Catalog Search Index issue

indexingmagento-1.6

We are running Magento store having 8k products. The tow indexing is not working. Throwing some errors.

Product Flat Data Error

Product Flat Data index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1005 Can't create table './bofc_magento/#sql-f22_6554c.frm' (errno: 121)' in /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ca...', Array)
#5 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ca...', Array)
#6 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(327): Varien_Db_Adapter_Pdo_Mysql->query('ALTER TABLE `ca...')
#7 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(2548): Varien_Db_Adapter_Pdo_Mysql->raw_query('ALTER TABLE `ca...')
#8 /var/www/vhosts/beadsofcambay.com/httpdocs/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 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(1390): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prepareFlatTable(1)
#10 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/Catalog/Model/Product/Flat/Indexer.php(296): Mage_Catalog_Model_Resource_Product_Flat_Indexer->reindexAll()
#11 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/Catalog/Model/Product/Indexer/Flat.php(336): Mage_Catalog_Model_Product_Flat_Indexer->reindexAll()
#12 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/Index/Model/Process.php(207): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#13 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#14 /var/www/vhosts/beadsofcambay.com/httpdocs/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#15 /var/www/vhosts/beadsofcambay.com/httpdocs/shell/indexer.php(198): Mage_Shell_Compiler->run()
#16 {main}

Catalog Search Index

Catalog Search Index index process unknown error:
exception 'Zend_Cache_Exception' with message 'Could not determine temp directory, please specify a cache_dir manually' in /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Cache.php:209
Stack trace:
#0 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Cache/Backend.php(197): Zend_Cache::throwException('Could not deter...')
#1 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Cache/Backend/File.php(123): Zend_Cache_Backend->getTmpDir()
#2 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Cache.php(153): Zend_Cache_Backend_File->__construct(Array)
#3 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Cache.php(94): Zend_Cache::_makeBackend('File', Array, false, false)
#4 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Locale/Data.php(940): Zend_Cache::factory('Core', 'File', Array, Array)
#5 /var/www/vhosts/beadsofcambay.com/httpdocs/lib/Zend/Locale.php(668): Zend_Locale_Data::getContent('en_US', 'date', NULL)
#6 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(763): Zend_Locale::getTranslation(NULL, 'date', Object(Zend_Locale))
#7 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(699): Mage_CatalogSearch_Model_Resource_Fulltext->_getStoreDate(1, NULL)
#8 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(634): Mage_CatalogSearch_Model_Resource_Fulltext->_getAttributeValue(145, NULL, 1)
#9 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(230): Mage_CatalogSearch_Model_Resource_Fulltext->_prepareProductIndex(Array, Array, 1)
#10 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(128): Mage_CatalogSearch_Model_Resource_Fulltext->_rebuildStoreIndex(1, NULL)
#11 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(83): Mage_CatalogSearch_Model_Resource_Fulltext->rebuildIndex(NULL, NULL)
#12 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/CatalogSearch/Model/Indexer/Fulltext.php(446): Mage_CatalogSearch_Model_Fulltext->rebuildIndex()
#13 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/Index/Model/Process.php(207): Mage_CatalogSearch_Model_Indexer_Fulltext->reindexAll()
#14 /var/www/vhosts/beadsofcambay.com/httpdocs/app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#15 /var/www/vhosts/beadsofcambay.com/httpdocs/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#16 /var/www/vhosts/beadsofcambay.com/httpdocs/shell/indexer.php(198): Mage_Shell_Compiler->run()
#17 {main}

Both indexes are run from command line. I Googled both errors but did not find any suitable solutions.

Best Answer

Your error explains it.

You are out of disk space and your permissions are wrong.

Check your disk usage on your DB server with df -h and fix your permissions with this

Related Solutions

Magento – Catalog Flat Category reindex issue

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.

Magento – Catalog Search Index Not Changing Status ‘Required Index’ Fix

Check which events set the indexes to invalid

Magento docs show the events for every index (see Events that Trigger Reindexing):

Further you can add some debug code in Mage_Index_Model_Resource_Process::updateStatus()

if ($status === Mage_Index_Model_Process::STATUS_REQUIRE_REINDEX) {
    Mage::log(sprintf('Indexer %s was invalidated.', $process->getIndexer()->getName()), null, 'invalid_index.log', true);
    foreach (debug_backtrace() as $db) {
        Mage::log(sprintf('%s::%s', $db['class'], $db['function']), null, 'invalid_index.log', true);
    }
}

This should show you a stack trace about what is setting the indexes to invalid. In my case it were API requests of 'catalogProductUpdate'

You can also check the exception.log if you'll find any locks of the 'index_process' table. This tables holds the data you see in System > Index Management

Creds for the debug code go here

Best Answer

Related Solutions

Magento – Catalog Flat Category reindex issue

Magento – Catalog Search Index Not Changing Status ‘Required Index’ Fix

Related Topic