MikeJava MikeJava - 1 year ago 294
SQL Question

MySQL Won't let User Login: Error 1524

Server version: 5.7.10 MySQL Community Server (GPL)

In MySQL, I have a user mangos. The user worked perfectly when I created it. After rebooting my computer, though, attempting to login to mangos yielded this output:

$ mysql -u mangos -p
Enter password:
ERROR 1524 (HY000): Plugin '*some_random_long_hash_I_cannot_remember' is not loaded

It kind of reminded me of a password hash, so after investigating mysql.user, I found that mangos had no password!
I updated the password:

SET PASSWORD FOR 'mangos'@'' = PASSWORD('mangos');

Now, I get:

ERROR 1524 (HY000): Plugin '*3FBBDB84EA2B2A0EA599948396AD622B7FF68183' is not loaded

is the same number shown in the password column of mysql.user for mangos, and is a different number than originally. I still can't log in.

How do I make MySQL recognize a password properly? Is that even the issue here?


mysql> SELECT * FROM mysql.user WHERE user = 'mangos' \G
*************************** 1. row ***************************
Host: localhost
User: mangos
Password: *3FBBDB84EA2B2A0EA599948396AD622B7FF68183
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: *3FBBDB84EA2B2A0EA599948396AD622B7FF68183
authentication_string: NULL
password_expired: N

Answer Source

As I suspected, your user table is corrupted. Possibly the reboot you mentioned triggered an upgrade to MySQL and the mysql_upgrade script was not run? This should resolve the situation:

mysql_upgrade -u root -ppassword --skip-grant-tables
mysql -u root -ppassword -e "UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'mangos'; FLUSH PRIVILEGES"

Source: http://kb.odin.com/en/126676

Also worth mentioning, the command to change a user password has changed in MySQL 5.7.6 and forward:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download