mysql authentification plugin is not loaded

Question

Problem:

I am encountering an login problem when setting up users in mysql. Old users can login as expected, for newly created users I cannot login and get an error message.

Executed SQL:

CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON somedb.* TO 'monty'@'%';
FLUSH PRIVILEGES;

Expected: I would now expect to be allowed to login as user monty from my client code (what works with already existing users).

Error: The login fails with following error message in my client code:

SQLSTATE[HY000] [1524] Plugin '*937217B9DD8E6E458FC3F434BB7292891****' is not loaded'

More Details

when I check the mysql.user table, I see that the plugin column contains the hashed password of the user which is 937217B9DD8E6E458FC3F434BB7292891****.

So I assume there is an error with the auth plugin and followed the migration guidelines(https://dev.mysql.com/doc/refman/5.6/en/account-upgrades.html).

Altering the user with

ALTER USER 'monty'@'%' IDENTIFIED WITH mysql_native_password BY 'some_pass';

helps. However, as soon as I do a flush privileges the login fails again.

Involved system is a docker container build from mysql/mysql-server.

mysql  Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using  EditLine wrapper

uname -a:

Linux 1046ac1aaeee 4.2.0-27-generic 
#32~14.04.1-Ubuntu SMP Fri Jan 22 15:32:26 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

mysql auth vars

mysql> show variables like '%auth'
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
| secure_auth                   | ON                    |
+-------------------------------+-----------------------+
2 rows in set (0.00 sec)

Question

How do I get the newly created users login to work permanently (even after a flush privileges)?

Answer 1

This issue is explained here here.

Run mysql_upgrade -u root -p to fix the issue.

OR

If you are creating the user and getting this issue, then use BY instead of WITH in your query.

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'xyz';

Answer 2

Turns out this is because I forgot to run mysql_upgrade on the database after rebuilding the docker container with the latest mysql version.

After running a mysql_upgrade everything works again as expected. More information can be found here. https://bugs.mysql.com/bug.php?id=79622