Magento reindex data

Dear Magento administrators/programmers, you may be stuck in a problem with index management of magento very often.

1) When i tried to re-index products i got this error.
Cannot initialize the indexer process.
So, I run index from command line:

php shell/indexer.php reindexall 

But i got the following error:
Category Flat Data index process unknown error:

exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1005 Can't create table './xyz_db/catalog_category_flat_store_1.frm' (errno: 150)' in /home/xyz/public_html/lib/Zend/Db/Statement/Pdo.php: 228 

But these few lines saved me. run these sql commands and you will be all set.

ALTER TABLE catalog_category_entity ENGINE=INNODB;
ALTER TABLE catalog_category_entity ENGINE=INNODB;

I also deleted all the .locks file from the var/locks folder.
If none found then make sure, the var/locks folder is writable.

2) Always the Category Flat Data is in processing mode. Below is the process we used to solve the reindexing issue for Category Products (Indexed category/products association):

Log into phpMyAdmin
Go to `catalog_category_product_index`
Click “Export”.
Export only structure with “Disable foreign key checks” enabled.
Drop the table `catalog_category_product_index`
With the SQL that was exported, remove the constraints at the bottom of the export. Be sure to keep the “SET FOREIGN_KEY_CHECKS” statements.
Click “SQL”, paste in modified SQL and click “Go”
Login to Magento backend
Go to: System -> Index Management
Click “Reindex Data” next to “Category Products” index

Another One simple solution is to truncate table `catalog_category_flat_store_1`.

If it wasn’t work then enable “log” from magento’s backend configuration. Check debug message in /var/log/exception.log file. If it says anything relevant to permission to database user then from your cPanel or web host manager grant the appropriate permissions to the database user. This is all you needed to do and hoorray, you got the rid of the messages “Some problem with reindexing process”.

Another thing that might be the table being locked. To get rid of it empty the /var/locks folder.

3) Check that the your magento tables are of type Innodb.
If your magento tables are of type MyISAM then definitely some problems of Indexing occurs. Change your database to Innodb. Follow the below link to change your tables to type Innodb.

Hope it helps you while you’re treading you way though Magento reindex process. Comment here for any questions or suggestions.

Speak Your Mind

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: