(mysql.info.gz) CHECK TABLE
Info Catalog
(mysql.info.gz) BACKUP TABLE
(mysql.info.gz) Table maintenance SQL
(mysql.info.gz) CHECKSUM TABLE
13.5.2.3 `CHECK TABLE' Syntax
.............................
CHECK TABLE TBL_NAME [, TBL_NAME] ... [OPTION] ...
OPTION = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
Checks a table or tables for errors. `CHECK TABLE' works for `MyISAM'
and `InnoDB' tables. For `MyISAM' tables, the key statistics are
updated.
As of MySQL 5.0.2, `CHECK TABLE' also can check views for problems, such
as tables that are referenced in the view definition that no longer
exist.
The `CHECK TABLE' statement returns a table with the following columns:
*Column* *Value*
`Table' The table name
`Op' Always `check'
`Msg_type' One of `status', `error', `info', or
`warning'
`Msg_text' The message
Note that the statement might produce many rows of information for each
checked table. The last row will have 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. Table maintenance. `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 different 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.
*Type* *Meaning*
`QUICK' Don't scan the rows to check for incorrect links.
`FAST' Only check tables that haven't been closed properly.
`CHANGED' Only check tables that have been changed since the last
check or haven't been closed properly.
`MEDIUM' Scan rows to verify that deleted links are okay. 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 will take 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 is the same thing 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, which does
a quick check on the table to see whether it was closed properly:
CHECK TABLE test_table FAST QUICK;
* In some cases, `CHECK TABLE' will change the table! This
happens if the table is marked as "corrupted" or "not closed properly"
but `CHECK TABLE' doesn't find any problems in the table. In this
case, `CHECK TABLE' marks the table as okay.
If a table is corrupted, it's 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'
doesn't find an error in the data file. (In most cases, MySQL should
find, under normal usage, any error in the data file. If this happens,
the table is marked as "corrupted" and cannot be used until it's
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 your table
from time to time. In most cases, `FAST' is to be preferred over
`CHANGED'. (The only case when it isn't 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' can't 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's
possible to create a row where the `AUTO_INCREMENT' column is 0 by
explicitly setting the column to 0 with an `UPDATE' statement.)
This isn't 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 will change
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, just execute an `UPDATE' statement to
set the column to some other value than 0.
Info Catalog
(mysql.info.gz) BACKUP TABLE
(mysql.info.gz) Table maintenance SQL
(mysql.info.gz) CHECKSUM TABLE
automatically generated byinfo2html