MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
CHECK TABLEtbl_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.7, “ALTER TABLE Statement”, and
Section 19.3.4, “Maintenance of Partitions”.
In MySQL 5.6.11 only, gtid_next
must be set to AUTOMATIC
before issuing this
statement. (Bug #16062608, Bug #16715809, Bug #69045)
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.
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 are 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.3, “Changes in MySQL 5.6”. For information about rebuilding tables, see Section 2.11.10, “Rebuilding or Repairing Tables or Indexes”.
The YEAR(2)
data type is
deprecated as of MySQL 5.6.6. For tables containing
YEAR(2)
columns,
CHECK TABLE
recommends
REPAIR TABLE
, which
converts 2-digit YEAR(2)
columns to 4-digit YEAR
columns.
As of MySQL 5.6.4, MySQL permits fractional seconds for
TIME
,
DATETIME
, and
TIMESTAMP
column values. As
a result, encoding and storage requirements for these
temporal column types differ in tables created in MySQL
5.6.4 and later. This incompatible change is described in
Section 2.11.3, “Changes in MySQL 5.6”. When
upgrading to MySQL 5.6.4 or later, be aware that
CHECK TABLE
... FOR UPGRADE
does not report temporal columns
that use the pre-MySQL 5.6.4 format (Bug #73008, Bug
#18985579). In MySQL 5.6.24, two new system variables,
avoid_temporal_upgrade
and
show_old_temporals
, were added to
provide control over temporal column upgrades (Bug #72997,
Bug #18985760).
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. Applies only to
MyISAM tables and views; ignored for
InnoDB . |
CHANGED |
Check only tables that have been changed since the last check or that
have not been closed properly. Applies only to
MyISAM tables and views; ignored for
InnoDB . |
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. Applies only to
MyISAM tables and views; ignored for
InnoDB . |
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. Applies
only to MyISAM tables and views;
ignored for InnoDB . |
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;
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.
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
.