Reindex error: Row size too large

It happened to me that I wanted to reindex the product flat data and I got a PDOException, you can see the whole reindex error message bellow. I tried to find a solution and here are my findings. If you encountered the same problem now you know how to solve it.

1. The 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 /lib/Zend/Db/Statement/Pdo.php:228

2. The Cause

What is happening here is that we exceeded the row limit in the catalog_product_flat table. As stated in the exception message and here, the maximum row size is 65535. If we multiply the number of attributes by 255, which is the VARCHAR size, we need to get a value lower than 65535.

2. The Solution

There are two possible solutions. First check and see if you really need to have in the front-end all the attributes that you currently store in the flat table. If not then go and uncheck “Use in Layered Navigation”, “Used in Product Listing” and “Used for Sorting in Product Listing” for those attributes. Try to reindex again.

If that didn’t worked out, as it didn’t for me either, the things get a bit more complicated. We need to change the 255 default value for some attributes to decrease the row size.

A quick fix, but not really ideal, is to create a local copy of the indexer class and make the following updates:

// app/code/local/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php
 
public function prepareFlatTable($storeId)
{
 
...
 
// Create table or modify existing one
if (!$this->_isFlatTableExists($storeId)) {
   /** @var $table Varien_Db_Ddl_Table */
   $table = $adapter->newTable($tableName);
   foreach ($columns as $fieldName => $fieldProp) {
+       $skip = array('url_key','url_path','thumbnail','thumbnail_label','small_image','small_image_label','name','image_label');
+       if (!in_array($fieldName, $skip)) {
+           if ($fieldProp['length'] == "255") {
+               $fieldProp['length'] = "64";
+           }
+       } 
 
...
 
// Sort columns into added/altered/dropped lists
$describe   = $adapter->describeTable($tableName);
$addColumns     = array_diff_key($columns, $describe);
$dropColumns    = array_diff_key($describe, $columns);
$modifyColumns  = array();
foreach ($columns as $field => $fieldProp) {
+    $skip = array('url_key','url_path','thumbnail','thumbnail_label','small_image','small_image_label','name','image_label');
+    if (!in_array($field, $skip)) {
+        if ($fieldProp['length'] == "255") {
+            $fieldProp['length'] = "64";
+        }
+    }
 
...

The lines marked with + are the added ones. What this code does, it changes the default 255 value to 64. You can see also that I chose to exclude some attributes to not loose data integrity, because some of the attributes are larger than 64. I listed them in the $skip array, where you can put yours too.

Go ahead and reindex now. It will definitely work.