2.11.2.1 Changes Affecting Downgrades from MySQL 5.6

Before downgrading from MySQL 5.6, review the changes described in this section. Some changes may require action before or after downgrading.

System Tables

• The mysql.user 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.

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

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