Skip navigation links
12.4.2.4 CHECKSUM TABLE Syntax »
Section Navigation      [Toggle]

12.4.2.3. CHECK TABLE Syntax

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

CHECK TABLE checks a table or tables for errors. CHECK TABLE works for MyISAM, InnoDB, and (as of MySQL 5.0.16) ARCHIVE tables. For MyISAM tables, the key statistics are updated as well.

As of MySQL 5.0.2, CHECK TABLE can also check views for problems, such as tables that are referenced in the view definition that no longer exist.

CHECK TABLE returns a result set with the following columns.

Column Value
Table The table name
Op Always check
Msg_type status, error, info, or warning
Msg_text An informational message

Note that the statement might produce many rows of information for each checked table. The last row has a Msg_type value of status and the Msg_text normally should be OK. If you don't get OK, or Table is already up to date you should normally run a repair of the table. See Section 6.6, “MyISAM Table Maintenance and Crash Recovery”. Table is already up to date means that the storage engine for the table indicated that there was no need to check the table.

The FOR UPGRADE option checks whether the named tables are compatible with the current version of MySQL. This option was added in MySQL 5.0.19. With FOR UPGRADE, the server checks each table to determine whether there have been any incompatible changes in any of the table's data types or indexes since the table was created. If not, the check succeeds. Otherwise, if there is a possible incompatibility, the server runs a full check on the table (which might take some time). If the full check succeeds, the server marks the table's .frm file with the current MySQL version number. Marking the .frm file ensures that further checks for the table with the same version of the server will be fast.

Incompatibilities might occur because the storage format for a data type has changed or because its sort order has changed. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.

Currently, FOR UPGRADE discovers these incompatibilities:

  • The indexing order for end-space in TEXT columns for InnoDB and MyISAM tables changed between MySQL 4.1 and 5.0.

  • The storage method of the new DECIMAL data type changed between MySQL 5.0.3 and 5.0.5.

  • As of MySQL 5.0.62, if your table was created by a different version of the MySQL server than the one you are currently running, FOR UPGRADE indicates that the table has an .frm file with an incompatible version. In this case, the result set returned by CHECK TABLE contains a line with a Msg_type value of error and a Msg_text value of Table upgrade required. Please do "REPAIR TABLE `tbl_name`" to fix it!

  • Changes are sometimes made to character sets or collations that require table indexes to be rebuilt. For details about these changes and when FOR UPGRADE detects them, see Section 2.18.3, “Checking Whether Tables or Indexes Must Be Rebuilt”.

The other check options that can be given are shown in the following table. These options are passed to the storage engine, which may use them or not. MyISAM uses them; they are ignored for InnoDB tables and views.

Type Meaning
QUICK Do not scan the rows to check for incorrect links.
FAST Check only tables that have not been closed properly.
CHANGED Check only tables that have been changed since the last check or that have not been closed properly.
MEDIUM Scan rows to verify that deleted links are valid. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.
EXTENDED Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but takes a long time.

If none of the options QUICK, MEDIUM, or EXTENDED are specified, the default check type for dynamic-format MyISAM tables is MEDIUM. This has the same result as running myisamchk --medium-check tbl_name on the table. The default check type also is MEDIUM for static-format MyISAM tables, unless CHANGED or FAST is specified. In that case, the default is QUICK. The row scan is skipped for CHANGED and FAST because the rows are very seldom corrupted.

You can combine check options, as in the following example that does a quick check on the table to determine whether it was closed properly: 

CHECK TABLE test_table FAST QUICK;

Note

In some cases, CHECK TABLE changes the table. This happens if the table is marked as “corrupted” or “not closed properly” but CHECK TABLE does not find any problems in the table. In this case, CHECK TABLE marks the table as okay.

If a table is corrupted, it is most likely that the problem is in the indexes and not in the data part. All of the preceding check types check the indexes thoroughly and should thus find most errors.

If you just want to check a table that you assume is okay, you should use no check options or the QUICK option. The latter should be used when you are in a hurry and can take the very small risk that QUICK does not find an error in the data file. (In most cases, under normal usage, MySQL should find any error in the data file. If this happens, the table is marked as “corrupted” and cannot be used until it is repaired.)

