System user password cannot be changed

I’m testing XtraDB with one use case.

I have restored database from S3 bucket. The database size is about 250GB. PMM Client - Server is used.
The problem is that PMM Server shows only metrics from node_exporter, not from mysqld_exporter.

PMM Client shows these error messages:
INFO[2022-01-12T07:20:00.004+00:00] Sending status: WAITING. agentID=/agent_id/d443be78-f455-4e2e-ac4b-0719d8903104 component=agent-builtin type=qan_mysql_perfschema_agent
ERRO[2022-01-12T07:20:01.756+00:00] failed to query events_statements_history: Error 1045: Access denied for user ‘monitor’@‘wft-qa-haproxy-pxc-0.wft-qa-haproxy-pxc.dbaas-mysql-wft-qa-hapro’ (using password: YES) agentID=/agent_id/d443be78-f455-4e2e-ac4b-0719d8903104 component=agent-builtin type=qan_mysql_perfschema_agent

PMM Client within pxc Pod:
[production@dbaasjump002 04haproxy]$ kubectl exec -it wft-qa-haproxy-pxc-0 -c pmm-client – bash
bash-4.2$ pmm-admin status
Agent ID: /agent_id/9a761555-e21d-42a3-beb1-d7ebd6ac4a7f
Node ID : /node_id/5f73ed85-ee8c-4d58-98c5-f244996462dd

PMM Server:
URL : https://xyz:4444/
Version: 2.25.0

PMM Client:
Connected : true
Time drift : -3.821367819s
Latency : 559.716µs
pmm-admin version: 2.23.0
pmm-agent version: 2.23.0
Agents:
/agent_id/4580b15b-21fc-4037-8a8a-1c1ae15ec014 node_exporter Running
/agent_id/95ca893a-89c3-4d0d-9441-957806a02716 mysqld_exporter Running
/agent_id/babd6e06-6557-4b01-adde-4edac35458a3 vmagent Running
/agent_id/d443be78-f455-4e2e-ac4b-0719d8903104 mysql_perfschema_agent Waiting

Backup that was restored includes “monitor” user account with password “C5syk?UY6+BeY$9N”. I have verified that the password is the problem. I created an another environment, where I used the same password for this “monitor” system user and encountered the same situation. On this case I was able to change “monitor” user password with the guide Users.

But the same method does not work with the environment where backup is restored on. And if I’m trying to change “monitor” user password with cr.yaml or from database (mysql alter user…) then the whole cluster goes sideways.

Example of trying to change password:
[production@dbaasjump002 04haproxy]$ kubectl get pxc
NAME ENDPOINT STATUS PXC PROXYSQL HAPROXY AGE
wft-qa-haproxy wft-qa-haproxy-haproxy.dbaas-mysql-wft-qa-haproxy ready 3 3 10h

[production@dbaasjump002 04haproxy]$ cat secrets.yaml
apiVersion: v1
kind: Secret
metadata:
name: my-cluster-secrets
type: Opaque
stringData:
root: xyz
xtrabackup: “xyz”
monitor: “C5syk?UY6+BeY$9N”

[production@dbaasjump002 04haproxy]$ echo -n ‘newPassword’ | base64
bmV3UGFzc3dvcmQ=
[production@dbaasjump002 04haproxy]$ kubectl edit secret my-cluster-secrets
secret/my-cluster-secrets edited
[production@dbaasjump002 04haproxy]$ kubectl get pods
NAME READY STATUS RESTARTS AGE
percona-xtradb-cluster-operator-f4755645c-q7wlx 1/1 Running 0 10h
restore-job-wft-workflow-database-restore-wft-qa-haproxy-7gs2c 0/1 Completed 0 10h
wft-qa-haproxy-haproxy-0 3/3 Running 0 8h
wft-qa-haproxy-haproxy-1 3/3 Running 0 8h
wft-qa-haproxy-haproxy-2 3/3 Running 0 8h
wft-qa-haproxy-pxc-0 4/4 Running 0 8h
wft-qa-haproxy-pxc-1 4/4 Running 0 8h
wft-qa-haproxy-pxc-2 4/4 Running 0 7h15m

[production@dbaasjump002 04haproxy]$ kubectl exec wft-qa-haproxy-pxc-0 -c pxc – mysql -umonitor -pnewPassword -e ‘show databases;’
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user ‘monitor’@‘localhost’ (using password: YES)
command terminated with exit code 1

Old password still works (not expected):
[production@dbaasjump002 04haproxy]$ kubectl exec wft-qa-haproxy-pxc-0 -c pxc – mysql -umonitor -p’C5syk?UY6+BeY$9N’ -e ‘show databases;’
mysql: [Warning] Using a password on the command line interface can be insecure.
Database
PERCONA_SCHEMA
harms
information_schema

Is there some restrictions on system user passwords? How to fix this case without doing all the things again (change monitor user password on source database, take backup, re-create XtraDB cluster, restore cluster …)?

And btw:
[production@dbaasjump002 04haproxy]$ kubectl patch secret/my-cluster-secrets -p ‘{“data”:{“monitor”: ‘$(echo -n newPassword | base64)’}}’
Error from server (BadRequest): invalid character ‘b’ looking for beginning of value

I tried to reproduce this situation and got error message:
Last Transition Time: 2022-01-12T16:23:30Z
Message: manage sys users: update sys users pass: update password: Error 1819: Your password does not satisfy the current policy requirements
Reason: ErrorReconcile
Status: True

Then I invented a little complex password and tried again. For some minutes it showed like the process started properly. But after hours the situation was that cluster was on “initializing” state. Pxc Pods were on “Running” state, but database was only working on 1/3 nodes. Monitor system user password was thought changed.

Now I’m trying to fix the situation, but there is no guide on this kind of situation. So I tried to delete Pods (where database is not working) but it didn’t help. I cannot see anything specific related to Galera state.

1 Like