Skip navigation links
2.12.4 Rebuilding or Repairing Tables or Indexes »
Section Navigation      [Toggle]

2.12.3. Checking Whether Table 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 change by the new version. However, sometimes modifications are made to the handling of character sets or collations that 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. For information about rebuilding indexes, see Section 2.12.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#29461 was fixed in MySQL 5.0.48, so it applies to upgrades from versions older than 5.0.48 to 5.0.48 or newer, and also to downgrades from 5.0.48 or newer to versions older than 5.0.48.

If you have tables with indexes that are affected, rebuild the indexes using the instructions given in Section 2.12.4, “Rebuilding or Repairing Tables or Indexes”.

In many cases, you can use CHECK TABLE ... FOR UPGRADE to identify tables for which index rebuilding is required. (It will report: 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 12.5.2.3, “CHECK TABLE Syntax”.

Changes that cause index rebuilding to be necessary:

  • MySQL 5.0.48 (Bug#29461)

    Affects indexes for columns that use any of these character sets: eucjpms, euc_kr, gb2312, latin7, macce, ujis

    Affected tables can be detected by CHECK TABLE ... FOR UPGRADE as of MySQL 5.1.29, 5.4.0 (see Bug#39585).

  • MySQL 5.0.48 (Bug#27562)

    Affects indexes that use the ascii_general_ci collation for columns that contain any of these characters: '`' GRAVE ACCENT, '[' LEFT SQUARE BRACKET, '\' REVERSE SOLIDUS, ']' RIGHT SQUARE BRACKET, '~' TILDE

    Affected tables can be detected by CHECK TABLE ... FOR UPGRADE as of MySQL 5.1.29, 5.4.0 (see Bug#39585).

  • MySQL 5.1.21 (Bug#29461)

    Affects indexes for columns that use any of these character sets: eucjpms, euc_kr, gb2312, latin7, macce, ujis

    Affected tables can be detected by CHECK TABLE ... FOR UPGRADE as of MySQL 5.1.29, 5.4.0 (see Bug#39585).

  • MySQL 5.1.23 (Bug#27562)

    Affects indexes that use the ascii_general_ci collation for columns that contain any of these characters: '`' GRAVE ACCENT, '[' LEFT SQUARE BRACKET, '\' REVERSE SOLIDUS, ']' RIGHT SQUARE BRACKET, '~' TILDE

    Affected tables can be detected by CHECK TABLE ... FOR UPGRADE as of MySQL 5.1.29, 5.4.0 (see Bug#39585).

  • 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).

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

  • MySQL 5.4.0 (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).

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

  • MySQL 5.4.4 (WL#3664)

    Affects indexes that use the latin2_czech_cs collation.

  • Affected tables can be detected by CHECK TABLE ... FOR UPGRADE as of MySQL 5.4.4 (see Bug#40054).

    MySQL 5.4.4 (Bug#33452)

    Affects indexes that use the latin2_czech_cs collation.

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

  • MySQL 5.4.4 (Bug#25420)

    Affects indexes for columns that use the following collations, if the columns contain the indicated characters: big5_chinese_ci: '~' TILDE or '`' GRAVE ACCENT; cp866_general_ci: j LATIN SMALL LETTER J; gb2312_chinese_ci: '~' TILDE; gbk_chinese_ci: '~' TILDE

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