FAST and CHANGED are mostly intended to be used from a script (for example, to be executed from cron) if you want to check tables from time to time. In most cases, FAST is to be preferred over CHANGED. (The only case when it is not preferred is when you suspect that you have found a bug in the MyISAM code.)

EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

Use of CHECK TABLE ... EXTENDED might influence the execution plan generated by the query optimizer.

Some problems reported by CHECK TABLE cannot be corrected automatically:

  • Found row where the auto_increment column has the value 0.

    This means that you have a row in the table where the AUTO_INCREMENT index column contains the value 0. (It is possible to create a row where the AUTO_INCREMENT column is 0 by explicitly setting the column to 0 with an UPDATE statement.)

    This is not an error in itself, but could cause trouble if you decide to dump the table and restore it or do an ALTER TABLE on the table. In this case, the AUTO_INCREMENT column changes value according to the rules of AUTO_INCREMENT columns, which could cause problems such as a duplicate-key error.

    To get rid of the warning, simply execute an UPDATE statement to set the column to some value other than 0.

  • If CHECK TABLE finds a problem for an InnoDB table, the server shuts down to prevent error propagation. Details of the error will be written to the error log.


User Comments

Posted by Zach Gorman on February 5 2004 5:37pm[Delete] [Edit]

Here is a php function you can use in a cron php script to periodically check the database for errors.

I check with $fast = true every 5 minutes and $fast = false every day.

# CheckSqlTables()
# Checks all tables in a database. Returns true if
# everything went successfully; returns false and fills
# out $msg if it did not.
function CheckSqlTables(&$msg, $fast = true) {
$msg = "";
ConnectToDb(); // Connect to your database here.

$rs_tables = mysql_query("SHOW TABLES");
if (!$rs_tables || mysql_num_rows($rs_tables) <= 0) {
$msg = "Could not iterate database tables\n";
return false;
}

$bOk = true;
$checktype = "";
if ($fast)
$checktype = "FAST";
while (list($tname) = mysql_fetch_row($rs_tables)) {
$rs_status = mysql_query("CHECK TABLE `$tname` $checktype");
if (!$rs_status || mysql_num_rows($rs_status) <= 0) {
$msg .= "Could not get status for table $tname\n";
$bOk = false;
continue;
}
# Seek to last row
mysql_data_seek($rs_status, mysql_num_rows($rs_status)-1);
$row_status = mysql_fetch_assoc($rs_status);
if ($row_status['Msg_type'] != "status") {
$msg .= "Table {$row_status['Table']}: ";
$msg .= "{$row_status['Msg_type']} = {$row_status['Msg_text']}\n";
$bOk = false;
}
}
return $bOk;
}

Posted by Greg Fenton on May 12 2004 1:15pm[Delete] [Edit]

I've taken Zach Gorman's example and made it directly ready for use via cron. Tested with PHP 4.3.2 on FreeBSD 4.3.
----------------------------------------------
#!/usr/local/bin/php
<?php

/*
 * Script to check the validity of MYSQL tables by running CHECK TABLE
 * on every table in the given database.
 *
 * To use:
 *    php -f <path_to_script> [-- options]
 *
 * (need to use "--" to separate script options from PHP options
 *
 * Example:
 *    php -f /usr/local/bin/check_mysql_tables.php -- -verbose -fast
 *
 * An example cron entry, checks each day at 2:02am:
 *    2 2 * * * /bin/php -f /bin/check_mysql_tables.php
 *
 */
$usage = "Checks the validity of tables in a mysql database\n\n" .
         "Usage: " . $argv[0] . " [options]\n" .
         "  -f[ast]    perform a fast check (CHECK TABLE tblName FAST)\n" .
         "  -<?|h|x>   show usage string (this message)\n" .
         "  -v[erbose] display verbose messages while running\n";

$msg = "";
$fast = false;
$verbose = false;
$server = "localhost";
$database = "test";
$uid = "root";
$pwd = "";
$tcount = 0;

array_shift($argv);  // take out the script name

foreach ($argv as $option) {
    switch ($option) {
    case '-f':
    case '-fast':
        $fast = true;
        break;
    case '-v':
    case '-verbose':
        $verbose = true;
        echo "Verbose enabled\n";
        break;
    case '-x':
    case '-?':
    case '-h':
        die($usage);
        break;
    default:
        die("Unknown parameter: " . $option . "\n\n" . $usage);
        break;
    }
}

