2.13.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:

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

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.

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.13.4, “Rebuilding or Repairing Tables or Indexes”.

Table Incompatibilities

After a binary upgrade to MySQL 5.1 from a MySQL 5.0 installation
that contains ARCHIVE tables:

As of MySQL 5.1.42, the server will not open 5.0
ARCHIVE tables at all.

In either case, the solution is to use
mysqldump to dump all 5.0
ARCHIVE tables before upgrading, and
reload them into MySQL 5.1 after upgrading. This problem is fixed
in MySQL 5.6.4: The server can open
ARCHIVE tables created in MySQL 5.0.
However, it remains the recommended upgrade procedure to dump 5.0
ARCHIVE tables before upgrading and
reload them after upgrading.

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:

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.13.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 newer, 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:

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.

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.

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