Add new mysql service from client (pmm-admin add) failed ! Connection check failed

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

Hello,
When I tried to add mysql service to pmm server from my client with pmm-admin add, I’ve got this error message:
pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass test X.X.X.X:3306

“error”: “Connection check failed: commands out of sync. Did you run multiple statements at once?.”,
“code”: 9,
“message”: “Connection check failed: commands out of sync. Did you run multiple statements at once?.”

pmm-admin status tells me it’s OK
pmm-admin status
Agent ID : /agent_id/4a17d5b3-7b9d-49bc-8c12-35ce03585df6
Node ID : /node_id/c937751c-5f42-49c7-a6e4-513d69deef05
Node name: mariadb1

PMM Server:
URL : https://9.101.52.28:31654/
Version: 2.37.0

PMM Client:
Connected : true
Time drift : 4.583847ms
Latency : 729.596µs
Connection uptime: 100
pmm-admin version: 2.37.1
pmm-agent version: 2.37.1
Agents:
/agent_id/80c6f300-366e-4578-b228-7913706516c9 vmagent Running 42000
/agent_id/abdf322a-9e93-48b6-9f34-7906471811d3 node_exporter Running 42001

thanks in advance for your help.
J

2

Hi @Jeromed,
is it possible that MySQL server restarted when you were trying to add it? Is it reproducable? Have you tried again?
Could you please provide information about your MySQL server? Version, topology and etc.

3

Hello nurlan,
My Mariadb server was up and running when I tried to add it to pmm server.

pmm-admin status
Agent ID : /agent_id/40481888-308a-4955-a3c3-1c08534386c9
Node ID : /node_id/9cf1cfe1-4286-4cbf-b424-f8d6741f8126
Node name: mariadb1

PMM Server:

PMM Client:

  • Connected : true*
  • Time drift : 4.717151ms*
  • Latency : 453.503µs*
  • Connection uptime: 100*
  • pmm-admin version: 2.37.1*
  • pmm-agent version: 2.37.1*
    Agents:
  • /agent_id/04b463e7-549b-4a59-90f2-26e6e004d113 vmagent Running 42000*
  • /agent_id/e6d23331-b4e2-453b-a14e-e91c4de7dc13 node_exporter Running 42001*

pmm-admin list
Service type Service name Address and port Service ID

Agent type Status Metrics Mode Agent ID Service ID Port
pmm_agent Connected /agent_id/40481888-308a-4955-a3c3-1c08534386c9 0
node_exporter Running push /agent_id/e6d23331-b4e2-453b-a14e-e91c4de7dc13 42001
vmagent Running push /agent_id/04b463e7-549b-4a59-90f2-26e6e004d113 42000

It’s a 10.3 MariaDB in standalone mode on a VM centos7.

systemctl status mariadb
● mariadb.service - MariaDB 10.3.32 database server

  • Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)*
  • Drop-In: /etc/systemd/system/mariadb.service.d*
  •       └─migrated-from-my.cnf-settings.conf*
  • Active: active (running) since Wed 2023-06-21 17:20:25 CEST; 2h 2min ago*

on PMM UI, I could see the node info from this mariadb server after run pmm-admin config successfuly.

But, unfortunately, pmm-admin add still in error …

pmm-admin add mysql --query-source=slowlog --size-slow-logs=1GiB --username=pmm --password=pass Mariadb1_Mysql_Node1 9.101.52.38:3306
Connection check failed: commands out of sync. Did you run multiple statements at once?.
[root@mariadb1 ~]# pmm-admin add mysql --query-source=slowlog --size-slow-logs=1GiB --username=pmm --password=pass Mariadb1_Mysql_Node1 9.101.52.38:3306 --debug
DEBUG 2023-06-21 17:32:31.163728119Z: POST /local/Status HTTP/1.1
Host: 127.0.0.1:7777
User-Agent: Go-http-client/1.1
Content-Length: 3
Accept: application/json
Content-Type: application/json
Accept-Encoding: gzip

{}

DEBUG 2023-06-21 17:32:31.166029725Z: HTTP/1.1 200 OK
Content-Length: 1119
Content-Type: application/json
Date: Wed, 21 Jun 2023 17:32:31 GMT
Grpc-Metadata-Content-Type: application/grpc

