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

  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 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 Section 14.6.12.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 using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. See Section 14.8.1.7, “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 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.

• 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”).