Pmm3.5 & proxysql_exporter error due to caching_sha2_password auth

Percona Monitoring and Management (PMM) PMM 3.x
1

Issue: PMM proxysql_exporter monitoring not working with pxc operator proxysql scheduler.

Using pxc-operator 1.19 with ProxySQL , pxc 8.4 , monitor use auth_plugin “caching_sha2_password”
proxysql version:

image
image357×234 21.1 KB

pmm 3.5 client and server:

image
image329×144 14.3 KB

We are using proxysql scheduler and looks like the error when connecting to proxyadmin user.

pmm-client proxysql_exporter error:
level=ERROR source=exporter.go:169 msg="Error opening connection to ProxySQL" error="unexpected resp from server for caching_sha2_password, perform full authentication"" agentID=60206561-b165-4a9c-a6b1-8cc5fdd4645a component=agent-process type=proxysql_exporter

2

Using this doc to update admin-admin_credentials but changes not persistent on pods

image
image1095×685 67.4 KB

3

Hi @Lalit_Choudhary,

This is a known compatibility gap in pxc-operator 1.19. The operator now sets default_authentication_plugin="caching_sha2_password" in ProxySQL’s mysql_variables (commit 3e65dbaa, K8SPXC-1470), aligning with MySQL 8.4’s default. However, ProxySQL doesn’t support caching_sha2’s RSA key exchange for non-TLS connections, so the scramble-based fast auth fails, the server requests full RSA auth, and go-sql-driver gets the “unexpected resp” error. In operator 1.18.0 this was masked because ProxySQL defaulted to mysql_native_password and the operator forced PXC to use it when ProxySQL was enabled; both safeguards were removed in 1.19.0. I reproduced this with proxysql:2.7.1 in Docker. Switching back to mysql_native_password resolves it immediately.

Workaround: override the ProxySQL config to switch back to mysql_native_password. You need to copy the default proxysql.cnf from the operator repo and change one line in mysql_variables. The operator requires the full ProxySQL configuration, not just the changed variable.

If you deployed via Helm (helm install percona/pxc-db), add proxysql.configuration to your values file:

proxysql:
  enabled: true
  configuration: |
    datadir="/var/lib/proxysql"

    admin_variables =
    {
        # ... keep all existing admin_variables from the default proxysql.cnf
    }

    mysql_variables=
    {
        # ... keep all existing mysql_variables, but change this line:
        default_authentication_plugin="mysql_native_password"
    }

Then upgrade: helm upgrade <release> percona/pxc-db -f values.yaml

If you applied the CR directly (kubectl apply), add the same block under spec.proxysql.configuration in your PerconaXtraDBCluster manifest:

spec:
  proxysql:
    configuration: |
      datadir="/var/lib/proxysql"

      admin_variables =
      {
          # ... keep all existing admin_variables from the default proxysql.cnf
      }

      mysql_variables=
      {
          # ... keep all existing mysql_variables, but change this line:
          default_authentication_plugin="mysql_native_password"
      }

For the credential persistence issue: the operator reconciles ProxySQL configuration on pod restarts, so any changes made directly through the admin interface are overwritten. All persistent changes must go through the CR, Helm values, or Kubernetes Secrets.

References: