magento product flat tables row size too large

Question

When I re-index data in magento 1.x with large attribute set about 200 attributes. The got me followin MySQL error.

Product Flat Data index process unknown error: exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB may he lp. In current row format, BLOB prefix of 0 bytes is stored inline.' in /var/www/html/uat/lib/Zend/Db/Statement/Pdo.php:228

Answer 1

Looks like you're hitting a max limitation for the number of columns in a database table as described here: https://www.percona.com/blog/2011/04/07/innodb-row-size-limitation/

There's an article here: https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large and here: https://serverfault.com/questions/326836/issue-with-maximum-row-size-in-mysql/326870#326870 regarding this. It's not a Magento issue per se and needs to be tackled at MySQL application level (or you need to reduce the number of attributes you're storing in the flat table).

Answer 2

Overwrite following class:

app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php

add following code inside

public function prepareFlatTable($storeId)

foreach ($columns as $fieldName => $fieldProp) {
    if (!isset($fieldProp['length'])) {
        $columnLength = null;
    } else {
        if ($fieldProp['type'] == 'text' && $fieldProp['length'] == 255) {
            $columnLength = 140;
        } else {
            $columnLength = $fieldProp['length'];
        }
    }

    $columns[$fieldName] = array_merge($columns[$fieldName], ['length' => $columnLength]);
}

After

if (Mage::helper('core')->useDbCompatibleMode()) {
         /* Convert old format of flat columns to new MMDB format that uses DDL types and definitions */
        foreach ($columns as $key => $column) {
            $columns[$key] = Mage::getResourceHelper('core')->convertOldColumnDefinition($column);
        }
    }

So your code should look like:

// Extract columns we need to have in flat table
$columns = $this->getFlatColumns();
if (Mage::helper('core')->useDbCompatibleMode()) {
     /* Convert old format of flat columns to new MMDB format that uses DDL types and definitions */
    foreach ($columns as $key => $column) {
        $columns[$key] = Mage::getResourceHelper('core')->convertOldColumnDefinition($column);
    }
}

foreach ($columns as $fieldName => $fieldProp) {
    if (!isset($fieldProp['length'])) {
        $columnLength = null;
    } else {
        if ($fieldProp['type'] == 'text' && $fieldProp['length'] == 255) {
            $columnLength = 140;
        } else {
            $columnLength = $fieldProp['length'];
        }
    }

    $columns[$fieldName] = array_merge($columns[$fieldName], ['length' => $columnLength]);
}

You can reduce column length this way.