Product flat table will not reindex - Row size too large

Question

When i try to reindex product flat data,I am getting There was a problem with reindexing process.Database repair didn't help.Exception.log file show this error

2011-08-29T11:54:05+00:00 DEBUG (7): Exception message: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
Trace: #0 /home/sites/www.domain.com/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/sites/www.domain.com/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/sites/www.domain.com/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ca...', Array)
#3 /home/sites/www.domain.com/lib/Varien/Db/Adapter/Pdo/Mysql.php(337): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ca...', Array)
#4 /home/sites/www.domain.com/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Flat/Indexer.php(652): Varien_Db_Adapter_Pdo_Mysql->query('ALTER TABLE `ca...')
#5 /home/sites/www.domain.com/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Flat/Indexer.php(122): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer->prepareFlatTable('1')
#6 /home/sites/www.domain.com/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Flat/Indexer.php(115): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer->rebuild('1')
#7 /home/sites/www.domain.com/app/code/core/Mage/Catalog/Model/Product/Flat/Indexer.php(64): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer->rebuild(NULL)
#8 /home/sites/www.domain.com/app/code/core/Mage/Catalog/Model/Product/Indexer/Flat.php(301): Mage_Catalog_Model_Product_Flat_Indexer->rebuild()
#9 /home/sites/www.domain.com/app/code/core/Mage/Index/Model/Process.php(139): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#10 /home/sites/www.domain.com/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#11 /home/sites/www.domain.com/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#12 /home/sites/www.domain.com/app/code/core/Mage/Core/Controller/Varien/Action.php(420): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#13 /home/sites/www.domain.com/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(253): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#14 /home/sites/www.domain.com/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#15 /home/sites/www.domain.com/app/code/core/Mage/Core/Model/App.php(340): Mage_Core_Controller_Varien_Front->dispatch()
#16 /home/sites/www.domain.com/app/Mage.php(627): Mage_Core_Model_App->run(Array)
#17 /home/sites/www.domain.com/index.php(80): Mage::run('', 'store')
#18 {main}

Any pointers?

Solution 1

You've exceeded the row limits in your product flat data table. Basically 255 * Number of Attributes > 65535 which is the maximum allowable characters per row in MySQL http://dev.mysql.com/doc/refman/5.1/en/column-count-limit.html

This can be fixed by removing any attributes that you do not need to appear in the product flat data table by turning off the following attribute properties: Used in Product Listing and Used for Sorting in Product Listing and then attempting a reindex when you've reduced the character count to below the limit.

If it's absolutely necessary to have everything there, then you need to start limiting some of the attribute storage in product flat data down to less than the 255 char maximum that's automatically assigned. Sonassi tells you how to do this here: http://www.sonassi.com/knowledge-base/magento-knowledge-base/mysql-limitations-on-the-flat-catalogue-in-magento/

Solution 2

I would suggest not using product_flat_data - when the row size is nearing its limit, its performance benefits are negated anyway.

You can find a fairly detailed article on the issue and possible resolutions here http://www.sonassi.com/knowledge-base/magento-kb/mysql-limitations-on-the-flat-catalogue-in-magento/

Comments

[drj201]

Can you provide more reasoning behing this statement? I too have encountered this problem but trimming attributes to comply is not an option - we have thousands of products. What kind of performance hit would result in sticking to the EAV tables? If we stick with EAV how do we prevent attempted reindex of Flat Catalog Product if it will always fail? Could another option be to set the attribute option "display in frontend" to false (hence remove from flat table) but show on the product page anyway via a template change? Any insight greatly appreciated.

[Ben Lessani]

Its going to vary from store to store and depends entirely on the complexity of your catalogue. Testing is the only thing that will reveal if a performance difference exists. But in the majority of instances flat catalogue has a negligible difference on overall performance.

[Salman von Abbas]

Wow.. just 65KB? that's horrible. Postgres supports up to 1.6 TB.

[Fiasco Labs]

Heh, good old 65,535 which is a venerable number I remember from working on RatShack TRS-80 systems and Borland Pascal untold aeons ago. As a derisory song goes, "Why are we waiting, oh why are we waiting, oh why oh why are weee waaiitting!!" and also related to "640k program space should be sufficient, we'll stick all the ROM and BIOS code up there in the 641k to 1024k area".

[spiilmusic]

The link is broken

[Liam Mitchell]

@FiascoLabs Dead link.

[Liam Mitchell]

@BenLessani-Sonassi Dead link, how do we reduce attribute sizes or just make them not even need to show up in the table.

[Ben Lessani]

The link isn't dead, its very much alive.