0

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)?

2

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 than use "BY" instead of "WITH" in your query.

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'xyz';
| improve this answer | |
1

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.