MySQL Limitations on the Flat Catalogue in Magento

We recently came across this problem with a customer who had painstakingly created every attribute possible for all his products. The result, a brilliantly user-friendly store, the downside, flat-catalogue product won't work!

The error you are likely to see in Magento is as vague as Can't initialize indexer process.

A table in MySQL has a limitation of 65535 bytes of overall row length, when you present a multitude of attributes (specifically drop-down/multiple select/text/image), they are allocated a 255 character limit, as per MySQL's varchar. In Magento starting from 1.3 the products catalog in the "flat" mode suffers from this limitation depending on the number and combination of the product attributes that participate in the flat product index.

Depending on what our client wants to do, there will likely be an extension to bypass this fault and allow some intelligence to modify the SQL statement and the resulting flat table to allow more than the ~85 text field/multiple select/image limitation.

A quick workaround (but not necessarily ideal) is to hack (read: extend) some core functions and override the declarations.

In Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer

# ~530
if (!$this->_isFlatTableExists($store)) {
    $sql = "CREATE TABLE {$tableNameQuote} (n";

    foreach ($columns as $field => $fieldProp) {
+      if ($fieldProp['type'] == "varchar(255)")
+        $fieldProp['type'] = "varchar(64)";
      $sql .= sprintf("  %s,n",
          $this->_sqlColunmDefinition($field, $fieldProp));

...

# ~633
  foreach ($addIndexes as $indexName => $indexProp) {
      $sql .= sprintf(' ADD %s,',
          $this->_sqlIndexDefinition($indexName, $indexProp));
  }
  $sql = rtrim($sql, ",");
+  $sql = str_replace("varchar(255)","varchar(64)",$sql);
  $this->_getWriteAdapter()->query($sql);

In the example above, I just changed the 255 to 65 globally for testing purposes, it would be a perfect solution if your attribute values are less than 64 characters, otherwise, you'll need to be clever with the assignment of varchar length and whether to assign text/blob instead.

Read the white paper on the fault SUP-MySQLLimitationsontheFlatCatalog(Product)-29Jul10-0343PM-17

  • Pingback: amitsamtani.com » Blog Archive » Index Manager Error On Magento()

  • Hi,

    I had this same problem on my web site. My problem was setting "Used
    in Product Listing" to yes on way too many attributes. It was unnecessary. The solution that I went with was:

    Take a look at the catalog_eav_attribute and eav_attribute tables. Update the used_in_product_listing field to 0 for all user defined fields that are set to 1. Before running this, use a SELECT clause and see what fields its pulling out. I wrote a quick post on my solution if you'd like to see, http://www.amitsamtani.com/2010/10/18/index-manager-error-on-magento/

    Maybe you can try this on your clients web site.

    Amit

  • Center

    i use magento products for database publishing, it would be great to have all the product data in the flat tables and connect that tables to excel with O.D.B.C or just export with phpmyadmin

    • You could easily do it with WebServices (SOAP/XML) or standalone scripts.

      • center

        do you know any existing script?

        • Existing scripts for what?

          • center

            never mind.

  • Center
  • center

    I used your hack with succes, thank you guys 😀

    Now i can index over 150 attributes for more than 7000 products and pull them into excell with ODBC in seconds.

  • Jelmer Keij

    Hello Benjamin,

    Thank you very much for this script. Fixed it for me! 🙂

    Cheers,
    Jelmer

  • Where I can file that file to edit it?
    Here: app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Flat
    I have indexer.php, but there is only these lines:

    class Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat_Indexer
    extends Mage_Catalog_Model_Resource_Product_Flat_Indexer

    • Nevermind, in 1.6 magento version this file is different...

      • Anybody maybe knows where is needed to change in magento 1.6 version ?

        • Cs

           Same problem, magento in 1.6.2 this file has 3 lines therefore no line 530 or 633 as per article, any idea where the file is located in 1.6.2?

  • Did you try it on magento 1.6?

    I have problem with attributes and unable to index flat catalog.

    ???

    • Julius if you still need some help I have a developer guy who can fix that 🙂

      • Contact me at labas[eta]mulas.lt

  • Daric Johnson

    Any solution for magento 1.6?

    • sonassi

      This isn't an issue with Magento. It is a MySQL limitation.

      • Daric Johnson

        I mean where to find the suggested file in magento 1.6?

  • Thanks, It works for me.

  • Rohan Patil

     Hi

    I have the same error in Magento 1.6 version
    this works still 1.5 is there any solution for 1.6

    Thanks

    • sonassi

      This isn't an issue with Magento. It is a MySQL limitation.

  • please help for 1.6 i need this solution please

  • cs@paragon-sci.com

    I found a solution to this with magento 1.6, similar solution (reduce the varchar field to 64) the file to change is in app/code/core/Mage/Eav/Model/Entity/Abstract.php copy this to your theme and replace 255 in line 677 with 64. Our site has about 200 Attributes and this solution allowed us to index all tables.

  • HT

    Hi guys!

    Can someone, please, tell me, where can I do that adjustment in magento 1.7?

    Thanks,
    HT

  • HTaina

    Hi again,

    I have 167 attributes in total, 51 of them Filterable (Used in Layered Nav).

    It seems that if you switch the attributes to "NO" for "Used in Product Listing", the following error doesn't occur anymore:

    "Exception 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"

    Thanks,
    HTaina

  • garethdaine

    Any progress here on a solution for 1.7, cheers.

  • Hiko

    Magento 1.7 fix:

    app/code/core/Mage/Eav/Model/Entity/Attribute/Abstract.php

    'type' => 'varchar(255)', => 'type' => 'varchar(100)',

    Reindex

  • daric

    you have given wrong path here, It is app/code/core/Mage/Eav/Model/Entity/Attribute/Abstract.php. Thought it will help someone

  • Alexis Huille

    Hey I had the same problem and nothing fixed it. WIth Mage 1.7 , I simply went to the admin/system/configuration/catalog then "frontend", switched Use Flat Catalog Product to "Yes". Reindex worked. Then back there, switched it back to "no". Reindex again, it worked. Hope it helps 😀

    PS : I don't know if it is related, but I did truncate the catalog_product_fat tables, and empty var folder before (it didn't change anything until I did what's up there, tho)

    PS 2 : Problem initially happened after my Reindex timed out.

  • I prefer to solve this issue using standard event/observer approach:
    config.xml:

    SSD_Import_Model_Observer
    catalogProductFlatPrepareColumns

    Observer.php:

    class SSD_Import_Model_Observer
    {
    public function catalogProductFlatPrepareColumns($observer){
    $columns = $observer->getEvent()->getColumns()->getColumns();
    foreach ($columns as $attribute=> $column) {
    if ($column['type'] == "varchar(255)") $column['type'] = "varchar(64)";
    }
    $observer->getEvent()->getColumns()->setColumns($columns);
    }
    }

  • raghuveer

    how we change in database tabel size according to this blog resolve my indexing issue 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

  • dsfdsfsd

    test ddddddddddddddddddddddddddddddddddddddddddddddddddd

  • Tony

    Won't solve, but rather kill the data integrity: such attributes like name, meta_title, meta_description, url_key, image, thumbnail might apparently store larger than 64 character long values. Run:

    select max(char_length(value)), v.attribute_id, a.attribute_code
    from catalog_product_entity_varchar as v
    inner join eav_attribute as a on a.attribute_id = v.attribute_id
    group by attribute_id
    having max(char_length(value)) > 64;

    in order to check what you have in your DB already.

  • JamesAne

    Was he showing all these attributes in the product listing page, layered nav or sort? If not why not just set the below to no and then in won't go into the product_flat right?

    "Use in Layered Navigation", "Used in Product Listing", "Used for Sorting in Product Listing"

    • Snowcore

      Good point!