2.11.4. Rebuilding or Repairing Tables or Indexes

This section describes how to rebuild a table, following changes to MySQL such as how data types or character sets are handled. For example, an error in a collation might have been corrected, requiring a table rebuild to update the indexes for character columns that use the collation. (For examples, see Section 2.11.3, "Checking Whether Tables or Indexes Must Be Rebuilt".) You might also need to repair or upgrade a table, as indicated by a table check operation such as that performed by CHECK TABLE, mysqlcheck, or mysql_upgrade.

Methods for rebuilding a table include dumping and reloading it, or using ALTER TABLE or REPAIR TABLE.

Note

If you are rebuilding tables because a different version of MySQL will not handle them after a binary (in-place) upgrade or downgrade, you must use the dump-and-reload method. Dump the tables before upgrading or downgrading using your original version of MySQL. Then reload the tables after upgrading or downgrading.

If you use the dump-and-reload method of rebuilding tables only for the purpose of rebuilding indexes, you can perform the dump either before or after upgrading or downgrading. Reloading still must be done afterward.

To rebuild a table by dumping and reloading it, use mysqldump to create a dump file and mysql to reload the file:

shell> mysqldump db_name
        t1 > dump.sqlshell> mysql db_name
        < dump.sql

To rebuild all the tables in a single database, specify the database name without any following table name:

shell> mysqldump db_name
        > dump.sqlshell> mysql db_name
        < dump.sql

To rebuild all tables in all databases, use the --all-databases option:

shell> mysqldump --all-databases > dump.sqlshell> mysql < dump.sql

To rebuild a table with ALTER TABLE, use a "null" alteration; that is, an ALTER TABLE statement that "changes" the table to use the storage engine that it already has. For example, if t1 is a MyISAM table, use this statement:

mysql> ALTER TABLE t1 ENGINE =
        MyISAM;

If you are not sure which storage engine to specify in the ALTER TABLE statement, use SHOW CREATE TABLE to display the table definition.

If you must rebuild a table because a table checking operation indicates that the table is corrupt or needs an upgrade, you can use REPAIR TABLE if that statement supports the table's storage engine. For example, to repair a MyISAM table, use this statement:

mysql> REPAIR TABLE t1;

For storage engines such as InnoDB that REPAIR TABLE does not support, use mysqldump to create a dump file and mysql to reload the file, as described earlier.

For specifics about which storage engines REPAIR TABLE supports, see Section 13.7.2.5, "REPAIR TABLE Syntax".

mysqlcheck --repair provides command-line access to the REPAIR TABLE statement. This can be a more convenient means of repairing tables because you can use the --databases or --all-databases option to repair all tables in specific databases or all databases, respectively:

shell> mysqlcheck --repair --databases db_name ...shell> mysqlcheck
        --repair --all-databases

For incompatibilities introduced in MySQL 5.1.24 by the fix for Bug #27877 that corrected the utf8_general_ci and ucs2_general_ci collations, a workaround is implemented as of MySQL 5.1.62, 5.5.21, and 5.6.5. Upgrade to one of those versions, then convert each affected table using one of the following methods. In each case, the workaround altering affected columns to use the utf8_general_mysql500_ci and ucs2_general_mysql500_ci collations, which preserve the original pre-5.1.24 ordering of utf8_general_ci and ucs2_general_ci.

To convert an affected table after a binary upgrade that leaves the table files in place, alter the table to use the new collation. Suppose that the table t1 contains one or more problematic utf8 columns. To convert the table at the table level, use a statement like this:
```
ALTER TABLE t1CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci;
```
To apply the change on a column-specific basis, use a statement like this (be sure to repeat the column definition as originally specified except for the COLLATE clause):
```
ALTER TABLE t1MODIFY c1 CHAR(N) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci;
```
To upgrade the table using a dump and reload procedure, dump the table using mysqldump, modify the CREATE TABLE statement in the dump file to use the new collation, and reload the table.

After making the appropriate changes, CHECK TABLE should report no error.