Reindex error: Row size too large - Paul Meștereagă
Programming

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…

Paul Mestereaga

October 18, 2016

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.

Is your Magento slow?

Wondered how you can make your Magento load faster? Wonder no more. Here is a FREE step by step guide that helps you.

Related Posts

How to create a product attribute using Data Patches in Magento 2.3

26 Mar 2020

|

Paul Mestereaga

This is a short tutorial in which we will learn to create a product attribute using Data Patches in Magento 2.3

Declarative schema in Magento 2.3+

16 Mar 2020

|

Paul Mestereaga

In this tutorial we will learn the how to configure a declarative schema in Magento 2.3, perform database operations using declarative schema, create a schema whitelist and create data and schema patches.

FREE!

Magento 2
Optimization guide

Wondered how you can make your Magento load faster? Wonder no more. Here is a step by step guide that helps you.