4.4.4 CHECK TABLE Syntax

CHECK TABLE tbl_name[,tbl_name...] [option [option...]]

option = QUICK | FAST | MEDIUM | EXTENDED | CHANGED

CHECK TABLE works only on MyISAM and InnoDB tables. On MyISAM tables, it's the same thing as running myisamchk --medium-check table_name on the table.

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

Checks the table or tables for errors. For MyISAM tables, the key statistics are updated. The command returns a table with the following columns:

Note that the statement may produce 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 Table is already up to date you should normally run a repair of the table. See section 4.4.6 Using myisamchk for Table Maintenance and Crash Recovery. Table is already up to date means that the storage manager for the table indicated that there was no need to check the table.

The different check types are as follows:

For dynamically sized MyISAM tables, a started check will always do a MEDIUM check. For statically sized rows, we skip the row scan for QUICK and FAST as 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 see whether it was closed properly:

CHECK TABLE test_table FAST QUICK;

Note: that 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 didn't find any problems in the table. In this case, CHECK TABLE will mark the table as okay.

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 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 didn't find an error in the datafile. (In most cases MySQL should find, under normal usage, any error in the datafile. 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, FAST is to be prefered over CHANGED. (The only case when it isn't is when you suspect that 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 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 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.

User Comments

Add your own comment.