Implementing caching_sha2_password - Works in app after manual connection using MySQL client

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.

If you’re going to test with the mysql client “on the same machine”, and use SSL, then why would you not also use SSL with your PHP test? What you show is not an apples-to-apples comparison.

Test your PHP script going directly to MySQL. Verify that works. Then, changing only the DB_HOST/DB_PORT, test script to ProxySQL. Check ProxySQL’s error log. Verify runtime_mysql_users in ProxySQL is correct with regards to the user’s password.

Turns out the self-signed certificates failed to verify due to a mismatched hostname. I re-generated new certificates, and the connection is now successful.

I assume there is no support to keep some users on mysql_native_password and others on caching_sha2_password, as the mysql-default_authentication_plugin must be explicitly set for the initial handshake to be successful?

Yes there is. If you look at proxysql docs, the variable mysql-default_authentication_plugin is exactly that, the default, not the only. The docs go on to explain how mysql handshake first offers the default, but can then switch to another plugin.
I recommend you read over the docs; there are several examples explained.

1 Like