Occasionally,
the magento “catalog_product_flat” indexer will hang. Recently, this happened to a store I was
working on that had several hundred thousand products with 225
catalog_product_flat_x entries.
The
fix for this problem is normally to BACKUP THE DATABASE, truncate all the “catalog_product_flat_x”
tables, and then reindex the catalog product entries.
However,
I am not so hot to manually truncate over 200 tables by hand. So I used a bash script along with a
configuration file.
One
thing interesting to note here, I flat out could not get “mysql” on my Mac
(installed with Zend server) to recognize the “mysql
–defaults-extra-file=whatever” command. It
works fine on the AWS boxes, but on my Mac, it just keeps giving the error:
/usr/local/zend/mysql/bin/mysql.client: unknown
variable 'defaults-extra-file=magento.cnf'
which
is pretty annoying. And yes, it was the first
argument after the “mysql” command. I created
a SQL script to create a dummy local magento database with a couple
“catalog_product_flat_x” tables as well as some other differently named tables
to test it on. But I ended up having to
test it on an AWS dev server because of this.
Giving
credit where it is due, I modified a bash script from here: https://gist.github.com/marcanuy/5977648
The
modified script is:
#!/bin/bash
# Truncate database tables from console
DATABASE=magento
DEFAULTS_FILE=$DATABASE.cnf
mysql --defaults-extra-file="$DEFAULTS_FILE"
-Nse 'SELECT table_name FROM information_schema.tables WHERE table_schema =
"magento" AND table_name like ("catalog_product_flat_%")'
$DATABASE | while read table; do mysql
--defaults-extra-file="$DEFAULTS_FILE" -e "truncate table
$table" $DATABASE; done
And
the config file which needs to be named “magento.cnf”, looks like:
[client]
host=localhost
user=magento
password=magento
where
of course you have to substitute the credentials for your mysql server in place
of the test credentials above.
Then
finally, go to the Magento docroot directory, and re-run the indexer:
php shell/indexer.php --reindex
catalog_product_flat
