Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.5Mb
PDF (A4) - 31.6Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 181.3Kb
Man Pages (Zip) - 292.4Kb
Info (Gzip) - 2.8Mb
Info (Zip) - 2.8Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Checking Whether Tables or Indexes Must Be Rebuilt

2.11.3 Checking Whether Tables or Indexes Must Be Rebuilt

A binary upgrade or downgrade is one that installs one version of MySQL in place over an existing version, without dumping and reloading tables:

  1. Stop the server for the existing version if it is running.

  2. Install a different version of MySQL. This is an upgrade if the new version is higher than the original version, a downgrade if the version is lower.

  3. Start the server for the new version.

In many cases, the tables from the previous version of MySQL can be used without problem by the new version. However, sometimes changes occur that require tables or table indexes to be rebuilt, as described in this section. If you have tables that are affected by any of the issues described here, rebuild the tables or indexes as necessary using the instructions given in Section 2.11.4, “Rebuilding or Repairing Tables or Indexes”.

Table Incompatibilities

If you have ARCHIVE tables created in MySQL 5.0, the recommended upgrade procedure is to dump the 5.0 ARCHIVE tables before upgrading and reload them after upgrading.

As of MySQL 5.6.4, MySQL permits fractional seconds for TIME, DATETIME, and TIMESTAMP column values. As a result, encoding and storage requirements for these temporal column types differ in tables created in MySQL 5.6.4 and later. This incompatibility is described in Section 2.11.1.1, “Changes Affecting Upgrades to MySQL 5.6”. When upgrading to MySQL 5.6.4 or later, be aware that CHECK TABLE ... FOR UPGRADE does not report temporal columns that use the pre-MySQL 5.6.4 format (Bug #73008, Bug #18985579). In MySQL 5.6.24, two new system variables, avoid_temporal_upgrade and show_old_temporals, were added to provide control over temporal column upgrades (Bug #72997, Bug #18985760).

Index Incompatibilities

In MySQL 5.6.3, the length limit for index prefix keys is increased from 767 bytes to 3072 bytes, for InnoDB tables using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. See Section 14.6.7, “Limits on InnoDB Tables” for details. This change is also backported to MySQL 5.5.14. If you downgrade from one of these releases or higher, to an earlier release with a lower length limit, the index prefix keys could be truncated at 767 bytes or the downgrade could fail. This issue could only occur if the configuration option innodb_large_prefix was enabled on the server being downgraded.

If you perform a binary upgrade without dumping and reloading tables, you cannot upgrade directly from MySQL 4.1 to 5.1 or higher. This occurs due to an incompatible change in the MyISAM table index format in MySQL 5.0. Upgrade from MySQL 4.1 to 5.0 and repair all MyISAM tables. Then upgrade from MySQL 5.0 to 5.1 and check and repair your tables.

Modifications to the handling of character sets or collations might change the character sort order, which causes the ordering of entries in any index that uses an affected character set or collation to be incorrect. Such changes result in several possible problems:

  • Comparison results that differ from previous results

  • Inability to find some index values due to misordered index entries

  • Misordered ORDER BY results

  • Tables that CHECK TABLE reports as being in need of repair

The solution to these problems is to rebuild any indexes that use an affected character set or collation, either by dropping and re-creating the indexes, or by dumping and reloading the entire table. In some cases, it is possible to alter affected columns to use a different collation. For information about rebuilding indexes, see Section 2.11.4, “Rebuilding or Repairing Tables or Indexes”.

To check whether a table has indexes that must be rebuilt, consult the following list. It indicates which versions of MySQL introduced character set or collation changes that require indexes to be rebuilt. Each entry indicates the version in which the change occurred and the character sets or collations that the change affects. If the change is associated with a particular bug report, the bug number is given.

The list applies both for binary upgrades and downgrades. For example, Bug #27877 was fixed in MySQL 5.1.24, so it applies to upgrades from versions older than 5.1.24 to 5.1.24 or higher, and to downgrades from 5.1.24 or newer to versions older than 5.1.24.

In many cases, you can use CHECK TABLE ... FOR UPGRADE to identify tables for which index rebuilding is required. It will report this message:

Table upgrade required.
Please do "REPAIR TABLE `tbl_name`" or dump/reload to fix it!

In these cases, you can also use mysqlcheck --check-upgrade or mysql_upgrade, which execute CHECK TABLE. However, the use of CHECK TABLE applies only after upgrades, not downgrades. Also, CHECK TABLE is not applicable to all storage engines. For details about which storage engines CHECK TABLE supports, see Section 13.7.2.2, “CHECK TABLE Syntax”.

These changes cause index rebuilding to be necessary:

  • MySQL 5.1.24 (Bug #27877)

    Affects indexes that use the utf8_general_ci or ucs2_general_ci collation for columns that contain 'ß' LATIN SMALL LETTER SHARP S (German). The bug fix corrected an error in the original collations but introduced an incompatibility such that 'ß' compares equal to characters with which it previously compared different.

    Affected tables can be detected by CHECK TABLE ... FOR UPGRADE as of MySQL 5.1.30 (see Bug #40053).

    A workaround for this issue is implemented as of MySQL 5.1.62, 5.5.21, and 5.6.5. The workaround involves 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.


User Comments
Sign Up Login You must be logged in to post a comment.