13.5.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
ARCHIVE tables. 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.
CHECK TABLE returns a result set with the
following columns:
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 5.9.4, “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.1.7. 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 other check options that can be given are shown in the
following table. These options apply only to checking
MyISAM tables and are ignored for
InnoDB 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: 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.
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.
