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!
Periodically I get an email from my hosting provider alerting me that my inode usage exceeds their resource abuse policy. Since I’m using magento on the server they recommend clearing out the contents of downloader/pearlib/cache/* and downloader/pearlib/download/* and deleting all empty .svn folders. My inode usage was still a little higher than necessary after completing these steps.
First of all, what is an inode? An inode stores basic information about a regular file, directory, or other file system object. So in a nutshell, any file, directory or file system object counts toward a point against your inode usage. Messing around I decided to clear all my caches (css, js and images). The image cache really penalized me. By clearing out the image cache my inode usage dropped from 56K to about 40K. Now, this was obviously a temporary fix as my cache is going to be rebuilt as visits come through.
One thing I did realize though was my media folder had high inode usage. Apparently when I was doing my initial product imports using the importing tools I kept on creating image files that were not really used on the site. Why keep those files around? I wrote a little script to clear those images out. Right now my inode usage has dropped to 26K and I don’t have to worry about getting suspended by my hosting provider for a little bit more time.
Before I post the script, a couple things… Test it out in a test environment before running it in production. Make a back up of your site and your image folder in case you miss something and go off deleting a bunch of random files. This is a simple script I put together to remove any unused product images in the site. I threw it up very quickly, I know it could be cleaned up quite a bit with added features and a lot of good blah blah, but I needed to bring my inode usage down quick and this script helped me clear a lot of unused images. Don’t track me down if something bad happens to your site. BACKUP! TEST! You have been warned!
Put the following two files in the /media/catalog/product/ folder:
1) Export contents of the catalog_product_entity_media_gallery table with only the value field:
2) Run the following code in the product folder. Before running the commented out code with the rm statements, see what its doing and test properly! This script will drill down into every subdirectory within the product directory looking for images and then will compare against the export file to see if the image is being used or not. If the image is not being used, the image will be deleted.
<?php
function removeUnusedImages($images,$dir) {
$prefix = $dir . '/';
if($handle = opendir($dir)):
while (false !== ($file = readdir($handle))):
if ($file === '.' || $file === '..' || $file === 'placeholder' || $file === 'cache') continue;
$file = $prefix . $file;
if (is_dir($file)):
removeUnusedImages($images,$file);
else:
$file = substr($file,1);
if(!in_array($file,$images)):
echo "Remove: {$file} \n";
//system('ls ' . substr($file,1));
//system('rm ' . substr($file,1));
else:
echo "Dont Remove: {$file} \n";
endif;
endif;
endwhile;
closedir($handle);
endif;
}
$images = file('export.txt',FILE_IGNORE_NEW_LINES);
removeUnusedImages($images,'.');
?>Good luck!
