MySQL Bugs: #76264: Safest and most common upgrade strategy broken by 5.7.6

Bug #76264	Safest and most common upgrade strategy broken by 5.7.6
Submitted:	11 Mar 2015 16:39	Modified:	13 Jun 2015 7:42
Reporter:	Justin Swanhart	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.7.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL 5.7.6 changelog contains the following:
You cannot use mysqldump to dump the user table from a version of MySQL older than 5.7.6 and reload the dump file into MySQL 5.7.6 or later. Instead, perform a binary (in-place) upgrade to MySQL 5.7.6 or later and run mysql_upgrade to migrate the Password column contents to the authentication_string column.

How to repeat:
The most common method of upgrade from an older release (say 5.0) to a newer release (say 5.7.6+) is to dump the old database and restore in the new database.  Obviously this no longer works as of 5.7.6

Other common scenarios are going from a cloud provider that provides an older version of the database to a newer version of the database.  Data is dumped from the old provider and imported into the new provider/database.

It is extremely important to be able to dump data from the old database and load it into the new database without loading it into an older version first and doing an in-place upgrade.  That is too many moving parts - variable names change, and all kinds of other things, and it make no sense to have to manage these changes in addition to worrying about making sure the data is loaded properly.

Suggested fix:
Allow the user table to be dropped and recreated.

There are a number of different options on how to make this work. Justin provided one way already.
A dump conversion tool could be another, or an updated mysqldump in the 5.6 range that can adjust on-the-fly.

Critically, changes such as these should be considered with the real world users in mind - many things sound just fine in the lab, but don't work out so well in the ugly real world. It's really not cool to send things into the world without these issues already having been resolved.
thanks

The changelog text quoted at the beginning of the bug report was incorrect. My fault: I wrote it based on a misapprehension of the implications of the change to the mysql.user table. It is in fact possible to reload an old (pre-5.7.6) dump file. I've amended the release notes:

The change in mysql.user table structure has compatibility implications for upgrading and downgrading:

* You can perform a binary (in-place) upgrade to MySQL 5.7.6 or later and run mysql_upgrade to migrate the Password column contents to the authentication_string column.

* If you plan to upgrade by loading a mysqldump dump file from an older (pre-5.7.6) MySQL installation, you must observe these conditions for the mysqldump command used to generate the file:

  * You must include the --add-drop-table option

  * You must not include the --flush-privileges option

  Load the pre-5.7.6 dump file into the 5.7.6 server before running mysql_upgrade.

Closing per last comment.