First thing I would like to say is I absolutely hate re-indexing manually on Magento. But as we all Magento Developers have no choice but to fix it and make client and boss happy. We encounter errors that gives more headache if we don't find the solution straight away. Sometimes I have to browse through so many magento forums pages with no luck. So, I'm going to show you how to fix this annoying magento reindexing error. When I tried to re-index products first I got this error.
Cannot initialize the indexer process.
Then again when I re-tried it, I got below error:
There was a problem with reindexing process.SQLSTATE[HY000]: General error: 1005 Can't create table 'magento.catalog_category_flat_store_1' (errno: 150)
So, I run index from command line:
php shell/indexer.php reindexall
But I got the following error again:
Category Flat Data index process unknown error: exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1005 Can't create table './magento.catalog_category_flat_store_1.frm' (errno: 150)' in /home/magento/public_html/lib/Zend/Db/Statement/Pdo.php: 228
But these few lines saved me. Run these sql commands on phpMyadmin or command line to your magento database and you will be able to reindex again.
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.
Then I re-run the indexer and everything was back to normal. yay!
[email protected] [~/public_html/shell]# php indexer.php reindexall Product Attributes index was rebuilt successfully Product Prices index was rebuilt successfully Catalog URL Rewrites index was rebuilt successfully Product Flat Data index was rebuilt successfully Category Flat Data index was rebuilt successfully Category Products index was rebuilt successfully Catalog Search Index index was rebuilt successfully Stock Status index was rebuilt successfully Tag Aggregation Data index was rebuilt successfully
I had reindex issue again and fixed by deleting flat tables and then reindexed successfully.
I created list of flat tables by running below command on mysql.
SELECT CONCAT('DROP TABLE ', table_name, ';') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = 'database_name' AND `table_name` like 'catalog_%flat%' ORDER BY table_name;
I got below result:
DROP TABLE catalog_category_flat_store_1; DROP TABLE catalog_category_flat_store_10; DROP TABLE catalog_category_flat_store_2; DROP TABLE catalog_category_flat_store_3; DROP TABLE catalog_category_flat_store_4; DROP TABLE catalog_category_flat_store_5; DROP TABLE catalog_category_flat_store_6; DROP TABLE catalog_category_flat_store_7; DROP TABLE catalog_category_flat_store_8; DROP TABLE catalog_category_flat_store_9; DROP TABLE catalog_product_flat_1; DROP TABLE catalog_product_flat_10; DROP TABLE catalog_product_flat_6; DROP TABLE catalog_product_flat_7; DROP TABLE catalog_product_flat_8; DROP TABLE catalog_product_flat_9;
And then I ran each sql statement to delete flat tables.
Did that solved your re-indexing issue in your magento admin? Let me know if you know any other tips and tricks to solve this bug or issue.
Please like and +1 to support us.