if ( ! mysql_connect($server, $uid, $pwd) ) {
       die("Failed connecting to server: " . mysql_error());
}

if ($verbose) echo "Connected to server: $server\n";

if ( ! mysql_select_db($database) ) {
       die( "Failed selecting database '$database': " . mysql_error() );
}

if ($verbose) echo "Selected database: " . $database . "\n";

$rs_tables = mysql_query("SHOW TABLES");
if (!$rs_tables || (($num_tables = mysql_num_rows($rs_tables)) <= 0) ) {
       die( "Could not iterate database tables\n" );
}
if ($verbose) echo "Number of tables: $num_tables\n";

$bOk = true;
$checktype = "";

if ($fast) $checktype = "FAST";

while (list($tname) = mysql_fetch_row($rs_tables)) {
    $query = "CHECK TABLE `$tname` $checktype";
    if ($verbose) printf("%3d. $query:\n", ++$tcount);

    $rs_status = mysql_query( $query );
    if (!$rs_status || mysql_num_rows($rs_status) <= 0 ) {
        $msg .= "Could not get status for table $tname\n";
        $bOk = false;
        continue;
    }

    // seek to last row
    mysql_data_seek($rs_status, mysql_num_rows($rs_status)-1);
    $row_status = mysql_fetch_assoc($rs_status);
    if ($row_status['Msg_type'] != "status") {
        $msg .= "Table {$row_status['Table']}: ";
        $msg .= "{$row_status['Msg_type']} = {$row_status['Msg_text']}\n";
        $bOk = false;
        if ($verbose) echo "  ** Check failed!!\n";
    }
    if ($verbose) {
        echo "       {$row_status['Msg_type']} -> {$row_status['Msg_text']}\n";
    }

}

if ( ! $bOk ) die( "Check failed: \n\n" . $msg );

exit(0);
?>

Posted by Stu Bray on September 21 2004 9:43pm[Delete] [Edit]

Here is a bash script insipired by the php code posted here by Greg Fenton and Zach Gorman. It works nicely with cron, and has been tested on both linux and darwin. Note that on darwin, the dbexclude feature will not work due to word boundary anchor bugs in regex.

#!/bin/bash
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# sbray@csc.uvic.ca, UVic Fine Arts 2004.
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/

# system variables (change these according to your system)
USER=username
PASSWORD=password
DBHOST=localhost
LOGFILE=./mysql_check.log
MAILTO=root@localhost
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
CORRUPT=no # start by assuming no corruption
DBNAMES="all" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space

# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE

echo -n "Logfile: "
date
echo "---------------------------------------------------------"
echo

# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi

# Run through each database and execute our CHECK TABLE command for each table...
for i in $DBNAMES
do
# to fancy up our log file
echo ""
echo "Database: $i"
echo "---------------------------------------------------------"

DBTABLES="`mysql --user=$USER --password=$PASSWORD $i --batch -N -e "show tables"`"

for j in $DBTABLES
do
echo "CHECK TABLE $j $TYPE1 $TYPE2" | mysql -u$USER -p$PASSWORD $i
done
echo ""
done

exec 1>&6 6>&- # Restore stdout and close file descriptor #6

# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done

# send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $HOST-`date`" $MAILTO
fi

Posted by Eric Barnes on November 16 2004 3:24pm[Delete] [Edit]

For anyone who refuses to give up their VB6 (like me), here's a code snippet for you. You need to make sure you have the Microsoft Active X Data Objects Library referenced in your project.

Private Sub dbCheck()
Dim chkConn, chkRs

Set chkConn = New ADODB.Connection
Set chkRs = New ADODB.Recordset

chkConn.CursorLocation = adUseClient
chkConn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};uid=maximus;password=allhail;server=pablo;database=mexico;option=" & 1 + 2 + 8 + 32 + 2048 + 16384

chkConn.Open
chkRs.Open = chkConn.Execute("CHECK TABLE `inventory` MEDIUM") 'create a recordset using the results from CHECK TABLE

