Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.3Kb
Man Pages (Zip) - 402.5Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

15.7.3.2 CHECK TABLE Statement

CHECK TABLEtbl_name [,tbl_name] ... [option] ...option: {    FOR UPGRADE  | QUICK  | FAST  | MEDIUM  | EXTENDED  | CHANGED}

CHECK TABLE checks a table or tables for errors.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 forInnoDB,MyISAM,ARCHIVE, andCSV tables.

Before runningCHECK TABLE onInnoDB tables, seeCHECK TABLE Usage Notes for InnoDB Tables.

CHECK TABLE is supported for partitioned tables, and you can useALTER TABLE ... CHECK PARTITION to check one or more partitions; for more information, seeSection 15.1.9, “ALTER TABLE Statement”, andSection 26.3.4, “Maintenance of Partitions”.

CHECK TABLE ignores virtual generated columns that are not indexed.

CHECK TABLE Output

CHECK TABLE returns a result set with the columns shown in the following table.

ColumnValue
TableThe table name
OpAlwayscheck
Msg_typestatus,error,info,note, orwarning
Msg_textAn informational message

The statement might produce many rows of information for each checked table. The last row has aMsg_type value ofstatus and theMsg_text normally should beOK.Table is already up to date means that the storage engine for the table indicated that there was no need to check the table.

Checking Version Compatibility

TheFOR UPGRADE option checks whether the named tables are compatible with the current version of MySQL. WithFOR 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).

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 inTEXT columns forInnoDB andMyISAM tables changed between MySQL 4.1 and 5.0.

  • The storage method of the newDECIMAL data type changed between MySQL 5.0.3 and 5.0.5.

  • Changes are sometimes made to character sets or collations that require table indexes to be rebuilt. For details about such changes, seeSection 3.5, “Changes in MySQL 8.0”. For information about rebuilding tables, seeSection 3.14, “Rebuilding or Repairing Tables or Indexes”.

  • MySQL 8.0 does not support the 2-digitYEAR(2) data type permitted in older versions of MySQL. For tables containingYEAR(2) columns,CHECK TABLE recommendsREPAIR TABLE, which converts 2-digitYEAR(2) columns to 4-digitYEAR columns.

  • Trigger creation time is maintained.

  • A table is reported as needing a rebuild if it contains old temporal columns in pre-5.6.4 format (TIME,DATETIME, andTIMESTAMP columns without support for fractional seconds precision) and theavoid_temporal_upgrade system variable is disabled. This helps the MySQL upgrade procedure detect and upgrade tables containing old temporal columns. Ifavoid_temporal_upgrade is enabled,FOR UPGRADE ignores the old temporal columns present in the table; consequently, the upgrade procedure does not upgrade them.

    To check for tables that contain such temporal columns and need a rebuild, disableavoid_temporal_upgrade before executingCHECK TABLE ... FOR UPGRADE.

  • Warnings are issued for tables that use nonnative partitioning because nonnative partitioning is removed in MySQL 8.0. SeeChapter 26,Partitioning.

Checking Data Consistency

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.

TypeMeaning
QUICKDo not scan the rows to check for incorrect links. Applies toInnoDB andMyISAM tables and views.
FASTCheck only tables that have not been closed properly. Ignored forInnoDB; applies only toMyISAM tables and views.
CHANGEDCheck only tables that have been changed since the last check or that have not been closed properly. Ignored forInnoDB; applies only toMyISAM tables and views.
MEDIUMScan 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. Ignored forInnoDB; applies only toMyISAM tables and views.
EXTENDEDDo a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but takes a long time. Ignored forInnoDB; applies only toMyISAM tables and views.

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

IfCHECK 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 theQUICK option. The latter should be used when you are in a hurry and can take the very small risk thatQUICK 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 andCHANGED are mostly intended to be used from a script (for example, to be executed fromcron) to check tables periodically. In most cases,FAST is to be preferred overCHANGED. (The only case when it is not preferred is when you suspect that you have found a bug in theMyISAM 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 ofCHECK TABLE ... EXTENDED might influence execution plans generated by the query optimizer.

Some problems reported byCHECK 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 theAUTO_INCREMENT index column contains the value 0. (It is possible to create a row where theAUTO_INCREMENT column is 0 by explicitly setting the column to 0 with anUPDATE statement.)

    This is not an error in itself, but could cause trouble if you decide to dump the table and restore it or do anALTER TABLE on the table. In this case, theAUTO_INCREMENT column changes value according to the rules ofAUTO_INCREMENT columns, which could cause problems such as a duplicate-key error.

    To get rid of the warning, execute anUPDATE statement to set the column to some value other than 0.

CHECK TABLE Usage Notes for InnoDB Tables

The following notes apply toInnoDB tables:

  • IfCHECK 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, runningCHECK TABLE can still cause a server exit.

  • IfCHECK TABLE encounters a corruptedDB_TRX_ID orDB_ROLL_PTR field in a clustered index,CHECK TABLE can causeInnoDB to access an invalid undo log record, resulting in anMVCC-related server exit.

  • IfCHECK TABLE encounters errors inInnoDB 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.

  • IfCHECK 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 forBLOB pointers.

  • When anInnoDB table is stored in its own.ibd file, the first 3pages of the.ibd file contain header information rather than table or index data. TheCHECK TABLE statement does not detect inconsistencies that affect only the header data. To verify the entire contents of anInnoDB.ibd file, use theinnochecksum command.

  • When runningCHECK TABLE on largeInnoDB tables, other threads may be blocked duringCHECK TABLE execution. To avoid timeouts, the semaphore wait threshold (600 seconds) is extended by 2 hours (7200 seconds) forCHECK TABLE operations. IfInnoDB detects semaphore waits of 240 seconds or more, it starts printingInnoDB 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, runCHECK TABLE QUICK instead ofCHECK TABLE.

  • CHECK TABLE functionality forInnoDBSPATIAL indexes includes an R-tree validity check and a check to ensure that the R-tree row count matches the clustered index.

  • CHECK TABLE supports secondary indexes on virtual generated columns, which are supported byInnoDB.

  • As of MySQL 8.0.14,InnoDB supports parallel clustered index reads, which can improveCHECK TABLE performance.InnoDB reads the clustered index twice during aCHECK TABLE operation. The second read can be performed in parallel. Theinnodb_parallel_read_threads session variable must be set to a value greater than 1 for parallel clustered index reads to occur. The default value is 4. The actual number of threads used to perform a parallel clustered index read is determined by theinnodb_parallel_read_threads setting or the number of index subtrees to scan, whichever is smaller.

CHECK TABLE Usage Notes for MyISAM Tables

The following notes apply toMyISAM tables:

  • CHECK TABLE updates key statistics forMyISAM tables.

  • IfCHECK TABLE output does not returnOK orTable is already up to date, you should normally run a repair of the table. SeeSection 9.6, “MyISAM Table Maintenance and Crash Recovery”.

  • If none of theCHECK TABLE optionsQUICK,MEDIUM, orEXTENDED are specified, the default check type for dynamic-formatMyISAM tables isMEDIUM. This has the same result as runningmyisamchk --medium-checktbl_name on the table. The default check type also isMEDIUM for static-formatMyISAM tables, unlessCHANGED orFAST is specified. In that case, the default isQUICK. The row scan is skipped forCHANGED andFAST because the rows are very seldom corrupted.