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

35 views
Skip to first unread message

Victor Pelagatti

unread,
Jul 26, 2024, 3:24:43 PMJul 26
to proxysql

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 unix-prov-mysql >>>>> unix-prov-mysql connected 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 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

# *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_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

# *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!

Nataporn Wattanakosol

unread,
Jul 31, 2024, 5:00:42 AMJul 31
to proxysql
Issue with Proxysql 2.6 and caching_sha2_password plugin with Percona 8 MySQL cluster
Reply all
Reply to author
Forward
0 new messages