chkRs.MoveLast 'last row will contain the status
strMsgText = StrConv(chkRs("Msg_text"), vbProperCase)
strMsgType = StrConv(chkRs("Msg_type"), vbProperCase)

If chkRs("Msg_text") = "OK" Then
MsgBox "Check successful. No errors reported", vbInformation + vbOKOnly, App.Title
GoTo cleanup
Else
msg = "Severity: " & strMsgType & vbCrLf & _
"Message: " & strMsgText & vbCrLf & vbCrLf & _
"Please run DBRepair or contact technical support for assistance."
MsgBox msg, vbCritical, "DBCheck reported the following error(s):"
GoTo cleanup
End If

cleanup:
'Close database connections and release variables
chkRs.Close
chkConn.Close
Set chkRs = Nothing
Set chkConn = Nothing
End Sub

Posted by eye chart on February 23 2005 4:05am[Delete] [Edit]

I changed up and improved the code posted by Stu Bray. Check out the change log for details.

-eyechart

-----


<?bash
#!/bin/bash
#
# #automysqlcheck.sh
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# original version by sbray@csc.uvic.ca, UVic Fine Arts 2004
#
# modified by eyechart AT gmail.com (see Change Log for details)
#
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/
#
#=====================================================================
# Change Log
#=====================================================================
#
# VER 1.1 - (2005-02-22)
#    Named script automysqlcheck.sh
#    Added PATH variable to make this script more CRON friendly
#    Removed the $DBTABLES loop and replaced it with single command
#      that executes the CHECK TABLE command on all tables in a given DB
#    Changed code to only check MyISAM and InnoDB tables
#    Cleaned up output to make the email prettier
#    Modified script to skip databases that have no tables
# VER 1 - (2004-09-24)
#    Initial release by sbray@csc.uvic.ca


# system variables (change these according to your system)
PATH=/usr/local/bin:/usr/bin:/bin:$PATH
USER=root
PASSWORD=
DBHOST=localhost
LOGFILE=/var/log/automysqlcheck.log
MAILTO=root@localhost
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
CORRUPT=no # start by assuming no corruption
DBNAMES="all" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space

# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE

echo -n "AutoMySQLCheck: "
date
echo "---------------------------------------------------------"; echo; echo

# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
  DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi

# Run through each database and execute our CHECK TABLE command for all tables
# in a single pass - eyechart
for i in $DBNAMES
do
  # echo the database we are working on
  echo "Database being checked:"
  echo -n "SHOW DATABASES LIKE '$i'" | mysql -t -u$USER -p$PASSWORD $i; echo

  # Check all tables in one pass, instead of a loop
  # Use GAWK to put in comma separators, use SED to remove trailing comma
  # Modified to only check MyISAM or InnoDB tables - eyechart
  DBTABLES="`mysql --user=$USER --password=$PASSWORD $i --batch -N -e "show table status;" \
  | gawk 'BEGIN {ORS=", " } $2 == "MyISAM" || $2 == "InnoDB"{print $1}' | sed 's/, $//'`"

  # Output in table form using -t option
  if [ ! "$DBTABLES" ]
  then
    echo "NOTE:  There are no tables to check in the $i database - skipping..."; echo; echo
  else
    echo "CHECK TABLE $DBTABLES $TYPE1 $TYPE2" | mysql -t -u$USER -p$PASSWORD $i; echo; echo
  fi
done

exec 1>&6 6>&- # Restore stdout and close file descriptor #6

# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
  if test $i = "warning" ; then
  CORRUPT=yes
  elif test $i = "error" ; then
  CORRUPT=yes
  fi
done

# Send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $HOST-`date`" $MAILTO
fi
?>

Posted by Laszlob Ronald on March 8 2005 3:27pm[Delete] [Edit]

Hallo eyechart,

When you can not guarantee that no users work with the table, it is better to work with "FLUSH TABLES" and "UNLOCK TABLES".
So the line with "CHECK TABLE...." we can add:

echo "FLUSH TABLES WITH READ LOCK; CHECK TABLE $DBTABLES $TYPE1 $TYPE2; UNLOCK TABLES;" | mysql -t -u$USER -p$PASSWORD $i; echo; echo

I think this is safty.

Ronald Laszlob


Posted by Rob Blick on April 27 2005 2:15pm[Delete] [Edit]

