DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) check-table

Info Catalog (mysql.info) backup-table (mysql.info) table-maintenance-sql (mysql.info) checksum-table 
 
 13.5.2.3 `CHECK TABLE' Syntax
 .............................
 
      CHECK TABLE TBL_NAME [, TBL_NAME] ... [OPTION] ...
 
      OPTION = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
 
 `CHECK TABLE' checks a table or tables for errors. `CHECK TABLE' works
 for `MyISAM', `InnoDB', and (as of MySQL 5.0.16) `ARCHIVE' tables. For
 `MyISAM' tables, the key statistics are updated as well.
 
 As of MySQL 5.0.2, `CHECK TABLE' can also check views for problems,
 such as tables that are referenced in the view definition that no
 longer exist.
 
 `CHECK TABLE' returns a result set 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 has 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. See
  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 `FOR UPGRADE' option checks whether the named tables are compatible
 with the current version of MySQL.  This option was added in MySQL
 5.0.19. 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 will
 be 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.
 
 Currently, `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.
 
 The other 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'     Do not scan the rows to check for incorrect links.
 `FAST'      Check only tables that have not been closed properly.
 `CHANGED'   Check only tables that have been changed since the last
             check or that have not been closed properly.
 `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.
 `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.
 
 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;
 
 * In some cases, `CHECK TABLE' changes the table. This happens if
 the table is marked as `corrupted' or `not closed properly' but `CHECK
 TABLE' does not find any problems in the table. In this case, `CHECK
 TABLE' marks the table as okay.
 
 If a table is corrupted, it is 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'
 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') if you want to check tables from
 time to time. 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 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' 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, simply execute an `UPDATE' statement to
      set the column to some value other than 0.
Info Catalog (mysql.info) backup-table (mysql.info) table-maintenance-sql (mysql.info) checksum-table
automatically generated byinfo2html