GNU Info

(mysql.info)CHECK TABLE

---

Next: REPAIR TABLE Prev: RESTORE TABLE Up: Disaster Prevention

---

Enter node , (file) or (file)node

`CHECK TABLE' Syntax
--------------------

     CHECK TABLE tbl_name[,tbl_name...] [option [option...]]
     
     option = QUICK | FAST | MEDIUM | EXTENDED | CHANGED

`CHECK TABLE' only works on `MyISAM' tables. On `MyISAM' tables it's
the same thing as running `myisamchk -m table_name' on the table.

If you don't specify any option `MEDIUM' is used.

Checks the table(s) for errors. For `MyISAM' tables the key statistics
is updated. The command returns a table with the following columns:

*Column*                  *Value*
Table                     Table name.
Op                        Always "check".
Msg_type                  One of `status', `error', `info', or `warning'.
Msg_text                  The message.

Note that you can get many rows of information for each checked table.
The last row will be of `Msg_type status' and should normally be `OK'.
If you don't get `OK', or `Not checked' you should normally run a
repair of the table. Note: Table maintenance. `Not checked' means
that the table the given `TYPE' told MySQL that there wasn't any need
to check the table.

The different check types stand for the following:

*Type*         *Meaning*
`QUICK'        Don't scan the rows to check for wrong links.
`FAST'         Only check tables which haven't been closed properly.
`CHANGED'      Only check tables which have been changed since last
               check or haven't been closed properly.
`MEDIUM'       Scan rows to verify that deleted links are ok. This also
               calculates a key checksum for the rows and verifies this
               with a calcualted 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!

For dynamic sized `MyISAM' tables a started check will always do a
`MEDIUM' check. For static size rows we skip the row scan for `QUICK'
and `FAST' as the rows are very seldom corrupted.

You can combine check options as in:

     CHECK TABLE test_table FAST QUICK;

Which only would do a quick check on the table if it wasn't closed
properly.

*NOTE:* that in some case `CHECK TABLE' will change the table!  This
happens if the table is marked as 'corrupted' or 'not closed properly'
but `CHECK TABLE' didn't find any problems in the table.  In this case
`CHECK TABLE' will mark the table as ok.

If a table is corrupted, then it's most likely that the problem is in
the indexes and not in the data part.  All of the above check types
checks the indexes throughly and should thus find most errors.

If you just want to check a table that you assume is ok, you should use
no check options or the `QUICK' option. The later should be used when
you are in a hurry and can take the very small risk that `QUICK' didn'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 then the
table will be marked as 'corrupted', in which case the table can't 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 you `FAST' is to be prefered over
`CHANGED'.  (The only case when it isn't is when you suspect a bug you
have found a bug in the `MyISAM' code.).

`EXTENDED' is only to be used 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 to happen if a normal check
has succeeded!).

Some things 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 in the table a row 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
     explicitely 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 like 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.