Before downgrading from MySQL 5.6, review the information in this section. Some items may require action before downgrading.
mysql.usersystem table in MySQL 5.6 has a
mysql.usertable 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.
mysql.hosttable 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.hosttable 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.
TIMESTAMPcolumns, 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
mysqldatabase (such as
proxies_priv). This means that
mysqlis 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;
InnoDBsearch 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.
FULLTEXTindexes can be identified using an
INFORMATION_SCHEMAquery. For example:
SELECT a.NAME AS Table_name, b.NAME AS Index_name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES a, INFORMATION_SCHEMA.INNODB_SYS_INDEXES b WHERE a.TABLE_ID = b.TABLE_ID AND b.TYPE = 32;
InnoDBsmall page sizes specified by the
innodb_page_sizeconfiguration 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
InnoDBtables in instances that use a smaller
InnoDBpage size, drop the tables, and re-create and reload them after the downgrade.
Tables created using persistent statistics table options (
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 Section 18.104.22.168, “Configuring Persistent Optimizer Statistics Parameters”.
innodb_log_file_sizedefault 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
ROW_FORMAT=COMPRESSED. See Section 22.214.171.124, “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_prefixwas enabled on the server being downgraded.
As of MySQL 5.6, the
relay-log.infofile contains a line count and a replication delay value, so the file format differs from that in older versions. See Section 126.96.36.199, “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 Section 188.8.131.52, “Disabling GTID Transactions”).