{
“agent_id”: “/agent_id/40481888-308a-4955-a3c3-1c08534386c9”,
“runs_on_node_id”: “/node_id/9cf1cfe1-4286-4cbf-b424-f8d6741f8126”,
“server_info”: {
“url”: “https://api_key:eyJrIjoiR1VWTWZWekV4b20xM0RCV0s3OVFqNHlnQ2dtNXVhb1giLCJuIjoicG1tLWFnZW50LW1hcmlhZGIxLTg0NDYwOTE3MzM2OTUzMDQ5MjAiLCJpZCI6MX0=@9.101.52.28:31654/”,
“insecure_tls”: true,
“connected”: true,
“version”: “2.37.0”,
“latency”: null,
“clock_drift”: null
},
“agents_info”: [
{
“agent_id”: “/agent_id/04b463e7-549b-4a59-90f2-26e6e004d113”,
“agent_type”: “VM_AGENT”,
“status”: “RUNNING”,
“listen_port”: 42000,
“process_exec_path”: “/usr/local/percona/pmm2/exporters/vmagent”
},
{
“agent_id”: “/agent_id/e6d23331-b4e2-453b-a14e-e91c4de7dc13”,
“agent_type”: “NODE_EXPORTER”,
“status”: “RUNNING”,
“listen_port”: 42001,
“process_exec_path”: “/usr/local/percona/pmm2/exporters/node_exporter”
}
],
“config_filepath”: “/usr/local/percona/pmm2/config/pmm-agent.yaml”,
“agent_version”: “2.37.1”,
“node_name”: “mariadb1”,
“connection_uptime”: 100
}
DEBUG 2023-06-21 17:32:31.166576414Z: POST /local/Status HTTP/1.1
Host: 127.0.0.1:7777
User-Agent: Go-http-client/1.1
Content-Length: 3
Accept: application/json
Content-Type: application/json
Accept-Encoding: gzip

{}

DEBUG 2023-06-21 17:32:31.167336413Z: HTTP/1.1 200 OK
Content-Length: 1119
Content-Type: application/json
Date: Wed, 21 Jun 2023 17:32:31 GMT
Grpc-Metadata-Content-Type: application/grpc

{
“agent_id”: “/agent_id/40481888-308a-4955-a3c3-1c08534386c9”,
“runs_on_node_id”: “/node_id/9cf1cfe1-4286-4cbf-b424-f8d6741f8126”,
“server_info”: {
“url”: “https://api_key:eyJrIjoiR1VWTWZWekV4b20xM0RCV0s3OVFqNHlnQ2dtNXVhb1giLCJuIjoicG1tLWFnZW50LW1hcmlhZGIxLTg0NDYwOTE3MzM2OTUzMDQ5MjAiLCJpZCI6MX0=@9.101.52.28:31654/”,
“insecure_tls”: true,
“connected”: true,
“version”: “2.37.0”,
“latency”: null,
“clock_drift”: null
},
“agents_info”: [
{
“agent_id”: “/agent_id/04b463e7-549b-4a59-90f2-26e6e004d113”,
“agent_type”: “VM_AGENT”,
“status”: “RUNNING”,
“listen_port”: 42000,
“process_exec_path”: “/usr/local/percona/pmm2/exporters/vmagent”
},
{
“agent_id”: “/agent_id/e6d23331-b4e2-453b-a14e-e91c4de7dc13”,
“agent_type”: “NODE_EXPORTER”,
“status”: “RUNNING”,
“listen_port”: 42001,
“process_exec_path”: “/usr/local/percona/pmm2/exporters/node_exporter”
}
],
“config_filepath”: “/usr/local/percona/pmm2/config/pmm-agent.yaml”,
“agent_version”: “2.37.1”,
“node_name”: “mariadb1”,
“connection_uptime”: 100
}
DEBUG 2023-06-21 17:32:31.168472169Z: POST /v1/management/MySQL/Add HTTP/1.1
Host: 9.101.52.28:31654
User-Agent: Go-http-client/1.1
Content-Length: 362
Accept: application/json
Authorization: Basic YXBpX2tleTpleUpySWpvaVIxVldUV1pXZWtWNGIyMHhNMFJDVjBzM09WRnFOSGxuUTJkdE5YVmhiMWdpTENKdUlqb2ljRzF0TFdGblpXNTBMVzFoY21saFpHSXhMVGcwTkRZd09URTNNek0yT1RVek1EUTVNakFpTENKcFpDSTZNWDA9
Content-Type: application/json
Accept-Encoding: gzip

{“node_id”:“/node_id/9cf1cfe1-4286-4cbf-b424-f8d6741f8126”,“service_name”:“Mariadb1_Mysql_Node1”,“address”:“9.101.52.38”,“port”:3306,“pmm_agent_id”:“/agent_id/40481888-308a-4955-a3c3-1c08534386c9”,“username”:“pmm”,“password”:“pass”,“qan_mysql_slowlog”:true,“max_slowlog_file_size”:“1073741824”,“metrics_mode”:“AUTO”,“disable_collectors”:null,“log_level”:“warn”}

