Mysql upgrade locks out its root user

[Dario Bertini]

If you have a mysql database with an empty root password (like we have here for development purpose) with mysql 5.6, with the upgrade to 16.04 you'll find yourself locked out on mysql 5.7

The problem is the new feature of mysql-server that generates a temporary password.

Unfortunately, this password is not recorded anywhere, neither in a .mysql_secret file (if the deprecated mysql_install_db script would've been used) which is nowhere on the filesystem, nor in the mysql logs. (Like instead it is on Windows or Red Hat, apparently).

The only solution (aside from wiping the db, and selecting a new password at install time), is to follow the instructions to reset permissions:

http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html?acf=1

(but you won't be able to simply use the --init-file option, due to the apparmor sandboxing of mysqld)

I attached a simple terminal session that demonstrates the issue

ProblemType: Bug
DistroRelease: Ubuntu 16.04
Package: mysql-server 5.7.11-0ubuntu6
ProcVersionSignature: Ubuntu 4.4.0-17.33-generic 4.4.6
Uname: Linux 4.4.0-17-generic x86_64
ApportVersion: 2.20.1-0ubuntu2
Architecture: amd64
CurrentDesktop: Unity
Date: Mon Apr 18 14:34:46 2016
InstallationDate: Installed on 2016-03-07 (42 days ago)
InstallationMedia: Ubuntu 16.04 LTS "Xenial Xerus" - Alpha amd64 (20160304)
Logs.var.log.daemon.log:

MySQLConf.etc.mysql.mysql.conf.d.mysqld_safe_syslog.cnf:
 [mysqld_safe]
 syslog
MySQLVarLibDirListing: False
PackageArchitecture: all
SourcePackage: mysql-5.7
UpgradeStatus: No upgrade log present (probably fresh install)

[Dario Bertini (berdario)]

I just realized that not even the

mysqld_safe --skip-grant-tables --skip-networking

solution will work, since after logging in

ALTER USER 'root'@'localhost' IDENTIFIED BY '';

will fail with

ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded

(the same applies for the old command: `SET PASSWORD FOR 'root'@'localhost' = PASSWORD('');`)

And trying

install plugin auth_socket soname 'auth_socket.so';

will only result in

ERROR 1030 (HY000): Got error 1 from storage engine

[Robie Basak (racb)]

I was going to add this to NEWS.Debian but missed it in my last upload - sorry.

> The problem is the new feature of mysql-server that generates a temporary password.

I think it's something else - please see below. Based on this, you should be able to change the root password to something non-empty with "sudo dpkg-reconfigure mysql-server-5.7" and then logging in as a non-root user should work. Alternatively, you should be able to log in as the mysql root user that has an empty password if you run the client as Unix root (eg. with sudo).

Please could you confirm if this works for you?

Intended NEWS.Debian next:

    Password behaviour when the MySQL root password is empty has changed. Packaging now enables socket authentication when the MySQL root password is empty. This means that a non-root user can't log in as root with an empty password. The new logic is as follows:

    The auth_socket plugin will be installed automatically only if it is to be activated for the root user.

    The auth_socket plugin will be activated for the root user:

    If you had a database before with an empty root password.

    If you create a new database with an empty root password.

    The auth_socket plugin WILL NOT be activated for the root user:

    If you had a database before with a root password set.

    If you create a new database with a root password set.

    The auth_socket plugin WILL NOT be activated for any other user.

    If you do not want the new behaviour, simply set the MySQL root password to be non-empty.

    The MySQL server now uses strict mode by default. See http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict for details. See http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-setting for details about the default settings in older versions.

[Dario Bertini (berdario)]

Thank you, indeed I had the suspicion that some kind of local socket authentication might've been disabled, instead of changing the password.

> I think it's something else - please see below. Based on this, you should be able to change the root password to something non-empty with "sudo dpkg-reconfigure mysql-server-5.7" and then logging in as a non-root user should work. Alternatively, you should be able to log in as the mysql root user that has an empty password if you run the client as Unix root (eg. with sudo).

I did already attempt the dpkg-reconfigure, but that's not useful (You can select the password only when creating a new datadir with empty config, and thus a dpkg-reconfigure does not suffice).

logging in with sudo instead works, but weirdly if then I

ALTER USER 'root'@'localhost' IDENTIFIED BY 'a';

I cannot login as normal user by supplying password `a`, and otoh I can still login by sudo-ing and without providing any mysql password. (I presume that this happens due to something like disabling the ability to change permissions when logging in via sudo... weird that this doesn't explicitly generate an error, though)

[Robie Basak (racb)]

> I did already attempt the dpkg-reconfigure, but that's not useful (You can select the password only when creating a new datadir with empty config, and thus a dpkg-reconfigure does not suffice).

I thought this is supposed to work, but perhaps I'm mistaken.

> ALTER USER 'root'@'localhost' IDENTIFIED BY 'a';

I think you need to switch authentication back to password if you're doing it manually like that. Lars will know how - I would need to look it up.

[Robie Basak (racb)]

So the original bug report is behaviour by design, so I'll mark this Won't Fix. If there's an issue with setting the root password afterwards though, then we can reopen this bug and change the title to track that, or file another one or something.

[Dario Bertini (berdario)]

Ok, at the very least this needs some documentation though. Since neither Mysql's own documentation nor Ubuntu's community wiki have any working solution.

[Dario Bertini (berdario)]

For those who are wondering:

My solution is to simply not use 5.7, and manually install mysql-server-5.6 (after all, the application that I'm working on still needs a bunch of changes to be able to work with 5.7's new stricter defaults)

[Lars Tangvald (lars-tangvald)]

Hi,

What you need is:
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'somepass';

[Robie Basak (racb)]

On Wed, Apr 20, 2016 at 04:49:09PM -0000, Dario Bertini wrote:
> My solution is to simply not use 5.7, and manually install mysql-
> server-5.6...

Note that Xenial will ship MySQL 5.7 only. 5.6 will be removed shortly.

[Leif (leifcr)]

There is a solution to fix this here:
https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/

After setting the password using mysql_native_password, you can set it to be empty again.

[Lars Tangvald (lars-tangvald)]

MySQL isn't internally strict with the empty root password; auth socket is enabled by packaging to make it more secure by default, not by the server itself, so just running ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY ''; will work