0

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

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).

0

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.

  • Thanks @sohel Rana but this solution is not working in my case because I have more then 400 columns in my table. I have found solution regarding tuning mysql configuration rather then change in files. – Arshad Muhammad Apr 16 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.