MySQL Bugs: #73997: Downgrading docs not complete

Bug #73997	Downgrading docs not complete
Submitted:	21 Sep 2014 20:11	Modified:	2 Jun 2017 19:05
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any

Description:
Page: http://dev.mysql.com/doc/refman/5.6/en/downgrading.html

The documentation for downgrading is not specific enough.

It should at least have a working procedure to downgrade from a default install of 5.6 to 5.5.

How to handle mysql.* tables which use InnoDB?

How do I find the tables using InnDB fulltext indexes? (Add the I_S query to the docs)

How can the MySQL Utilities help the downgrade?

What about GTIDs?

How to repeat:
Try to downgrade with the help of the documentation.

Suggested fix:
Procedure to downgrade from 5.6 to 5.5
0. Stop 5.6 and replace the binaries.
1. Set the innodb_log_file_size in my.cnf as the default has changed.
2. Copy the mysql.host table from another instance (Bug #73634)
3. Start mysql with --skip-grant-tables
4. Drop the mysql.proxies_priv table
5. Run mysql_upgrade
6. update mysql.user set plugin='' where plugin='mysql_native_password'
6. Run flush privileges

Documentation about downgrading with APT/YUM repos is also not present.

Hello Daniël,

Thank you for the report.

Thanks,
Umesh

Because of Bug #70778 a dump/restore downgrade from 5.6 to 5.5 might fail (solution: remove STATS_PERSISTENT=0 from dump)

the new timestamp format should also be taken in account when downgrading.

Posted by developer:
 
The following changes were made to:
https://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

* An I_S query was added for identifying fulltext tables:
https://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

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;

* Information about timestamp storage was added:

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

Posted by developer:
 
Information about innodb_log_file_size default and maximum value changes in MySQL 5.6 was added to:
https://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

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

Resetting status.

The following items have been addressed:

(1) How to handle mysql.* tables that use InnoDB
Changes to system tables are covered here:
https://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

(2) How to find tables that use InnoDB full-text indexes
A query for identifying InnoDB tables with full-text indexes was added here:
https://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

(3) Storage format changes to TIME, DATETIME, and TIMESTAMP columns are covered here:
https://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

(4) The innodb_log_file_size default value change is covered here:
https://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

(5) Disabling GTIDs:
Disabling GTIDs before downgrading is mentioned here:
https://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

There is a separate bug requesting documentation for disabling GTIDs in MySQL 5.6 for downgrading to 5.5 (Bug #86479), but it is not likely to become a documentation priority. Disabling GTIDs is, however, documented for 5.7: 
https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-disable-gtids.html

The following information was added regarding downgrade of MySQL APT, SLES, and Yum repositories:
https://dev.mysql.com/doc/refman/5.6/en/downgrading.html
- In-place downgrades are not supported for MySQL repository installations. 
- Logical downgrades are supported but only between major versions releases.
- For logical downgrades, where instructions call for initializing an older instance, use
the package management utility to remove current version packages and install previous version packages. 

Thank you for the bug report.