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 apassword_expired
column. Themysql.user
table in MySQL 5.5 does not. This means that an account with an expired password in MySQL 5.6 works 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 themysql.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 usingSHOW CREATE TABLE
, or see Bug #73634.
Data Types
For
TIME
,DATETIME
, andTIMESTAMP
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 ascolumns_priv
andproxies_priv
). This means thatmysql
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 ofFULLTEXT
), 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 withFULLTEXT
indexes can be identified using anINFORMATION_SCHEMA
query. 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;
InnoDB
small page sizes specified by theinnodb_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 allInnoDB
tables in instances that use a smallerInnoDB
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
, andSTATS_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 14.8.11.1, “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 usingROW_FORMAT=DYNAMIC
orROW_FORMAT=COMPRESSED
. See Section 14.22, “InnoDB Limits” 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 optioninnodb_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 Section 17.2.2.2, “Replication Metadata Repositories”. If you downgrade a replica server to a version older than MySQL 5.6, the older server does 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 17.1.3.5, “Disabling GTID Transactions”).