# Implementing caching\_sha2\_password - Works in app after manual connection using MySQL client

**URL:** <https://forums.percona.com/t/implementing-caching-sha2-password-works-in-app-after-manual-connection-using-mysql-client/36348>\
**Category:** ProxySQL\
**Tags:** percona\
**Created:** [January 26, 2025, 11:39pm UTC](https://forums.percona.com/t/implementing-caching-sha2-password-works-in-app-after-manual-connection-using-mysql-client/36348 "2025-01-26T23:39:20Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rjoeh](https://avatars.discourse-cdn.com/v4/letter/r/57b2e6/32.png) [@Rjoeh](https://forums.percona.com/u/Rjoeh)\
**Post date:** [January 26, 2025, 11:39pm UTC](https://forums.percona.com/t/implementing-caching-sha2-password-works-in-app-after-manual-connection-using-mysql-client/36348/1 "2025-01-26T23:39:20Z")

</div>

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:

```sql
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`:

```sql
alter user 'user'@'%' identified with caching_sha2_password by 'xxxxxxxxxxxxxxx';

```

I then re-synchronize users with the `proxysql-admin` utility as below:

```console
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.

```php
$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:

```console
[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:

```console
[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.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [January 27, 2025, 3:17am UTC](https://forums.percona.com/t/implementing-caching-sha2-password-works-in-app-after-manual-connection-using-mysql-client/36348/2 "2025-01-27T03:17:25Z")

</div>

> [@Rjoeh](#):
>
> 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.

---

<div class="post-metadata">

**Author:** ![Rjoeh](https://avatars.discourse-cdn.com/v4/letter/r/57b2e6/32.png) [@Rjoeh](https://forums.percona.com/u/Rjoeh)\
**Post date:** [January 27, 2025, 8:52pm UTC](https://forums.percona.com/t/implementing-caching-sha2-password-works-in-app-after-manual-connection-using-mysql-client/36348/3 "2025-01-27T20:52:01Z")

</div>

> [@matthewb](#):
>
> Why would you not also use SSL with your PHP test? What you show is not an apples-to-apples comparison.

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?

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [January 27, 2025, 9:56pm UTC](https://forums.percona.com/t/implementing-caching-sha2-password-works-in-app-after-manual-connection-using-mysql-client/36348/4 "2025-01-27T21:56:19Z")

</div>

> [@Rjoeh](#):
>
> 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](https://proxysql.com/documentation/authentication-methods/); there are several examples explained.