Check MySQL Drupal database integrity

By Selwyn Polit, 25 August, 2021

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
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:

warning  : Found row where the auto_increment column has the value 0
status   : OK
warning  : 2 clients are using or haven't closed the table properly
error    : Found 101024 keys of 101023
error    : Corrupt
warning  : 2 clients are using or haven't closed the table properly
status   : OK
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.