Bug #98255 MySQL Router (8.0.19) appears to be forcing all connections to use sha_256_passw
Submitted: 16 Jan 15:00 Modified: 13 Feb 0:41
Reporter: IGG t Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Router Severity:S2 (Serious)
Version:8.0.19 OS:Microsoft Windows (10)
Assigned to: MySQL Verification Team CPU Architecture:x86
Tags: caching_sha2_password, router

[16 Jan 15:00] IGG t
Description:
MySQL Router (8.0.19) appears to be forcing all connections to use sha_256_password authentication even if the database itself has specified a different default (mysql_native_password).

How to repeat:
Set up a Replicaset (same issue found with an Innodb Cluster) consisting of three nodes (all MySQL 8.0.19). And then connected MySQL Router.

All three databases are set up with:

select @@global.default_authentication_plugin
+----------------------------------------+
| @@global.default_authentication_plugin |
+----------------------------------------+
| mysql_native_password                  |
+----------------------------------------+

I add a user:
CREATE USER 'test'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';

+------+------+-----------------------+
| user | host | plugin                |
+------+------+-----------------------+
| test | %    | mysql_native_password |
+------+------+-----------------------+

I then try and connect to it using the 'test' user via the router on port 6446 using an old PHP connection that doesn't support caching_sha2_password authentication, and I get the error 
"The server requested authentication method unknown to the client [sha256_password]"

However, if I instead try and connect direct to the database on port 3306, it works fine.

With MySQL Router 8.0.18 and MySQL 8.0.18 it worked fine.

Suggested fix:
MySQL Router should respect the database default authentication when trying to establish a connection.
[22 Jan 4:45] MySQL Verification Team
I'm verifying this as a bug. Thanks for your report. Not sure why this changed in 8.0.19 and while those old unsecure passwords should eventually die I don't see the deprecation nor documentation about this change anywhere.

all best
[3 Feb 6:28] to vu
Hello.
I try to simulate this on Centos & Windows OS but did not work...
Can someone confirmed this bug?
Thanks
[3 Feb 12:00] IGG t
I would suggest it can be closed. I have just gone through my initial set up and tested it again, and have also been unable to recreate it suggesting there must have been some error in my initial setup.
[3 Feb 12:13] MySQL Verification Team
I could not reproduce this on linux (centos, rhel, debian) but I did reproduce this on Windows10 destop. Will recheck today and if does not reproduce on Win10 with fresh install I'll close it
[6 Feb 10:31] IGG t
The only way I have been able to recreate it since is by restricting the 'host'. If I use 'root'@'127.0.0.1' I get the original error.

If I change it to 'root'@'%' I can connect.

Checking the 'current_user()' shows the connection is from 'root'@'fe80::90fb:1c8e:363f:f3e7%7' which is why it isn't matching the '127.0.0.1' host.

All my instances of database and router are running on my windows 10 laptop, so I assumed they would all work with 127.0.0.1.
[13 Feb 0:41] MySQL Verification Team
After further analysis, I conclude this is not a bug.