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

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”.

Index Incompatibilities

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. 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 and 5.4.0, so it applies to upgrades from versions older than 5.1.24 to 5.1.24 or newer, and to downgrades from 5.1.24 or newer to versions older than 5.1.24.

Changes that cause index rebuilding to be necessary:

  • MySQL 4.1.2 (Bug#3152)

    String comparison works according to the SQL standard: Instead of stripping end spaces before comparison, we now extend the shorter string with spaces. The problem with this is that now 'a' > 'a\t', which it was not before. If you have any tables where you have indexes on CHAR, VARCHAR or TEXT column in which the last character in index values may be less than ASCII(32), you should rebuild those indexes.

  • MySQL 4.1.3

    InnoDB uses the same character set comparison functions as MySQL for non-latin1_swedish_ci character strings that are not BINARY. This changes the sorting order of space and characters with a code < ASCII(32) in those character sets. This affects InnoDB tables with an index on a non-latin1_swedish_ci character set and collation order column that is not BINARY if that column contains characters with a code < ASCII(32). (For MySQL 4.1.0 and 4.1.1, it affects indexes with any character set and collation).

  • MySQL 5.0.48, 5.1.21 (Bug#29461)

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

  • MySQL 5.0.48, 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

  • MySQL 5.1.24, 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).