MySQL Bugs: #114876: Inconsistent error for bad user when mysql_native

Bug #114876	Inconsistent error for bad user when mysql_native_password=OFF
Submitted:	3 May 21:11	Modified:	6 May 7:57
Reporter:	Evan Elias	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	8.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 8.4.0, with the default of mysql_native_password=OFF, attempting to connect to the server with a nonexistent user name sometimes results in ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

In prior releases, this would always result in ERROR 1045 (28000): Access denied for user ... (using password: ...)

In 8.4.0, with mysql_native_password=OFF, it seems the response randomly changes between errors 1524 and 1045.

How to repeat:
Note the error difference on the third attempt. None of these users exist on the server btw.

bash-4.4# mysql -h 127.0.0.1 -u fakeuser1 -pwrong
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'fakeuser1'@'127.0.0.1' (using password: YES)
bash-4.4# mysql -h 127.0.0.1 -u fakeuser2 -pwrong
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'fakeuser2'@'127.0.0.1' (using password: YES)
bash-4.4# mysql -h 127.0.0.1 -u fakeuser3 -pwrong
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

I was only able to reproduce this with mysql_native_password=OFF. When I use ON, the server behaves like previous releases, always giving error 1045.

Suggested fix:
Consistently return error 1045 in this situation.

Hello Evan Elias,

Thank you for the report and feedback.

regards,
Umesh

Additionally the error "ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded" is confusing.

I saw this when upgrading a server to 8.4 from 8.0.
Unfortunately I had not updated the mysql_native_password users to caching_sha2_password so could not login :-(

The error seems to indicate that you should "load the plugin", but of course there's no explicit mysql_native_password plugin to load. Instead to fix this requires setting in /etc/my.cnf mysql_native_password=ON which while documented is not intuitive.

Given this is going to bite a lot of people please provide a BETTER error message otherwise people will go on a wild goose chase looking for how to "load the plugin" rather than enabling it as shown above.

Reference: https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html#:~:text=MySQL%20native%20passw...

It might also be argued as indicated by Evan that you don't want to expose this detail to the user, so the ERROR 1045 would be correct.

However, in such a case the error reported the the mysqld log file could provide better information to the DBA as to what the problem actually is.