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

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