CHECK TABLEtbl_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,
ARCHIVE, and CSV tables.
For MyISAM tables, the key statistics are
updated as well.
To check a table, you must have some privilege for it.
CHECK TABLE can also check views
for problems, such as tables that are referenced in the view
definition that no longer exist.
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 12.1.6, “ALTER TABLE Syntax”, and
Section 18.3.3, “Maintenance of Partitions”.
CHECK TABLE returns a result set
with the following columns.
| Column | Value |
|---|---|
Table | The table name |
Op | Always check |
Msg_type | status, error,
info, note, 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.
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
TEXTcolumns forInnoDBandMyISAMtables changed between MySQL 4.1 and 5.0.The storage method of the new
DECIMALdata 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 UPGRADEindicates that the table has an.frmfile with an incompatible version. In this case, the result set returned byCHECK TABLEcontains a line with aMsg_typevalue oferrorand aMsg_textvalue ofTable 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 UPGRADEdetects them, see Section 2.13.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;
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_INCREMENTindex column contains the value 0. (It is possible to create a row where theAUTO_INCREMENTcolumn is 0 by explicitly setting the column to 0 with anUPDATEstatement.)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 TABLEon the table. In this case, theAUTO_INCREMENTcolumn changes value according to the rules ofAUTO_INCREMENTcolumns, which could cause problems such as a duplicate-key error.To get rid of the warning, simply execute an
UPDATEstatement to set the column to some value other than 0.If
CHECK TABLEfinds a problem for anInnoDBtable, the server shuts down to prevent error propagation. Details of the error will be written to the error log.