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 ftptestn");
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 connectedn");
===============================================================================================
- 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!