Note that CHECK TABLE requires SELECT privileges only.

Posted by Matthew Haines on August 17 2005 12:22pm[Delete] [Edit]

The above script has a flaw that prevents it from finding and reporting some corrupted tables.

It appears that if a table is completely corrupted, MyISAM does not appear in the 'Engine' column. Thus the script skips the table. Here is an example of output showing this case:

> show table status \G
[ ... snip ...]
*************************** 3. row ***************************
Name: residential
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: Can't open file: 'residential.MYI' (errno: 145)

Thus this table is simply missing from the results. If you have hundreds of tables, it is pretty easy to not realize that one is missing.

Posted by Kristofer Widholm on October 28 2006 3:41am[Delete] [Edit]

Matthew Haines writes on August 17 2005 12:22pm:

>>The above script has a flaw that prevents it from finding and reporting some corrupted tables.

There are at least three scripts "above," all doing similar things. Which one are you refering to? Or all of them?

Thanks.

Posted by [name withheld] on October 29 2006 6:42pm[Delete] [Edit]

I've made one small improvement to this script that is simple but I post it so the less geeky won't run into trouble.

I skipped the colour coding as the first backlash in "\`" $1 "\`" disappeared.

Check out the change log for details.

/ Mickael Sundberg

-----

#!/bin/bash
#
# #automysqlcheck.sh
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# original version by sbray@csc.uvic.ca, UVic Fine Arts 2004
#
# modified by eyechart AT gmail.com and Mickael Sundberg at mickael@pischows.se
# (see Change Log for details)
#
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/
#
#=====================================================================
# Change Log
#=====================================================================
#
# VER 1.2 - (2006-10-29)
# Added "\`" arround the tables in $DBTABLES, otherwise it'll create
# errors if tablenames containt characters like -.
# Modified by Mickael Sundberg
# VER 1.1 - (2005-02-22)
# Named script automysqlcheck.sh
# Added PATH variable to make this script more CRON friendly
# Removed the $DBTABLES loop and replaced it with single command
# that executes the CHECK TABLE command on all tables in a given DB
# Changed code to only check MyISAM and InnoDB tables
# Cleaned up output to make the email prettier
# Modified script to skip databases that have no tables
# Modified by eyechart
# VER 1 - (2004-09-24)
# Initial release by sbray@csc.uvic.ca


# system variables (change these according to your system)
PATH=/usr/local/bin:/usr/bin:/bin:$PATH
USER=root
PASSWORD=
DBHOST=localhost
LOGFILE=/var/log/automysqlcheck.log
MAILTO=root@localhost
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
CORRUPT=no # start by assuming no corruption
DBNAMES="all" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space

# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE

echo -n "AutoMySQLCheck: "
date
echo "---------------------------------------------------------"; echo; echo

# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi

# Run through each database and execute our CHECK TABLE command for all tables
# in a single pass - eyechart
for i in $DBNAMES
do
# echo the database we are working on
echo "Database being checked:"
echo -n "SHOW DATABASES LIKE '$i'" | mysql -t -u$USER -p$PASSWORD $i; echo

# Check all tables in one pass, instead of a loop
# Use GAWK to put in comma separators, use SED to remove trailing comma
# Modified to only check MyISAM or InnoDB tables - eyechart
DBTABLES="`mysql --user=$USER --password=$PASSWORD $i --batch -N -e "show table status;" \
| gawk 'BEGIN {ORS=", " } $2 == "MyISAM" || $2 == "InnoDB"{print "\`" $1 "\`"}' | sed 's/, $//'`"

# Output in table form using -t option
if [ ! "$DBTABLES" ]
then
echo "NOTE: There are no tables to check in the $i database - skipping..."; echo; echo
else
echo "CHECK TABLE $DBTABLES $TYPE1 $TYPE2" | mysql -t -u$USER -p$PASSWORD $i; echo; echo
fi
done

exec 1>&6 6>&- # Restore stdout and close file descriptor #6

# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done

# Send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $HOST-`date`" $MAILTO
fi

Posted by Jake Carr on December 2 2006 9:12pm[Delete] [Edit]

One small tweak to Mickael Sundberg's version of the script posted above, to get the script to pay attention to the DBHOST variable at the top:



