Recently I suspected I had a problem in my mysql databases on my webserver, so I ran the following command:
mysqlcheck -u root -p'put-your-database-password-here' --all-databases
I got a neat list of databases and tables and messages saying some tables are OK and some not. Notice that I cleverly replaced my database root password with the phrase put-your-database-password-here, so you should replace that with your password if you want to try this. BTW it seems you can ignore the warnings about client using or not closing tables.
It looks a bit like this:
wp_austinperm.wp_commentmeta OK
wp_austinperm.wp_comments OK
wp_austinperm.wp_links OK
wp_austinperm.wp_options OK
wp_austinperm.wp_postmeta OK
wp_austinperm.wp_posts OK
wp_austinperm.wp_search_phrases
warning : 1 client is using or hasn't closed the table properly
status : OK
wp_austinperm.wp_term_relationships OK
wp_austinperm.wp_term_taxonomy OK
wp_austinperm.wp_terms OK
wp_austinperm.wp_usermeta OK
wp_austinperm.wp_users OK
You can further send the output to a file like this:
mysqlcheck -u root -p'put-your-database-password-here' --all-databases >mysqlcheck.txt
Then look in the file using a text editor or the following command to find problems:
nano mysqlcheck.txt
egrep -v '.*\..*.*OK' mysqlcheck.txt
Here is a problem I found The g2_CacheMap table apparently was corrupt. Check out the log below:
drupal_ssw.users
warning : Found row where the auto_increment column has the value 0
status : OK
gallery_austexcs.g2_CacheMap
warning : 2 clients are using or haven't closed the table properly
error : Found 101024 keys of 101023
error : Corrupt
gallery_austexcs.g2_Derivative
warning : 2 clients are using or haven't closed the table properly
status : OK
gallery_austexcs.g2_EventLogMap
warning : 2 clients are using or haven't closed the table properly
status : OK
To fix the table, I went to phpmyadmin, selected the table, selected repair table (luckily it was a myisam table) and now the application runs again. Strangely, the repair table option is not available for the InnoDB Type tables - so much so for new and improved.
You can also check a specific database using the following syntax
mysqlcheck -u put-database-username-here -p'put-your-database-password-here' --databases put-database-name-here
eg. mysqlcheck -u d7_kipp_user -p'mypass' --databases d7_kipp_db
I hope folks find this useful.