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===============================================================================================
===============================================================================================
===============================================================================================
===============================================================================================
===============================================================================================
On /var/lib/proxysql/proxysql.log, I've got: 2024-07-26 11:53:12 [INFO] Received load mysql users to runtime command 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_CONNECTING_CLIENT___STATE_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
===============================================================================================
===============================================================================================
-- If I update the user's password, I need to connect again using the credentials and then test.php executes successfully.
===============================================================================================
===============================================================================================
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_CONNECTING_CLIENT___STATE_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
===============================================================================================
===============================================================================================
Do you have any clue about how to fix this issue?
Thank you very much!