#!/bin/bash
#
# #automysqlcheck.sh
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# original version by sbray@csc.uvic.ca, UVic Fine Arts 2004
#
# modified by eyechart AT gmail.com and Mickael Sundberg at mickael@pischows.se and Jake Carr jake-+AT+-websitesource-+DOT+-com
# (see Change Log for details)
#
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/
#
#=====================================================================
# Change Log
#=====================================================================
#
# VER 1.3 - (2006-12-02)
# Added --host=$DBHOST in mysql commands, so it's useful for non-localhost situations
# Jake Carr
# VER 1.2 - (2006-10-29)
# Added "\`" arround the tables in $DBTABLES, otherwise it'll create
# errors if tablenames containt characters like -.
# Modified by Mickael Sundberg
# VER 1.1 - (2005-02-22)
# Named script automysqlcheck.sh
# Added PATH variable to make this script more CRON friendly
# Removed the $DBTABLES loop and replaced it with single command
# that executes the CHECK TABLE command on all tables in a given DB
# Changed code to only check MyISAM and InnoDB tables
# Cleaned up output to make the email prettier
# Modified script to skip databases that have no tables
# Modified by eyechart
# VER 1 - (2004-09-24)
# Initial release by sbray@csc.uvic.ca


# system variables (change these according to your system)
PATH=/usr/local/bin:/usr/bin:/bin:$PATH
USER=root
PASSWORD=
DBHOST=localhost
LOGFILE=/var/log/automysqlcheck.log
MAILTO=root@localhost
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
CORRUPT=no # start by assuming no corruption
DBNAMES="all" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space

echo -n "AutoMySQLCheck: "
date
echo "---------------------------------------------------------"; echo; echo

# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --host=$DBHOST --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi

# Run through each database and execute our CHECK TABLE command for all tables
# in a single pass - eyechart
for i in $DBNAMES
do
# echo the database we are working on
echo "Database being checked:"
echo -n "SHOW DATABASES LIKE '$i'" | mysql -t --host=$DBHOST -u$USER -p$PASSWORD $i; echo

# Check all tables in one pass, instead of a loop
# Use GAWK to put in comma separators, use SED to remove trailing comma
# Modified to only check MyISAM or InnoDB tables - eyechart
DBTABLES="`mysql --host=$DBHOST --user=$USER --password=$PASSWORD $i --batch -N -e "show table status;" | gawk 'BEGIN {ORS=", " } $2 == "MyISAM" || $2 == "InnoDB"{print "\`" $1 "\`"}' | sed 's/, $//'`"

# Output in table form using -t option
if [ ! "$DBTABLES" ]
then
echo "NOTE: There are no tables to check in the $i database - skipping..."; echo; echo
else
echo "CHECK TABLE $DBTABLES $TYPE1 $TYPE2" | mysql --host=$DBHOST -t -u$USER -p$PASSWORD $i; echo; echo
fi
done

exec 1>&6 6>&- # Restore stdout and close file descriptor #6

# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done

# Send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $DBHOST-`date`" $MAILTO
fi

Posted by david mcanulty on December 29 2006 12:19am[Delete] [Edit]

There is an error in the above example that prevents logfile generation. The block below is missing:

# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE

It should be between:
DBEXCLUDE="" # or a list delimited by space

and:
echo -n "AutoMySQLCheck: "


I also found it a more useful default to set $dbhost=localhost to
$dbhost=`hostname -s`

Posted by Cary Petterborg on September 19 2008 4:17pm[Delete] [Edit]

Starting with 5.0.51 or 5.0.67 the scripts above will have a problem when they perform a CHECK TABLE on the information_schema MyISAM tables, as these produce an error with the msg_text of:

Table upgrade required. Please do "REPAIR TABLE `/tmp/#sql_162_0`" to fix it!

This is a problem introduced in one of the two versions. Even with a completely clean install, and no newly created databases, this message will result from the check. Modifying the shell script to do something like (in context):

for i in $DBNAMES
do
if test $i = 'information_schema'
then
continue
fi
# echo the database we are working on
...

This will just exclude the information_schema database from the checks, thus preventing seeing the error that is not really an error. A similar fix could be done with the php script as well.

Add your own comment.