# PXC 8.0.36-28-1 with caching\_sha2\_password and different hash **URL:** \ **Category:** Percona XtraDB Cluster 8.x\ **Tags:** percona\ **Created:** [July 9, 2024, 1:02pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523 "2024-07-09T13:02:06Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 9, 2024, 1:02pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/1 "2024-07-09T13:02:06Z")
There is a cluster 8.0.36-28.1 of 3 nodes. By default, the `caching_sha2_password` plugin is there. The problem is that when creating a new user, each node has its own password hash. What it looks like: 1. create a user on 1 node: `CREATE USER 'sha2user'@'%' IDENTIFIED BY 'qwerty';` 2. check the hash on each node: 1 node: ```sql mysql> select user,host,plugin,authentication_string from mysql.user where user = 'sha2user'; +----------+------+-----------------------+------------------------------------------------------------------------+ | user | host | plugin | authentication_string | +----------+------+-----------------------+------------------------------------------------------------------------+ | sha2user | % | caching_sha2_password | $A$005$ Q~1>{a!s%riRZl?"pjsNMud0lsc8AMRfcuyLoA93GuBUun.8gLcS5qi015C | +----------+------+-----------------------+------------------------------------------------------------------------+ 1 row in set (0,00 sec) ``` 2 node: ```sql mysql> select user,host,plugin,authentication_string from mysql.user where user = 'sha2user'; +----------+------+-----------------------+------------------------------------------------------------------------+ | user | host | plugin | authentication_string | +----------+------+-----------------------+------------------------------------------------------------------------+ | sha2user | % | caching_sha2_password | $A$005$d3W\-zx▒Y!H)mmf90LtOdN1mA0gLiFVt5NQg/pRnP7g74i64m0WdXd/ | +----------+------+-----------------------+------------------------------------------------------------------------+ 1 row in set (0,00 sec) ``` 3 node: ```sql mysql> select user,host,plugin,authentication_string from mysql.user where user = 'sha2user'; +----------+------+-----------------------+------------------------------------------------------------------------+ | user | host | plugin | authentication_string | +----------+------+-----------------------+------------------------------------------------------------------------+ | sha2user | % | caching_sha2_password | $A$005$T1Cyt"Z0^ !x+BlrMLoI2txdrMKPgI7mNJ1w15qAAzSDaLq0wlgAsbe7 | +----------+------+-----------------------+------------------------------------------------------------------------+ 1 row in set (0,00 sec) ``` 1. caching\_sha variables: ```sql mysql> show variables like 'caching_sha2%'; +----------------------------------------------+-----------------+ | Variable_name | Value | +----------------------------------------------+-----------------+ | caching_sha2_password_auto_generate_rsa_keys | ON | | caching_sha2_password_digest_rounds | 5000 | | caching_sha2_password_private_key_path | private_key.pem | | caching_sha2_password_public_key_path | public_key.pem | +----------------------------------------------+-----------------+ 4 rows in set (0,01 sec) ``` The PCA key is different: 1 node: ```sql ***************************1. row*************************** Variable_name: Caching_sha2_password_rsa_public_key Value: -----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAx4OmmTwJjTonPNwTn81h Qx21eO0LtqPOoXXvEfLF1V1V9TfTNdhH97ls/nI1oBTm7srG1vwC0gThII9sgIdG DGKD7ECReTUecPe33NOtKAZ13w+82asWhVWFlOSISjMsXEmRPO0jgfS4VG3EIl6A eChmDaD/ktFvz9yQsmEJHs2YgwOOHbEIIvy+JDoZKCB3awUvV0JvFpXJcTISUsBD cj+UZM3OQ3ctLrMgyYZeuDlKuf08TrtULxmW66Dgslb0B8i5Wcbl6qhRlQwWdQFS abOA5G3gaSblKzZuUVU/AmNSzh9T9UQyFqV+iGf89QMn/aTsIV6IoFbYCsh5IrCZ LwIDAQAB -----END PUBLIC KEY----- ``` 2 node: ```sql mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G ***************************1. row*************************** Variable_name: Caching_sha2_password_rsa_public_key Value: -----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA5jjtmoRCSOJTvhY4Gt5q +EpUdD4+LhXpTKkQo5YdjnXMEnw9SRgIROGUEfIFiSd/kY+RhfL2w1k/2wB6LkmD 5EIIkKM4hdJ3mzxU2xbT1TAAjsfDCJOZdl+lR5MUkO7cRQOQWf7s8QtLOfuzwovs 4Ag4h2jzBEGfvSt3clBekd0c39F922/rFJZuxCihOFlI3yYtzBwXUY7yGvCDetUg AY1l2zR1bwOH9RWL2hlHHDRE52xMrAnITrcHdGwdjwcGpRnsP9EpI1tLdJYc91Eb 7jm4STQfxsj7c5PTCUjyuPsDDjHxLxSiP3Z3YC6XiCfNtxcBG7NGHr+K7W025jHf JQIDAQAB -----END PUBLIC KEY----- ``` 3 node: ```sql mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G ***************************1. row*************************** Variable_name: Caching_sha2_password_rsa_public_key Value: -----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAyGgKBQZNAvG/cVr6F/LK olowOZbw1BkGfHL6QWWxPVIPOjLRkIhpS2wfPTy1AzJ1m/qFnAQaN4ojzjr4TojA DGBVrr39JhkkCIxp+YwfJ29p8+R98kJJazCd1KG+VhHaBXeS0JvrsAffsSfT39NB y229E0Eb8z0PRrWF6PsjaGc9Iev9LxA8OxvXBBL54IM3XXymgYgNrgWXBofK4SpU 6b8vfCxasuWoDeMnavOdUMM4tbmspO1HrTw8jIZFOd3UBICd/nLwnEy0sHbn+D/J 7ZwACygsM20YP1wSuY2qSUwfApnzzmzSXsQk1+xVk/cG0cY06SUvTQXuEHxkggE1 CwIDAQAB -----END PUBLIC KEY----- ``` ProxySQL is installed in front of the nodes and the `mysql_users` table contains users with a password hash (`password` field). This is a problem, there must be one hash. I believe this is due to the default `caching_sha2_password_auto_generate_rsa_keys=ON` variable. Which, according to the [documentation](https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-rsa-files-using-mysql.html), automatically generates a PCA key pair on the server. We recently had an update from version 8.0.33-25.1 to 8.0.36-28 (04/03/2024). All users there still use the `mysql_native_password` plugin, which is outdated and seems to have been removed in version 9.0. How to safely and correctly update users to the `caching_sha2_password` plugin and solve the problem with different hash and PCA keys? Will it be enough to take the already generated pair of PCA keys and rewrite them on nodes 2 and 3? And set the variable `caching_sha2_password_auto_generate_rsa_keys=OFF`? We don’t use SSL. We need to solve this before updating on the production environment, so as not to break the operation of the cluster. I’m currently testing this in a test environment. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 9, 2024, 1:58pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/2 "2024-07-09T13:58:00Z")
There is an entry in the logs: ```auto 2024-07-09T12:23:16.115975Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2024-07-09T12:23:16.116011Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2024-07-09T12:23:16.116264Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory. 2024-07-09T12:23:16.116457Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory. ``` This is even on a clean install with bootstrap. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 9, 2024, 2:29pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/3 "2024-07-09T14:29:03Z")
I found an article on ProxySQL [https://www.percona.com/blog/caching\_sha2\_password-support-for-proxysql-is-finally-available/](https://www.percona.com/blog/caching_sha2_password-support-for-proxysql-is-finally-available/) with support for `caching_sha2_password`. ---
**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:** [July 9, 2024, 2:33pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/4 "2024-07-09T14:33:44Z")
> [@shigaev.s](#): > > seems to have been removed in version 9.0. Yes. Officially native\_password is _deprecated_ in 8.0 and fully removed in 9.0. > [@shigaev.s](#): > > Will it be enough to take the already generated pair of PCA keys and rewrite them on nodes 2 and 3? And set the variable `caching_sha2_password_auto_generate_rsa_keys=OFF`? Yes, you can do that. > [@shigaev.s](#): > > We don’t use SSL. You should 😉 ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 9, 2024, 2:43pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/5 "2024-07-09T14:43:28Z")
Thanks for your feedback! I’ll try updating the PCA key pair and check the changes on the ProxySQL side. I’ll be back later. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 9, 2024, 3:21pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/6 "2024-07-09T15:21:21Z")
I tried replacing a pair of PCA keys and restarted each node, now they are the same on all nodes. Conclusion `SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G` is the same. I created a new user, but the hash is still different. ```sql mysql> CREATE USER 'test.sha2'@'localhost' IDENTIFIED BY 'qwerty'; Query OK, 0 rows affected (0,02 sec) ``` 1 node: ```sql mysql> select user,host,plugin,authentication_string from mysql.user where user = 'test.sha2'; +-----------+-----------+-----------------------+------------------------------------------------------------------------+ | user | host | plugin | authentication_string | +-----------+-----------+-----------------------+------------------------------------------------------------------------+ | test.sha2 | localhost | caching_sha2_password | $A$005$Cl"[,}! 5}z:2eK"p9KiJlpJvzs2TrMselZb8481HMWdzKs8NnsOVxzK3o4 | +-----------+-----------+-----------------------+------------------------------------------------------------------------+ 1 row in set (0,00 sec) ``` 2 node: ```sql mysql> select user,host,plugin,authentication_string from mysql.user where user = 'test.sha2'; +-----------+-----------+-----------------------+------------------------------------------------------------------------+ | user | host | plugin | authentication_string | +-----------+-----------+-----------------------+------------------------------------------------------------------------+ W2K6TSffu.N9KaRsTdX7Qi.k.IsC6eVEtvkwNd.A4TD |rd | $A$005$!L79eH5b]fOrO- +-----------+-----------+-----------------------+------------------------------------------------------------------------+ 1 row in set (0,00 sec) ``` 3 node: ```sql mysql> select user,host,plugin,authentication_string from mysql.user where user = 'test.sha2'; +-----------+-----------+-----------------------+------------------------------------------------------------------------+ | user | host | plugin | authentication_string | +-----------+-----------+-----------------------+------------------------------------------------------------------------+ | test.sha2 | localhost | caching_sha2_password | $A$005$8_ =JyTKd!&I\▒- gcxGst40rW90LB5tsHoxc8PLRxXrJ4cDeG7aHgrvNbA | +-----------+-----------+-----------------------+------------------------------------------------------------------------+ 1 row in set (0,00 sec) ``` What’s wrong? Maybe something else needs to be changed? ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 10, 2024, 8:26am UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/7 "2024-07-10T08:26:45Z")
Also, with the same `.pem` on the nodes, the hash is different. Could it be some kind of salt? But then it is not possible to work with such a plugin in a cluster together with ProxySQL. If you stay on `mysql_native_password`, then this will make further updating impossible. ---
**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:** [July 10, 2024, 2:23pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/8 "2024-07-10T14:23:54Z")
Are you sure this is actually an issue? Have you tested this? I run PXC+ProxySQL in our training labs all the time, and have never come across an issue like this before. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 10, 2024, 2:57pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/9 "2024-07-10T14:57:07Z")
How does it work for you? Is it the same with different hash of the user? How do you add it to ProxySQL? This behavior occurs on an existing cluster and a test cluster with a clean deployment. Judging by the documentation, the client libraries used in PHP do not have support for the `caching_sha2_password` plugin, which negates the transition to this plugin. > PHP: the PDO\_MySQL and ext/mysqli extensions do not support `caching_sha2_password` . In addition, when used with PHP versions before 7.1.16 and PHP 7.2 before 7.2.4, they fail to connect with `default_authentication_plugin=caching_sha2_password` even if `caching_sha2_password` is not used. ---
**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:** [July 10, 2024, 3:09pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/10 "2024-07-10T15:09:41Z")
> [@shigaev.s](#): > > Is it the same with different hash of the user? I never looked that closely because it’s never been an issue. PHP 7.4 supports caching\_sha2: [mysql - Does mysqli have support for caching\_sha2\_password in PHP 7.4? - Stack Overflow](https://stackoverflow.com/a/59238483/1461007) Additionally, PHP 7.2 EOL’d over 6 years ago, and 7.4 EOL’d almost 5 years ago. You shouldn’t be on such old versions. 8.3 is the current PHP. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 10, 2024, 3:16pm UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/11 "2024-07-10T15:16:12Z")
Yes, we have PHP 8.2 and 8.3 versions. In the ProxySQL documentation for `caching_sha2_password` you need to add a hash, which is taken from the users table. But each node has a different hash. How did you add in this case? ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 12, 2024, 9:40am UTC](https://forums.percona.com/t/pxc-8-0-36-28-1-with-caching-sha2-password-and-different-hash/31523/12 "2024-07-12T09:40:36Z")
Hello! Deployed a new cluster along with a ProxySQL. Created a new user with `caching_sha2_password`. On the ProxySQL I set the variable `mysql-default_authentication_plugin = 'caching_sha2_password';` - without setting the variable does not work for authorization with `caching_sha2_password` Added a user to the proxy using the `HEX/UNHEX` function. I checked the connection under a new user on each node, and then through a proxy. It works. No problem. I was confused by the different hashes, but it turned out to be a salt, which does not affect authorization. Those. you can take a hash from any node or even salt + password and add it to the ProxySQL and it works. Sorry for the trouble and thanks for the feedback.