System user password cannot be changed

MySQL & MariaDB Percona Operator for MySQL
,
1

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

2

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
3

Hi @katajistok ,

As I can see you installed component_validate_password password validation component that is why you have passwords validation on DB level.

We have a documentation how to restore DB from the backup Providing Backups and as you can see for all system users passwords should be the same as in original cluster. E.g. if you have wrong password for pmmserver user, pmm client will not be able to connect to pmm server and pmm client sidecar will be restarted periodically and the cluster will be in “initializing” state.

So, the passwords should be the same as in original cluster.

P.S. In order to understand what is going wrong with your cluster you need to collect logs and if needed you can provide them for us and we will help to understand the root of the issue but firstly please re-check the passwords.

Thank you.

2 Likes
4

Hi,

I need to check if there was “component_validate_password” in use on original database server.

My problem was that password on “monitor” user had characters that did not work “C5syk?UY6+BeY$9N”. I was able to restore database from backup and I was also able to change the password afterwards. Although this process required some extra steps like deleting of Pods and PVC’s in this case.
What characters are allowed in system user password?

1 Like