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 core_store ENGINE=INNODB; ALTER TABLE catalog_category_entity ENGINE=INNODB; ALTER TABLE core_store 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.