Index Manager Error On Magento
Posted on October 18th, 2010 in Magento | 5 Comments »
The Problem
After performing an upgrade to version 1.4.1.0 my index management and catalog search stopped working.
The Alert:
“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.”
The Error:
“Cannot initialize the indexer process”
The Screenshot:
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!
Solution
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
…and Fixed!
With a working search…
Another solution
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.
Good Luck!
5 Responses
many thanks – solution worked
and it was a threshold thing!!
This is great content. You’ve loaded this with useful, informative content that any reader can understand. I enjoy reading articles that are so very well-written.
Thanks a lot, you pointed exactly what I was looking for a whole day !
I performed the update and my indexing worked magically.
Do you mind if I quote a few of your posts as long as I provide credit and sources back to your blog? My website is in the very same niche as yours and my users would genuinely benefit from a lot of the information you provide here. Please let me know if this okay with you. Appreciate it!
Go ahead