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
Amit Samtani
19 Oct. 2010
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
29 Apr. 2011
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
Benjamin
29 Apr. 2011
You could easily do it with WebServices (SOAP/XML) or standalone scripts.
center
30 Apr. 2011
do you know any existing script?
Benjamin
2 May. 2011
Existing scripts for what?
center
2 May. 2011
never mind.
Center
1 May. 2011
http://www.youtube.com/watch?v=BKe6EKjE8kk
center
2 May. 2011
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
10 Jun. 2011
Hello Benjamin,
Thank you very much for this script. Fixed it for me!
Cheers,
Jelmer
Tomas
28 Oct. 2011
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
Tomas
28 Oct. 2011
Nevermind, in 1.6 magento version this file is different…
Tomas
28 Oct. 2011
Anybody maybe knows where is needed to change in magento 1.6 version ?
Julius
2 Nov. 2011
Did you try it on magento 1.6?
I have problem with attributes and unable to index flat catalog.
???
Tomas
19 Dec. 2011
Julius if you still need some help I have a developer guy who can fix that
Tomas
19 Dec. 2011
Contact me at labas[eta]mulas.lt