No data for a remote database in Query analytics

Percona Monitoring and Management (PMM)
1

we are running on 2.20.

For a newly added remote monitoring host Mysql 8.0 , query analytics data is not showing up. rest all data is working as expected for the host.

Noticed the relevant QAN MySQL Perfschema Agent is in WAITING status.

performance schema is enabled while adding the system to PMM.
Perf schema is enabled in the remote database.
user has the right permissions.
connectivity is fine as we can get the remaining data for the system.

How do we troubleshoot on why the agent is not started ?

1 Like
2

Did you ‘Test Connection’ before adding the remote MySQL?

1 Like
3

I have the same issue. I did repoint the pmm2-client to a new PMM server instance, changing the password first for the pmm user in MySQL. Upgraded the client to the latest version (2.38.0-6.el7), register the node against the new PMM server, and when adding the MySQL service I got this error:

> Connection check failed: this user requires clear text authentication. If you still want to use it, please add 'allowCleartextPasswords=1' to your DSN

So I decided to use the --skip-connection-check flag, and it finally managed to add the service, but it’s giving the error stated in this post. Also, in the Instance overview, you can see:

ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled
2023_07_11_09_51_46 Cannot connect to MySQL.  Check that MySQL is running and that the options after -- are correct.

Any help on this matter would be greatly appreciated!

4

mysql_clear_password

Are you running some external authentication plugin in MySQL? LDAP? PAM?

PMM does NOT require cleartext passwords. You have something wrong in either your PMM Server install or your MySQL install, or you created the user strangely.

5

Hi Mathew!

Thanks a lot for your reply. I’ve realized that the servers in which PMM agent/client works don’t have this plugin:
validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL

However, it does exist in the ones that I’m having the issue with. The weird thing is that it was working before I decided to repoint them to the new PMM server instance, with the old PMM server one, and I don’t know if there is any way to instruct pmm-client to use the “LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1” when connecting to MySQL with the “pmm” user.

6

Also, just to be sure, I dropped the user “pmm” and recreated it with:

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost';
#or
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';
7

Hello @Dani_Castellano,
The validate_password plugin does not activate when you log in to MySQL. The validate_password plugin is only used when creating new users, or modifying the password of existing users. The validate_password plugin has nothing to do with the actual login verification process.

The pmm user you recreated looks correct. If you are still seeing this cleartext issue, then you have something else wrong on your MySQL.

Please verify: SELECT @@default_authentication_plugin; and SELECT user, plugin FROM mysql.user;

8

Hi @matthewb !

Thanks for taking the time to reply. The result of both queries is “mysql_native_password”, In the first one for the default, and in the second one for all the users, including “pmm” user.

Also, if I do a “service pmm-agent status -l” I get loads of lines similar to this one:

Jul 12 06:14:30 my-db-server pmm-agent[7075]: ERRO[2023-07-12T06:14:30.634+00:00] ts=2023-07-12T06:14:30.634Z caller=exporter.go:111 level=error msg=“Error pinging mysqld” err=“this user requires clear text authentication. If you still want to use it, please add ‘allowCleartextPasswords=1’ to your DSN” agentID=/agent_id/0c1f964e-61d1-4d0a-afc9-2dbeadfca729 component=agent-process type=mysqld_exporter

9

Hi again @matthewb !

This is just to let you know that I have managed to get it working
 Apparently, and for some unknown reasons, the MySQL instances are different in their configuration, so for the ones with this error, you need to add the MySQL service to PMM using the “-socket” parameter, so it would be like:

pmm-admin add mysql --username=pmm --password=[MyPMMPass] --query-source=perfschema --replication-set=MyReplicationSet --environment=DEV --socket=/var/lib/mysql/mysql.sock

Thanks again!

10

Hmm. There is still something highly suspicious about your setup. I’ve installed PMM many, many times, and I’ve never encountered a clear text error, nor have I ever use the socket connection.
Glad you got it working.

1 Like