Issue with Proxysql 2.6 and caching_sha2_password plugin with Percona 8 MySQL cluster

I'm struggiling since 4 days with a strange issue with my ProxySQL+MySQL cluster. I've attached all information that seems relevant to me. I read documentation about how to import 'caching_sha2_password' from MySQL to ProxySQL (https://proxysql.com/documentation/Password-management/#import-caching_sha2_passwords) but even that worked, when I try to connect using a PHP script, it failed. After connect to ProxySQL using MySQL cliente (mysql) and I execute PHP script again, it works!

Hope anyone can help. Below you have my secuencial path....

=============================================================================================== OS: # Red Hat Enterprise Linux release 9.4 (Plow) - Oracle Linux 9

MySQL cluster: 3 nodes (1 master, 2 slaves) replicating MySQL version: Server version: 8.0.36-28 Percona Server (GPL), Release 28, Revision 47601f19

ProxySQL version: ProxySQL version 2.6.3-percona-1.1, codename Truls

global variables:

# mysql -uadmin -p -h 0.0.0.0 -P6032 --prompt='Admin> ' -s -e 'show global variables' | grep -e mysql-default_authentication -e mysql-have_ssl 
Enter password: 
mysql-default_authentication_plugin caching_sha2_password mysql-have_ssl true

Get user's password from MySQL

mysql> select hex(authentication_string) from user where user='ftptest'; 
+----------------------------------------------------------------------------------------------------------------------------------------------+ 
| hex(authentication_string) | 
+----------------------------------------------------------------------------------------------------------------------------------------------+ 
| 244124303035243E257A64062E3C10397A582F1345503C6D2A66174865582E75797163574B4F4C58322E5A665A645A7A564A5476507A612F304648726E646943544334324B32 | 
+----------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

Insert username's password into ProxySQL, load mysql users to runtime and save them to disk:

Admin> insert into mysql_users (username, password, active, use_ssl) values ('ftptest', UNHEX('244124303035243E257A64062E3C10397A582F1345503C6D2A66174865582E75797163574B4F4C58322E5A665A645A7A564A5476507A612F304648726E646943544334324B32'), 1, 0); 
Admin> load mysql users to runtime; 
Admin> save mysql users to disk;

Create a 'test.php' file and connect using 'ftptest' credentials using ProxySQL

# cat test.php 
#!/usr/bin/php 
<?php 
print("Connect with ftptest\n"); 
if( ! $c = new PDO( "mysql:host=10.200.72.135;dbname=mysql;charset=utf8;", "ftptest", "mynewpassword" ) ) throw new Exception("Can't open MySQL"); 
print(">>>>> ftptest connected\n");

Execute test.php and FAIL!

# php test.php 
Connect with ftptest PHP 
Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2006] MySQL server has gone away in /root/test.php:7 
Stack trace: #0 
/root/test.php(7): PDO->__construct() #1 {main} thrown in /root/test.php on line 7

On /var/lib/proxysql/proxysql.log, I've got:

2024-07-26 11:53:12 [INFO] Received load mysql users to runtime command0 2024-07-26 11:53:12 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x3F78E03252E9AD09', with epoch '1722009192' 2024-07-26 11:53:16 [INFO] Received save mysql users to disk command 2024-07-26 11:53:27 MySQL_Protocol.cpp:1470:PPHR_1(): [ERROR] User 'ftptest'@'10.200.72.135' is disconnecting during switch auth 2024-07-26 11:53:27 MySQL_Session.cpp:5779:handler_status_CONNECTINGCLIENTSTATE_SERVER_HANDSHAKE_WrongCredentials(): [ERROR] ProxySQL Error: Access denied for user 'ftptest'@'10.200.72.135' (using password: YES)

Connect with the same configuration using 'mysql' client

# mysql -u ftptest -p -h 10.200.72.135 -s 
Enter password: mynewpassword 
mysql>

Execute test.php and SUCCESS!

# php test.php 
Connect with ftptest 
>>>>> ftptest connected

If I update the user's password, I need to connect again using the credentials and then test.php executes successfully.

Update user's password in MySQL master

