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.

Related Posts

Extend Magento 2 jQuery Widget

18 Jun 2019

|

Paul Mestereaga

In this short tutorial we will learn how to extend Magento 2 jQuery widget with our custom logic.

Console Commands in Magento 2

21 May 2019

|

Paul Mestereaga

In this tutorial I will give an overview of the console commands in Magento 2 and we will learn how to build a custom console command in Magento 2

Optimize Magento 2

Free guide to help you optimise your Magento 2 installation and improve your website speed

Get your free optimisation guide.

Enter your name and email to join my mailing list in exchange for the free download. You can opt out any time.


Can I email you?

I will use the information you provide on this form to stay in touch, send you updates, and send marketing emails about our products and services. You can unsubscribe at any time through a link in the footer of an email from me. For more information, please read the Privacy Policy.