I am about to change existing mysql_native_password
to caching_sha2_password
due to the deprecation of mysql_native_password
.
I signed in to ProxySQL and updated the following global variable, and loaded to runtime:
set mysql-default_authentication_plugin = 'caching_sha2_password';
load mysql variables to runtime;
Everything executes well. I then change the authentication of my user to use caching_sha2_password
:
alter user 'user'@'%' identified with caching_sha2_password by 'xxxxxxxxxxxxxxx';
I then re-synchronize users with the proxysql-admin
utility as below:
proxysql-admin --syncusers
I made a little PHP (v. 8.3) script in order to test that the connection works, but the connection fails with error MySQL server has gone away
. What’s strange is when I connect using the mysql
utility, my app suddenly can connect just fine without error (but I always have to connect once manually using the mysql
utility, my app can never be the first to connect after I have synced a user.
$db = mysqli_init();
$db->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, false);
$db->ssl_set(NULL, NULL, "", NULL, NULL);
$db->real_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);
I then try to connect using the mysql
client directly, and that works perfectly:
[dev@local ~]$ mysql -h127.0.0.1 -P6033 -urjoeh -p -e '\s' | grep -P 'SSL|Connection'
Enter password:
Connection id: 389425
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Connection: 127.0.0.1 via TCP/IP
ProxySQL version:
[dev@local ~]$ proxysql --version
2025-01-26 23:20:59 [INFO] Using jemalloc with MALLOC_CONF: config.xmalloc:1, lg_tcache_max:16, opt.prof_accum:1, opt.prof_leak:1, opt.lg_prof_sample:20, opt.lg_prof_interval:30, rc:0
ProxySQL version 2.7.1-percona-1.2, codename Truls
Any idea what issue could be causing this? I have not tried to load full client/key/ca pem files using PHP, but I believe that shouldn’t be necessary as ProxySQL is on the same machine.