After performing an upgrade to version 22.214.171.124 my index management and catalog search stopped working.
“One or more of the Indexes are not up to date: Catalog Search Index. Click here to go to Index Management and rebuild required indexes.”
“Cannot initialize the indexer process”
Whats going on?
A table in MySQL has a limitation of 65535 bytes of overall row length. It may severely limit the number of columns in a table when it has varchar/char columns. 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. Source: SUP-MySQLLimitationsontheFlatCatalogProduct-29Jul10-0343PM-17
The workaround that stood out was setting “Used in Product Listing” = No:
Apparently, when I originally set up the store I was under the impression that this field always has to be set to Yes. Of course I want this attribute to be used in my product listing. Whats the point of creating an attribute that will not be used in the product listing, right? Well, no. This field controls if the attribute will be used in the “grid” or “list” view when showing multiple products per page. In my opinion you only need certain fields set to yes for those views, and its already preset when you set up magento for the first time. Fields like, price, special price, name and short description. Not EVERY attribute!
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. Also, run on a test environment first!!
update `catalog_eav_attribute` as cea left join eav_attribute as ea on cea.attribute_id = ea.attribute_id set cea.used_in_product_listing = 0 where cea.used_in_product_listing = 1 and is_user_defined = 1
With a working search…
I was trying to find the thread where I found the original PDF to give credit to the person who led me in this direction and ended up stumbling across another solution: http://www.sonassi.com/knowledge-base/magento-knowledge-base/mysql-limitations-on-the-flat-catalogue-in-magento/. If my solution does not help, maybe Sonassis solution will.