Magento Reindexer

28

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[42000]: 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 1.7.0.2) 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 1.7.0.2) 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.

  • http://www.magentogems.com/ Zahid Mughal

    Hi I have an issue regarding index management of Magento 1.5.0.1. When i do index management all index suceefully done instaed “Catalog_product_Flat” and “Catalog_Category_flat”. I have try to remove the tables of catalog_product_flat_1 etc but no issue resolve…

    Next exception ‘Zend_Db_Statement_Exception’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for ‘created_at’

    | query (ALTER TABLE `mgn_catalog_product_flat_6` DROP COLUMN `expando_color`, DROP COLUMN `expando_color_value`, DROP COLUMN `expando_diameter_range`, DROP COLUMN `expando_diameter_range_value`, DROP COLUMN `expando_size`, DROP COLUMN `expando_size_value`, DROP COLUMN `expando_spool_size`, DROP COLUMN `expando_spool_size_value`, DROP COLUMN `expando_temperature`, DROP COLUMN `expando_temperature_value`, DROP COLUMN `manufacturer`, DROP COLUMN `manufacturer_value`, DROP COLUMN `master_federal_mogul_part_number`, DROP COLUMN `master_federal_mogul_part_number_value`, DROP COLUMN `master_size_value`, DROP COLUMN `master_spool_length_value`, DROP COLUMN `roundit_color`, DROP COLUMN `roundit_color_value`, DROP COLUMN `roundit_federalmogul_partnumber`, DROP COLUMN `roundit_federalmogul_partnumber_value`, DROP COLUMN `roundit_old_bh_part_number`, DROP COLUMN `roundit_old_bh_part_number_value`, DROP COLUMN `roundit_operating_temperature`, DROP COLUMN `roundit_operating_temperature_value`, DROP COLUMN `roundit_size`, DROP COLUMN `roundit_size_value`, DROP COLUMN `shrink_tubing_color`, DROP COLUMN `shrink_tubing_color_value`, DROP COLUMN `shrink_tubing_size`, DROP COLUMN `shrink_tubing_size_value`, DROP COLUMN `shrink_tubing_rated_temperature`, DROP COLUMN `shrink_tubing_rated_temperature_value`, DROP COLUMN `shrink_tubing_spool_length`, DROP COLUMN `shrink_tubing_spool_length_value`, DROP COLUMN `shrink_tubing_shrink_ratio`, DROP COLUMN `shrink_tubing_shrink_ratio_value`, DROP COLUMN `shrink_tubing_standard`, DROP COLUMN `shrink_tubing_standard_value`, DROP INDEX `IDX_EXPANDO_COLOR`, DROP INDEX `IDX_EXPANDO_COLOR_VALUE`, DROP INDEX `IDX_EXPANDO_DIAMETER_RANGE`, DROP INDEX `IDX_EXPANDO_DIAMETER_RANGE_VALUE`, DROP INDEX `IDX_EXPANDO_SIZE`, DROP INDEX `IDX_EXPANDO_SIZE_VALUE`, DROP INDEX `IDX_EXPANDO_SPOOL_SIZE`, DROP INDEX `IDX_EXPANDO_SPOOL_SIZE_VALUE`, DROP INDEX `IDX_EXPANDO_TEMPERATURE`, DROP INDEX `IDX_EXPANDO_TEMPERATURE_VALUE`, DROP INDEX `IDX_MANUFACTURER`, DROP INDEX `IDX_MANUFACTURER_VALUE`, DROP INDEX `IDX_NAME`, DROP INDEX `IDX_ROUNDIT_COLOR`, DROP INDEX `IDX_ROUNDIT_COLOR_VALUE`, DROP INDEX `IDX_ROUNDIT_OPERATING_TEMPERATURE`, DROP INDEX `IDX_ROUNDIT_OPERATING_TEMPERATURE_VALUE`, DROP INDEX `IDX_ROUNDIT_SIZE`, DROP INDEX `IDX_ROUNDIT_SIZE_VALUE`, DROP INDEX `IDX_SHRINK_TUBING_COLOR`, DROP INDEX `IDX_SHRINK_TUBING_COLOR_VALUE`, DROP INDEX `IDX_SHRINK_TUBING_SIZE`, DROP INDEX `IDX_SHRINK_TUBING_SIZE_VALUE`, DROP INDEX `IDX_SHRINK_TUBING_RATED_TEMPERATURE`, DROP INDEX `IDX_SHRINK_TUBING_RATED_TEMPERATURE_VALUE`, DROP INDEX `IDX_SHRINK_TUBING_SPOOL_LENGTH`, DROP INDEX `IDX_SHRINK_TUBING_SPOOL_LENGTH_VALUE`, DROP INDEX `IDX_SHRINK_TUBING_SHRINK_RATIO`, DROP INDEX `IDX_SHRINK_TUBING_SHRINK_RATIO_VALUE`, DROP INDEX `IDX_SHRINK_TUBING_STANDARD`, DROP INDEX `IDX_SHRINK_TUBING_STANDARD_VALUE`, DROP INDEX `IDX_ROUNDIT_OLD_BH_PART_NUMBER`, DROP INDEX `IDX_ROUNDIT_OLD_BH_PART_NUMBER_VALUE`, DROP INDEX `IDX_ROUNDIT_FEDERALMOGUL_PARTNUMBER`, DROP INDEX `IDX_ROUNDIT_FEDERALMOGUL_PARTNUMBER_VALUE`, DROP INDEX `IDX_MASTER_FEDERAL_MOGUL_PART_NUMBER`, DROP INDEX `IDX_MASTER_FEDERAL_MOGUL_PART_NUMBER_VALUE`, DROP INDEX `IDX_MASTER_SIZE_VALUE`, DROP INDEX `IDX_MASTER_SPOOL_LENGTH_VALUE`, MODIFY COLUMN `image_label` varchar(64) DEFAULT NULL, MODIFY COLUMN `links_title` varchar(64) DEFAULT NULL, MODIFY COLUMN `master_size` decimal(12,4) DEFAULT NULL, MODIFY COLUMN `master_spool_length` decimal(12,4) DEFAULT NULL, MODIFY COLUMN `name` varchar(64) DEFAULT NULL, MODIFY COLUMN `required_options` smallint UNSIGNED NOT NULL DEFAULT ’0′, MODIFY COLUMN `small_image` varchar(64) DEFAULT NULL, MODIFY COLUMN `small_image_label` varchar(64) DEFAULT NULL, MODIFY COLUMN `thumbnail` varchar(64) DEFAULT NULL, MODIFY COLUMN `thumbnail_label` varchar(64) DEFAULT NULL, MODIFY COLUMN `url_key` varchar(64) DEFAULT NULL, MODIFY COLUMN `url_path` varchar(64) DEFAULT NULL, ADD COLUMN `created_at` timestamp DEFAULT NULL AFTER `cost`, ADD COLUMN `master_size_inch` varchar(64) DEFAULT NULL AFTER `master_size`, ADD COLUMN `updated_at` timestamp DEFAULT NULL AFTER `thumbnail_label`, ADD INDEX `IDX_MASTER_SIZE_INCH` (`master_size_inch`), ADD INDEX `IDX_SKU` (`sku`))’ in /home/titanhig/public_html/lib/Zend/Db/Statement/Pdo.php:235

  • raghuveer

    Hi I have an issue regarding index management of Magento 1.5.0.1. When i
    do index management all index suceefully done instaed
    “Catalog_product_Flat” and “Catalog_Category_flat”. I have try to remove
    the tables of catalog_product_flat_1 etc but no issue resolve.