Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Can’t Reindex Product Flat Data - SQLSTATE[42000] Error
 
VSC_JOHN
Jr. Member
 
Total Posts:  6
Joined:  2011-10-13
 

My Product Flat Data has been stuck for awhile in processing (months, site hasn’t launched yet).  I tried running in manually via SSH today for the first time and it still won’t go.  Cleared all locks before hand, both media and shell folder are set to full 777 permissions.

root@sho [~]# cd /home/vsc/www/magento/shell
root@sho [/home/vsc/www/magento/shell]# php -f indexer.php reindexall
Product Attributes index was rebuilt successfully
Product Prices index was rebuilt successfully
Catalog URL Rewrites index was rebuilt successfully
Product Flat Data index process unknown error
:
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access v                                                              iolation: 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'                                                               in /home/vsc/public_html/magento/lib/Zend/Db/Statement/Pdo.php:228
Stack trace
:
#0 /home/vsc/public_html/magento/lib/Zend/Db/Statement/Pdo.php(228): PDOStatemen                                                              t->execute(Array)
#1 /home/vsc/public_html/magento/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zen                                                              d_Db_Statement_Pdo->_execute(Array)
#2 /home/vsc/public_html/magento/lib/Zend/Db/Statement.php(300): Varien_Db_State                                                              ment_Pdo_Mysql->_execute(Array)
#3 /home/vsc/public_html/magento/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_                                                              Statement->execute(Array)
#4 /home/vsc/public_html/magento/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend                                                              _Db_Adapter_Abstract->query('CREATE TABLE `c...', Array)
#5 /home/vsc/public_html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_                                                              Db_Adapter_Pdo_Abstract->query('CREATE TABLE `c...', Array)
#6 /home/vsc/public_html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(2018): Vari                                                              en_Db_Adapter_Pdo_Mysql->query('CREATE TABLE `c...')
#7 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Resource/Produ                                                              ct/Flat/Indexer.php(692): Varien_Db_Adapter_Pdo_Mysql->createTable(Object(Varien                                                              _Db_Ddl_Table))
#8 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Resource/Produ                                                              ct/Flat/Indexer.php(125): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prep                                                              areFlatTable(1)
#9 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Resource/Produ                                                              ct/Flat/Indexer.php(115): Mage_Catalog_Model_Resource_Product_Flat_Indexer->rebu                                                              ild('1')
#10 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Product/Flat/                                                              Indexer.php(73): Mage_Catalog_Model_Resource_Product_Flat_Indexer->rebuild(NULL)
#11 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Product/Index                                                              er/Flat.php(303): Mage_Catalog_Model_Product_Flat_Indexer->rebuild()
#12 /home/vsc/public_html/magento/app/code/core/Mage/Index/Model/Process.php(166                                                              ): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#13 /home/vsc/public_html/magento/app/code/core/Mage/Index/Model/Process.php(194                                                              ): Mage_Index_Model_Process->reindexAll()
#14 /home/vsc/public_html/magento/shell/indexer.php(158): Mage_Index_Model_Proce                                                              ss->reindexEverything()
#15 /home/vsc/public_html/magento/shell/indexer.php(198): Mage_Shell_Compiler->r                                                              un()
#16 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42000]: Synt                                                              ax 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 colum                                                              ns to TEXT or BLOBs' in /home/vsc/public_html/magento/lib/Zend/Db/Statement/Pdo.                                                              php:234
Stack trace
:
#0 /home/vsc/public_html/magento/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zen                                                              d_Db_Statement_Pdo->_execute(Array)
#1 /home/vsc/public_html/magento/lib/Zend/Db/Statement.php(300): Varien_Db_State                                                              ment_Pdo_Mysql->_execute(Array)
#2 /home/vsc/public_html/magento/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_                                                              Statement->execute(Array)
#3 /home/vsc/public_html/magento/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend                                                              _Db_Adapter_Abstract->query('CREATE TABLE `c...', Array)
#4 /home/vsc/public_html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_                                                              Db_Adapter_Pdo_Abstract->query('CREATE TABLE `c...', Array)
#5 /home/vsc/public_html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(2018): Vari                                                              en_Db_Adapter_Pdo_Mysql->query('CREATE TABLE `c...')
#6 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Resource/Produ                                                              ct/Flat/Indexer.php(692): Varien_Db_Adapter_Pdo_Mysql->createTable(Object(Varien                                                              _Db_Ddl_Table))
#7 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Resource/Produ                                                              ct/Flat/Indexer.php(125): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prep                                                              areFlatTable(1)
#8 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Resource/Produ                                                              ct/Flat/Indexer.php(115): Mage_Catalog_Model_Resource_Product_Flat_Indexer->rebu                                                              ild('1')
#9 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Product/Flat/I                                                              ndexer.php(73): Mage_Catalog_Model_Resource_Product_Flat_Indexer->rebuild(NULL)
#10 /home/vsc/public_html/magento/app/code/core/Mage/Catalog/Model/Product/Index                                                              er/Flat.php(303): Mage_Catalog_Model_Product_Flat_Indexer->rebuild()
#11 /home/vsc/public_html/magento/app/code/core/Mage/Index/Model/Process.php(166                                                              ): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#12 /home/vsc/public_html/magento/app/code/core/Mage/Index/Model/Process.php(194                                                              ): Mage_Index_Model_Process->reindexAll()
#13 /home/vsc/public_html/magento/shell/indexer.php(158): Mage_Index_Model_Proce                                                              ss->reindexEverything()
#14 /home/vsc/public_html/magento/shell/indexer.php(198): Mage_Shell_Compiler->r                                                              un()
#15 {main}
Category Flat Data index was rebuilt successfully
Category Products index was rebuilt successfully
Catalog Search Index index was rebuilt successfully
Stock Status index was rebuilt successfully
Tag Aggregation Data index was rebuilt successfully