DEBUG 2023-06-21 17:32:31.185197858Z: HTTP/1.1 400 Bad Request
Content-Length: 223
Connection: keep-alive
Content-Type: application/json
Date: Wed, 21 Jun 2023 17:32:31 GMT
Server: nginx
Strict-Transport-Security: max-age=63072000; includeSubdomains;

{
“error”: “Connection check failed: commands out of sync. Did you run multiple statements at once?.”,
“code”: 9,
“message”: “Connection check failed: commands out of sync. Did you run multiple statements at once?.”
}
DEBUG 2023-06-21 17:32:31.185313238Z: Result:
DEBUG 2023-06-21 17:32:31.185332733Z: Error: &my_sql.AddMySQLDefault{_statusCode:400, Payload:(*my_sql.AddMySQLDefaultBody)(0xc0005f0330)}
Connection check failed: commands out of sync. Did you run multiple statements at once?.
[root@mariadb1 ~]#

Thanks

4

The only thing jumping out at me is that the client (2.37.1) is newer than the PMM server (2.37.0) but given that 2.37.1 was CVE related fix on the server side seems odd that would be it. Server can work with older versions of client but it is expected that client would not be newer than server. I would highly recommend you update your server to 2.37.1 though as it does address a pretty nasty CVE.

I’ve never seen that error before. I’m curious if you’re able to add “remote monitoring” of that same node (through the PMM server UI). That would use the pmm agent bundled with pmm-server to retrieve metrics from the MariaDB service port (3306) and at least see if it’s a client problem or server problem. If you can’t do it as remote monitoring I’d shift my focus to some incompatibility with that version of MariaDB.

5

indeed I removed and reinstalled pmm agent to my client.
I didn’t catch up version was upper on the client side vs server side.

My other mariadb server has been added correctly with the same version of pmm client and server.

I tried to add from the gui (remote monitoring).
So, I removed node and agents from the gui for my mariadb1 server and tried by the gui.

adding remote mysql failed too.
service is down but node is OK

pmm add remote service
pmm add remote service1492×460 57.6 KB

pmm inventory nodes
pmm inventory nodes1484×554 59.6 KB

6

I suspect that “down” status for the mariadb1 instance is purely for firewall reasons. When you do remote monitoring the pmm server makes a connection to the service port (3306 in this case) but your PMM server is likely in a different firewall zone compared to the db server you want to monitor. easy way to test is to get a shell on your pmm server host and telnet 9.101.52.38 3306 and see if you get a connect prompt.

Since matching versions seems to work (based on the rancher working), I’d downgrade the client on mariadb1 and try to add again. the benefit of having the agent running on the db server is actually many:

  • Metrics are locally accumulated and pushed to the pmm server (most firewalls are set to keep things from the outside out but allow inside traffic out)
  • Remote monitoring only shows DB metrics…you won’t see any stats on the host like CPU/RAM/Disk which are vital to diagnosing issues
  • the agent has caching ability built-in and enabled by default so if agent loses communication with pmm server up to 1GB of metrics will accumulate until server restores and then those metrics will be pushed so no gaps in monitoring
  • the only way to use slowlog is locally since it’s file based, remote can only use perf schema for query analytics
    there’s more but you get the idea!
7

Hello Steve, in both cases (ServerRancher and mariadb12), firewall is disabled on each VM.

What did you said matching versions and asked to upgrade client on mariadb1 ?
I have to downgraded it to match my pmm server on 2.37.0.

Don’t have access to my servers right now, I will test asap and keep you inform.

Thanks for your help.

8

I meant downgrade (And fixed the reply) :man_facepalming:

In terms of firewall I was thinking more about the corporate firewall. In most companies I’ve worked for DB’s are in a protected network zone and application and utility servers are outside that zone…so the network security team would have to open up access from your PMM server to your DB server to allow the PMM server to retrieve metrics. The telnet test above would confirm or debunk that though. If PMM server can talk to port 3306 on your db server then there’s another reason for the “down” which you can find by going to: https://<pmm-server-ip:port>/victoriametrics/targets and click the “unhealthy” to see what error is being thrown.

9

So, I could downgrade client to match server version : 2.37.0

I had an error when I tried to telnet from pmm server (by the way, your pmm server image (installed from helm) doesn’t contain telnet !)

I had the issue when trying to telnet my db :
Host ‘’ is not allowed to connect to this MariaDB serverConnection closed by foreign host.

even if in my config file I have (bind-address = 0.0.0.0),it’s failed.
I could resolve it by adding skip-grant-tables.

With that , I could add remote service with GUI.

Thanks.