Thursday, June 6, 2013

MySQL Check Table

The MySQL Check tables command is very useful for anyone who wants to do the following:
  • Checking Version Compatibility 
  • Checking Data Consistency 
  • Upgrades
  • General Table Errors
 The process is simple enough:

> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+

> check table City\G
*************************** 1. row ***************************
   Table: world.City
      Op: check
Msg_type: status
Msg_text: OK


This is a good task to stay updated on so you are aware of possible errors. One possible problem is that this tool really focuses on MyISAM and not InnoDB. If you use it for InnoDB, The "Check table" command really only applies when you add the QUICK option (or no options).  The FAST, CHANGED, MEDIUM and EXTENDED options are all ignored for InnoDB. Now, if you are asking yourself, what about InnoDB? Why would MySQL ignore data consistency in the InnoDB engine? Take a deep breath and relax, InnoDB is ACID complaint, ACID is "an acronym standing for atomicity, consistency, isolation, and durability." So do not disregard checking InnoDB tables because it can still provide you some insight or confirmation on your tables. Keep in mind that if an InnoDB table was to be corrupted the server will shut down to protect the data. You just get more bang for your buck with MyISAM tables and this tool.

Hopefully you get a response of "OK" or "Table is already up to date" otherwise you need to run a repair table to fix the table.

So what are the options available to us so you can do this often and easily.
The documentation link below will also provide you with several community driven automatic options. You can script it the process and easily show tables then apply check tables to all of your results. It just appears easier to me though to use the tools provided for you.


$ mysqlcheck -u root -p --databases world --fast
Enter password:
world.City                               OK
world.Country                            OK
world.CountryLanguage                    OK

$mysqlcheck -u root -p --databases world --fast --check-only-changed
Enter password:
world.City                               OK
world.Country                            OK
world.CountryLanguage                    OK

Now this is simple and direct but it also lends itself to another question, What about the password?

Should you create a user with no password that is allowed to check tables just so you do not have to put a password into your script or cron job? You want to prevent having the password sitting around in .mysql_history files as well.  So again take advantage of the tools available for you. MySQL 5.6 introduced the MySQL Configuration Utility.  I have an example of how to set it up in a previous blog post:
http://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html

mysqlcheck --login-path=local  --databases world --fast --check-only-changed
world.City                              OK
world.Country                           OK
world.CountryLanguage                   OK

$ mysqlcheck --help will provide a full list of options available to you.
Now, you can check all of your tables, keep your passwords out of the crontab file and/or scripts.

Documentation: