Bug #73026 Upgrade to 5.7 disables user accounts
Submitted: 17 Jun 2014 16:41 Modified: 11 Nov 2014 4:30
Reporter: Philip Antoniades Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Pluggable Authentication Severity:S3 (Non-critical)
Version:5.7.4-m14 OS:Linux (14.04)
Assigned to: CPU Architecture:Any
Tags: authentication, plugin, user

[17 Jun 2014 16:41] Philip Antoniades 
Description:
Upgraded server from 5.6 (installed via .deb) to 5.7 (via APT repository). Could no longer log in to server. Startup logs showed all users were disabled for having NULL 'plugin' values in the mysql.user table. 

Starting with --skip-grant-tables and updating all rows with plugin = 'mysql_native_password' fixed it. 

How to repeat:
Install mysql version < 5.7 without any AUTH plugins. Upgrade via APT repo to latest 5.7 milestone.

Suggested fix:
Add procedure in upgrade process to do 
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE plugin IS NULL
[8 Jul 2014 12:50] MySQL Verification Team 
Philip,

This is actually expected behavior when you upgrade to 5.7 without required procedure. It is all very well described in our manual, but here I will quote you only the important part.

In order to have privilege tables prepared for version 5.7, you should run mysql_upgrade against a server with --skip-grant-tables. 

I hope that this is clear. Let me know if you have any other questions.
[11 Nov 2014 4:30] Paul DuBois 
Noted in 5.7.6 changelog.

Beginning with MySQL 5.7.2, the server disables at startup any
account that has no authentication plugin. The server now writes a
more extensive message to the error log in this case to indicate how
to reenable such accounts.