Any Ideas?

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1848
Joined:  2009-06-04
 

The telling error is:

Product Flat Data index process unknown error: exception ‘PDOException’ with 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’ in /home/vsc/public_html/magento/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:

You may have so many attributes that you’ve exceeded at 255 per field (column), the 65535 maximum row capacity. MySQL Column Count Limit

One of the things that can be done is to shrink the column width as per this page: Sonassi MySQL Limitations on the Flat Catalog in Magento

The other is to check for this misconfiguration and adjust appropriately: Amitsamtani Index Manager error on Magento Not all attributes need to be enabled to show in the product listing, keep it to only what you really need. The fix is simple, go into attribute management and set Used in Product Listing = No for every attribute that doesn’t need to be used in the product listing.

 
Magento Community Magento Community
Magento Community
Magento Community
 
VSC_JOHN
Jr. Member
 
Total Posts:  6
Joined:  2011-10-13
 

THANK YOU!  THANK YOU!  THANK YOU!

You solved the issue I was having.  95% of the attributes that we had loaded in were set to be used in the product listing.  This wasn’t an issue until we did a mass import from a content provider that gave us over 1k+ attributes.

A weight has been loaded off my shoulders now smile

Thanks Again!

 
Magento Community Magento Community
Magento Community
Magento Community
 
marcinsdance
Jr. Member
 
Avatar
Total Posts:  1
Joined:  2012-05-27
 
chiefair - 15 May 2012 11:30 AM

Not all attributes need to be enabled to show in the product listing, keep it to only what you really need. The fix is simple, go into attribute management and set Used in Product Listing = No for every attribute that doesn’t need to be used in the product listing.

You’re a staaaaaaaaaaaaar!! Thank you chiefair - this did the job wink

 
Magento Community Magento Community
Magento Community
Magento Community
 
Mirasvit
Guru
 
Avatar
Total Posts:  639
Joined:  2009-08-22
 

Indeed when a large number of goods or attributes need to be saved, backend works very slow, as each time you save data the system re-indexes the goods through the entire catalog.
Asynchronous Re-indexing is a solution of this problem.  When a product or category is saved it is not immediately re-indexed, but put into a queue. The queue is re-indexed in the background. This greatly speeds up the backend. This mechanism is implemented by using an extension of http://mirasvit.com/magento-extensions/magento-asynchronous-reindex.html

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top