MySQL Bugs: #110161: Old clients (non-ssl) cannot connect through MySQL Router

Bug #110161	Old clients (non-ssl) cannot connect through MySQL Router
Submitted:	22 Feb 2023 6:33	Modified:	19 Aug 2023 6:21
Reporter:	Aleksei Faians	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Router	Severity:	S2 (Serious)
Version:	8.0.32	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
After upgrading InnoDB cluster to version 8.0.32 we noticed that old clients could no longer connect via MySQL Router. Some clients were able to connect after forcing ssl mode, but that was not an acceptable solution. At the same time, old clients could connect normally directly to cluster nodes (bypassing MySQL Router). We started experimenting with MySQL Router setting. Here's what we tried and the results:

client_ssl_mode = PREFERRED
server_ssl_mode = AS_CLIENT
Result: FAIL (old clients cannot connect without ssl)

client_ssl_mode = PASSTHROUGH
server_ssl_mode = AS_CLIENT
Result: FAIL (old clients cannot connect without ssl)

client_ssl_mode = PREFERRED
server_ssl_mode = PREFERRED
Result: PARTIAL SUCCESS (most old clients can connect without ssl, but some old Perl script fails with authentication errors)

Please note that everything worked fine with version 8.0.31.

How to repeat:
1. Set up InnoDB cluster in Single-Primary mode
2. Set up MySQL Router
3. Create test user and DB
4. Try connecting with older client, i.e. mysql from MariaDB 10.6, through MySQL Router

Suggested fix:
Implement a *REAL* passthrough mode in MySQL Router. I don't know why, but current implementation does not just forward everything from client to server,  because clients failed to connect through MySQL Router in PASSTHROUGH mode but they could connect to the same MySQL Server directly.

Hi,
Thank you for the report.

Can you share a list of those "old" clients you had issues with?

thanks

Client list:
1. `mysql` from MariaDB 10.6.8
2. A very old Perl script (Perl 5.16.2, compiled on RHEL6 from sources on 26 November 2012)

The Perl script doesn't work even with both client_ssl_mode and server_ssl_mode set to PREFERRED, most probably because it doesn't support modern authentication scheme.

Hello Aleksei, 

Could you provide the authentication method used by the user(s) and the error you get on the client when the connection fails ?

Thank you.

Let's assume that MySQL Router is running on localhost with default settings and connected to a InnoDB cluster node with IP address 10.41.16.14.

1. MySQL> create user 'test'@'%' identified with mysql_native_password by 'test';
2. $ MYSQL_PWD=test mysql -h 127.0.0.1 -P 6446 -u test     # connection fails
3. $ MYSQL_PWD=test mysql -h 10.41.16.14 -P 3306 -u test   # connection establishes

Expected result on step 2: connection establishes
Actual result on step 2: ERROR 2061 (HY000): Couldn't read RSA public key from server

Posted by developer:
 
Looks like a duplicate of Bug#35015376

Posted by developer:
 
Added the following note to the MySQL Router 8.0.33 release notes:
	
 It was not possible to connect through MySQL Router using older PHP clients if the MySQL account
 used caching_sha2_password and MySQL Router was configured to use PASSTHROUGH/AS_CLIENT or PREFERRED/AS_CLIENT options.

This bug is back in MySQL Router 8.0.34. Please reopen.

Posted by developer:
 
Reopened as Bug#112167