Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 36.5Mb
HTML Download (TGZ) - 9.9Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 210.0Kb
Man Pages (Zip) - 319.1Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

13.7.2.2 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. For MyISAM tables, the key statistics are updated as well. CHECK TABLE can also check views for problems, such as tables that are referenced in the view definition that no longer exist.

To check a table, you must have some privilege for it.

CHECK TABLE works for InnoDB, MyISAM, ARCHIVE, and CSV tables.

Before running CHECK TABLE on InnoDB tables, see CHECK TABLE Usage Notes for InnoDB Tables.

CHECK TABLE is supported for partitioned tables, and you can use ALTER TABLE ... CHECK PARTITION to check one or more partitions; for more information, see Section 13.1.8, “ALTER TABLE Syntax”, and Section 22.3.4, “Maintenance of Partitions”.

CHECK TABLE ignores virtual generated columns that are not indexed.

CHECK TABLE Output

CHECK TABLE returns a result set with the columns shown in the following table.

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

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. For a MyISAM table, if you don't get OK or Table is already up to date, you should normally run a repair of the table. See Section 7.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.

Checking Version Compatibility

The FOR UPGRADE option checks whether the named tables are compatible with the current version of MySQL. 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.

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.

  • 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 such changes, see Section 2.11.1.2, “Changes Affecting Upgrades to MySQL 5.7”. For information about rebuilding tables, see Section 2.11.3, “Rebuilding or Repairing Tables or Indexes”.

  • The YEAR(2) data type is deprecated and support for it is removed in MySQL 5.7.5. For tables containing YEAR(2) columns, CHECK TABLE recommends REPAIR TABLE, which converts YEAR(2) to YEAR(4).

  • As of MySQL 5.7.2, trigger creation time is maintained. If run against a table that has triggers, CHECK TABLE ... FOR UPGRADE displays this warning for each trigger created before MySQL 5.7.2:

    Trigger db_name.tbl_name.trigger_name does not have CREATED attribute.

    The warning is informational only. No change is made to the trigger.

  • As of MySQL 5.7.7, a table is reported as needing a rebuild if it contains old temporal columns in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision) and the avoid_temporal_upgrade system variable is disabled. This helps mysql_upgrade detect and upgrade tables containing old temporal columns. If avoid_temporal_upgrade is enabled, FOR UPGRADE ignores the old temporal columns present in the table; consequently, mysql_upgrade does not upgrade them.

    To check for tables that contain such temporal columns and need a rebuild, disable avoid_temporal_upgrade before executing CHECK TABLE ... FOR UPGRADE.

  • Warnings are issued for tables that use nonnative partitioning because nonnative partitioning is deprecated in MySQL 5.7 and removed in MySQL 8.0. See Chapter 22, Partitioning.

Checking Data Consistency

The following table shows the other check options that can be given. These options are passed to the storage engine, which may use or ignore them.

Type Meaning
QUICK Do not scan the rows to check for incorrect links. Applies to InnoDB and MyISAM tables and views.
FAST Check only tables that have not been closed properly. Ignored for InnoDB; applies only to MyISAM tables and views.
CHANGED Check only tables that have been changed since the last check or that have not been closed properly. Ignored for InnoDB; applies only to MyISAM tables and views.
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. Ignored for InnoDB; applies only to MyISAM tables and views.
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. Ignored for InnoDB; applies only to MyISAM tables and views.

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

If CHECK TABLE finds no problems with a table that is marked as corrupted or not closed properly, CHECK TABLE may remove the mark.

If a table is corrupted, the problem is most likely 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.

To check a table that you assume is okay, 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) to check tables periodically. 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 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 execution plans 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, execute an UPDATE statement to set the column to some value other than 0.

CHECK TABLE Usage Notes for InnoDB Tables

The following notes apply to InnoDB tables:

  • If CHECK TABLE encounters a corrupt page, the server exits to prevent error propagation (Bug #10132). If the corruption occurs in a secondary index but table data is readable, running CHECK TABLE can still cause a server exit.

  • If CHECK TABLE encounters a corrupted DB_TRX_ID or DB_ROLL_PTR field in a clustered index, CHECK TABLE can cause InnoDB to access an invalid undo log record, resulting in an MVCC-related server exit.

  • If CHECK TABLE encounters errors in InnoDB tables or indexes, it reports an error, and usually marks the index and sometimes marks the table as corrupted, preventing further use of the index or table. Such errors include an incorrect number of entries in a secondary index or incorrect links.

  • If CHECK TABLE finds an incorrect number of entries in a secondary index, it reports an error but does not cause a server exit or prevent access to the file.

  • CHECK TABLE surveys the index page structure, then surveys each key entry. It does not validate the key pointer to a clustered record or follow the path for BLOB pointers.

  • When an InnoDB table is stored in its own .ibd file, the first 3 pages of the .ibd file contain header information rather than table or index data. The CHECK TABLE statement does not detect inconsistencies that affect only the header data. To verify the entire contents of an InnoDB .ibd file, use the innochecksum command.

  • When running CHECK TABLE on large InnoDB tables, other threads may be blocked during CHECK TABLE execution. To avoid timeouts, the semaphore wait threshold (600 seconds) is extended by 2 hours (7200 seconds) for CHECK TABLE operations. If InnoDB detects semaphore waits of 240 seconds or more, it starts printing InnoDB monitor output to the error log. If a lock request extends beyond the semaphore wait threshold, InnoDB aborts the process. To avoid the possibility of a semaphore wait timeout entirely, run CHECK TABLE QUICK instead of CHECK TABLE.

  • CHECK TABLE functionality for InnoDB SPATIAL indexes includes an R-tree validity check and a check to ensure that the R-tree row count matches the clustered index.

  • CHECK TABLE supports secondary indexes on virtual generated columns, which are supported by InnoDB.

CHECK TABLE Usage Notes for MyISAM Tables

The following notes apply to MyISAM tables:

  • CHECK TABLE updates key statistics for MyISAM tables.

  • If CHECK TABLE output does not return OK or Table is already up to date, you should normally run a repair of the table. See Section 7.6, “MyISAM Table Maintenance and Crash Recovery”.

  • If none of the CHECK TABLE 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.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Fabrizio La Rosa on October 18, 2010
In this version I have add the I/O redirection suggested by David and changed the db exclusion so that it also works with Darwin/Mac OS X

#!/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.
#
# 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)
#
#=====================================================================
# Change Log
#=====================================================================
#
# VER 1.4 - (2010-10-18)
# Added I/O redirection to $LOGFILE
# added flush & lock tables before check
# modified the database exclusion so that it works also with Darwin/Mac OS X
# Modified by Fabrizio La Rosa
# 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" # either "all" or a list delimited by space
DBEXCLUDE="" # either "" 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...
if [ "$DBNAMES" = "all" ] ; then
DBNAMES=""
ALLDB="`mysql --host=$DBHOST --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $ALLDB ; do
INCLUDEDB=1
for j in $DBEXCLUDE ; do
if [ "$i" = "$j" ] ; then
INCLUDEDB=0
fi
done
if [ $INCLUDEDB -eq 1 ] ; then
DBNAMES=$DBNAMES" "$i
fi
done
fi

# Lock tables
mysql --host=$DBHOST --user=$USER --password=$PASSWORD --batch -N -e "flush tables with read lock; flush logs"
# 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 AWK 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;" | awk '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
# Unlock tables
mysql --host=$DBHOST --user=$USER --password=$PASSWORD --batch -N -e "unlock tables"

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

Sign Up Login You must be logged in to post a comment.