Rechercher dans le manuel MySQL
13.7.3.2 CHECK TABLE Syntax
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 for
InnoDB
,
MyISAM
,
ARCHIVE
, and
CSV
tables.
Before running CHECK TABLE
on
InnoDB
tables, see
CHECK TABLE Usage Notes for InnoDB Tables.
CHECK TABLE
is supported for
partitioned tables, and you can use ALTER TABLE ...
CHECK PARTITION
to check one or more partitions; for
more information, see Section 13.1.9, “ALTER TABLE Syntax”, and
Section 23.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.
Column | Value |
---|---|
Table |
The table name |
Op |
Always check |
Msg_type |
status , error ,
info , note , or
warning |
Msg_text |
An informational message |
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
. 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.
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).
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 in
TEXT
columns forInnoDB
andMyISAM
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.Changes are sometimes made to character sets or collations that require table indexes to be rebuilt. For details about such changes, see Section 2.11.4, “Changes in MySQL 8.0”. For information about rebuilding tables, see Section 2.11.13, “Rebuilding or Repairing Tables or Indexes”.
MySQL 8.0 does not support the
YEAR(2)
data type permitted in older versions of MySQL. For tables containingYEAR(2)
columns,CHECK TABLE
recommendsREPAIR TABLE
, which convertsYEAR(2)
toYEAR(4)
.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, disable
avoid_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. See Chapter 23, Partitioning.
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.
Type | Meaning |
---|---|
QUICK |
Do not scan the rows to check for incorrect links. Applies to
InnoDB and MyISAM
tables and views. |
FAST |
Check only tables that have not been closed properly. Ignored for
InnoDB ; applies only to
MyISAM tables and views. |
CHANGED |
Check only tables that have been changed since the last check or that
have not been closed properly. Ignored for
InnoDB ; applies only to
MyISAM tables and views. |
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. Ignored for
InnoDB ; applies only to
MyISAM tables and views. |
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. Ignored
for InnoDB ; applies only to
MyISAM 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:
If CHECK 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 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) to check tables
periodically. 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 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 of CHECK
TABLE ... EXTENDED
might influence execution plans
generated by the query optimizer.
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 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 an
ALTER 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 an
UPDATE
statement to set the column to some value other than 0.
The following notes apply to
InnoDB
tables:
If
CHECK 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.If
CHECK 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 an MVCC-related server exit.If
CHECK 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.If
CHECK 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 an
InnoDB
table is stored in its own.ibd
file, the first 3 pages 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 the innochecksum command.When running
CHECK 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 forInnoDB
SPATIAL
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.
The following notes apply to
MyISAM
tables:
CHECK TABLE
updates key statistics forMyISAM
tables.If
CHECK TABLE
output does not returnOK
orTable is already up to date
, you should normally run a repair of the table. See Section 7.6, “MyISAM Table Maintenance and Crash Recovery”.If none of the
CHECK TABLE
optionsQUICK
,MEDIUM
, orEXTENDED
are specified, the default check type for dynamic-formatMyISAM
tables isMEDIUM
. This has the same result as running myisamchk --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.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-check-table.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.