mysql> alter user 'ftptest' identified by 'mypassword'; 
Query OK, 0 rows affected (0.01 sec) 
mysql> select hex(authentication_string) from user where user='ftptest'; 
+----------------------------------------------------------------------------------------------------------------------------------------------+ 
| hex(authentication_string) | 
+----------------------------------------------------------------------------------------------------------------------------------------------+ 
| 24412430303524564D58543350161E30197553035E39140230384E624B4F527567667A6B314249333443355754362E6B567555357350376951576F566467374E625637686233 | 
+----------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

Update username's password in ProxySQL

Admin> update mysql_users set password=UNHEX('24412430303524564D58543350161E30197553035E39140230384E624B4F527567667A6B314249333443355754362E6B567555357350376951576F566467374E625637686233') where username = 'ftptest'; 
Admin> load mysql users to runtime; 
Admin> save mysql users to disk;

Execute test.php and FAIL!

# php test.php 
Connect with ftptest 
PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2006] MySQL server has gone away in /root/test.php:7 
Stack trace: #0 
/root/test.php(7): PDO->__construct() #1 {main} thrown in /root/test.php on line 7

On /var/lib/proxysql/proxysql.log, I've got:

2024-07-26 13:40:12 [INFO] Received load mysql users to runtime command 2024-07-26 13:40:12 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x88D93F9645792B23', with epoch '1722015612' 2024-07-26 13:40:14 [INFO] Received save mysql users to disk command 2024-07-26 13:25:12 MySQL_Protocol.cpp:1470:PPHR_1(): [ERROR] User 'ftptest'@'10.200.72.135' is disconnecting during switch auth 2024-07-26 13:25:12 MySQL_Session.cpp:5779:handler_status_CONNECTINGCLIENTSTATE_SERVER_HANDSHAKE_WrongCredentials(): [ERROR] ProxySQL Error: Access denied for user 'ftptest'@'10.200.72.135' (using password: YES)

Connect with the same credentials using 'mysql' client

# mysql -u ftptest -p -h 10.200.72.135 -s 
Enter password: mypassword 
mysql>

Execute test.php and SUCCESS!

# php test.php 
Connect with ftptest 
>>>>> ftptest connected

Do you have any clue about how to fix this issue?

Thank you very much!

Hi @ItsReddi, @vpelagatti,

let me try to help with this issue. I think that the problem you might be having is that you are trying to create non-SSL connections while using chaching_sha2_password as authentication method. This is not possible, since caching_sha2_password requires a secure channel for the password to be shared, you can see ProxySQL interaction with a similar script to yours:

output - failure as in your script:

Connect with ftptest
PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000] [2006] MySQL server has gone away in /home/.../script.php:18
Stack trace:
#0 /home/.../4596/script.php(18): PDO->__construct()
#1 {main}
  thrown in /home/.../4596/script.php on line 18

And in this traffic capture, you can see that ProxySQL denies the access when the public key is requested in an insecure channel by the client: PHP-ProxySQL-No-SSL.pcapng.zip.

Now, the situation changes once you perform this action Connect with the same credentials using 'mysql' client this is because the MySQL client will establish an SSL connection with ProxySQL, and after that interaction ProxySQL will remember internally password in clear-text, so no more full-authentication from caching_sha2 would be required, and SSL (a secure channel) won't be a requirement for the connection establishment.

Modifying your PHP script into something like the following, you should be providing a secure channel (SSL) for performing the connection, and thus, authentication should work also the first time, when secrets are required to be shared:

#!/usr/bin/php 
<?php

print("Connect with ftptest\n"); 

$ssl_options = array(
    PDO::MYSQL_ATTR_SSL_CA => '',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
);

$dsn = "mysql:host=127.0.0.1;port=6033;charset=utf8mb4";

// Creds
$user = "sbtest1";
$pass = "sbtest1";

$con = new PDO($dsn, $user, $pass);

print(">>>>> ftptest connected\n");

?>

This requirement is also expressed in a couple of points of the documentation for caching_sha2_password. Especially in this two places:

I hope this helps to clarify your doubts, please feel free to close the issue if that is the case.

Regards, Javier.

sysown / proxysql

Issue with Proxysql 2.6 and caching_sha2_password plugin with Percona 8 MySQL cluster #4596