Magento Product Flat Data and Catalog Search Index issue

Question

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.

Answer 1

I had the same issue a few days ago.

1 - Please make a DB backup

2 - Switch off the Use Flat Product and Category options in Magento and switch the Index mode from the Product Flat Data to Manual update;

3 - Now go into your DB. delete the catalog_product_flat_xx table(s);

4 - You must reindex the catalog via SSH. It should look like this:

usr/local/bin/php public_html/shell/indexer.php --reindex catalog_product_flat

Magento will create new tables for each shop. I could take a while.

Once it done, you can activate the Flat products option and check out whether Magento can do the reindex itself.

Answer 2

Truncate Product Flat Data and then run this script again. Should be foreign key constraint issue.