11.3 Downgrade Notes

Before downgrading from MySQL 5.6, review the
information in this section. Some items may require action before
downgrading.

System Tables

The mysql.user system table in MySQL 5.6
has a password_expired column. The
mysql.user table in MySQL 5.5 does not.
This means that an account with an expired password in MySQL
5.6 will work normally in MySQL 5.5.

The mysql.host table was removed in MySQL
5.6.7. When downgrading to a previous release, startup on the
downgraded server fails with an error if the
mysql.host table is not present. You can
recreate the table manually or restore it from a backup taken
prior to upgrading to MySQL 5.6.7 or higher. To recreate the
table manually, retrieve the table definition from a pre-MySQL
5.6.7 instance using SHOW CREATE
TABLE, or see Bug #73634.

Data Types

For TIME,
DATETIME, and
TIMESTAMP columns, the storage
required for tables created before MySQL 5.6.4 differs from
storage required for tables created in 5.6.4 and later. This
is due to a change in 5.6.4 that permits these temporal types
to have a fractional part. To downgrade to a version older
than 5.6.4, dump affected tables with
mysqldump before downgrading, and reload
the tables after downgrading.

The following query identifies tables and columns that may be
affected by this problem. Some of them are system tables in
the mysql database (such as
columns_priv and
proxies_priv). This means that
mysql is one of the databases you must dump
and reload, or server startup may fail after downgrading.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('TIME','DATETIME','TIMESTAMP')
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

InnoDB

InnoDB search indexes (with a type of
FULLTEXT), introduced in MySQL 5.6.4, are
not compatible with earlier versions of MySQL, including
earlier releases in the 5.6 series. Drop such indexes before
performing a downgrade.

InnoDB tables with
FULLTEXT indexes can be identified using an
INFORMATION_SCHEMA query. For
example:

InnoDB small page sizes specified by the
innodb_page_size
configuration option, introduced in MySQL 5.6.4, are not
compatible with earlier versions of MySQL, including earlier
releases in the 5.6 series. Dump all InnoDB
tables in instances that use a smaller
InnoDB page size, drop the tables, and
re-create and reload them after the downgrade.

Tables created using persistent statistics table options
(STATS_PERSISTENT,
STATS_AUTO_RECALC, and
STATS_SAMPLE_PAGES) introduced in MySQL
5.6.6, are not compatible with earlier releases (Bug #70778).
Remove the options from table definitions prior to
downgrading. For information about these options, see
Configuring Persistent Optimizer Statistics Parameters.

The innodb_log_file_size
default and maximum values were increased in MySQL
5.6. Before downgrading, ensure that the
configured log file size is compatible with the previous
release.

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

Replication

As of MySQL 5.6, the relay-log.info file
contains a line count and a replication delay value, so the
file format differs from that in older versions. See
Slave Status Logs. If you downgrade a slave
server to a version older than MySQL 5.6, the older server
will not read the file correctly. To address this, modify the
file in a text editor to delete the initial line containing
the number of lines.

Beginning with MySQL 5.6.6, the MySQL Server employs Version 2
binary log events when writing the binary log. Binary logs
written using Version 2 log events cannot by read by earlier
versions of MySQL Server. To generate a binary log that is
written using Version 1 log events readable by older servers,
start the MySQL 5.6.6 or later server using
--log-bin-use-v1-row-events=1,
which forces the server to employ Version 1 events when
writing the binary log.

The MySQL 5.6.5 release introduced global
transaction identifiers (GTIDs) for MySQL
Replication. If you enabled GTIDs in MySQL 5.6 and want to
downgrade to a MySQL release that does not support GTIDs, you
must disable GTIDs before downgrading (see
Disabling GTID Transactions).