Ecommerce shops with a large number of products and attributes per product have to reindex their products by way of a Linux command line.
To that end, the following requirements have to be met:
- first and foremost, one must have SSH access
- after logging into the SSH through Putty, the website’s root has to be identified
- once in the root the following command, execute the following command:
php shell/indexer.php reindexall
Since the database is above average in size and the number of attributes is very large, there’s a possibility that once the reindexing process reaches the Product Flat Data (catalog_product_flat) section, the following error may appear:
Product Flat Data index process unknown error: exception 'PDOException' with message 'SQLSTATE: Syntax error or access violation: 1118 Row size too large.
The maximum row size for the used table type, not counting BLOBs, is 65535.
You have to change some columns to TEXT or BLOBs' in ../yourdomain.com/public_html/lib/Zend/Db/Statement/Pdo.php:228
This happens because “varchar” type fields have a maximum size of 255 characters.
So, how do we solve the problem?
1. Edit app/code/core/Mage/Eav/Model/Entity/Attribute/Abstract.php, decreasing the value of the length element on line 604 (Magento 188.8.131.52) from 255 characters to 64 in the following array:
$columns[$this->getAttributeCode()] = array( 'type' => Varien_Db_Ddl_Table::TYPE_TEXT, 'length' => '64', 'unsigned' => false, 'nullable' => true, 'default' => null, 'extra' => null );
The best way to go about it is to override the Abstract.php class locally, otherwise, in the event of an upgrade, your changes will disappear and you’ll be back at square one.
2. Decrease the value of the type element on line 677 (Magento 184.108.40.206) from 255 characters to 64 in the following array:
$columns[$this->getAttributeCode()] = array( 'type' => 'varchar(64)', 'unsigned' => false, 'is_null' => true, 'default' => null, 'extra' => null );
If the compiler is active, after you’ve saved the file, you’ll have to run it again, following the Backend -> System -> Tools -> Compilation -> Run Compilation Process path.
3. Refresh Magento’s cache.
Backend -> System -> Cache Management -> Select All items -> Refresh
Once you’ve completed all these steps you can safely resume the reindexing process.