PMM2 custom mysql query fail

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

Hi.
I’m using PMM2, and MySQL InnoDB cluster (8.0).
I add a custom mysql query to get innodb cluster specific information.

The query when it running manually:


*************************** 1. row ***************************

REGEXP_SUBSTR(i.instance_name, '.+?(?=:)'): node3
CLUSTER_NAME: testCluster
instance_id: 3
host_id: 3
replicaset_id: 1
mysql_server_uuid: 2fdffc14-3387-11ea-9ab7-5254008afee6
instance_name: node3:3306
role: HA
weight: NULL
addresses: {"mysqlX": "node3:33060", "grLocal": "node3:33061", "mysqlClassic": "node3:3306"}
attributes: {"joinTime": "2020-01-10 09:59:51.623245", "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_3"}
version_token: NULL
description: NULL
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 2fdffc14-3387-11ea-9ab7-5254008afee6
MEMBER_HOST: node3
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.18
MEMBER_STATE_UPDATE: ONLINE
STATUS: 0
ROLE: 1
*************************** 2. row ***************************
REGEXP_SUBSTR(i.instance_name, '.+?(?=:)'): node2
CLUSTER_NAME: testCluster
instance_id: 2
host_id: 2
replicaset_id: 1
mysql_server_uuid: 7d716b1c-3386-11ea-8661-5254008afee6
instance_name: node2:3306
role: HA
weight: NULL
addresses: {"mysqlX": "node2:33060", "grLocal": "node2:33061", "mysqlClassic": "node2:3306"}
attributes: {"joinTime": "2020-01-10 09:59:29.264074", "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_2"}
version_token: NULL
description: NULL
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 7d716b1c-3386-11ea-8661-5254008afee6
MEMBER_HOST: node2
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.18
MEMBER_STATE_UPDATE: ONLINE
STATUS: 0
ROLE: 1
*************************** 3. row ***************************
REGEXP_SUBSTR(i.instance_name, '.+?(?=:)'): node1
CLUSTER_NAME: testCluster
instance_id: 1
host_id: 1
replicaset_id: 1
mysql_server_uuid: cfe65629-3385-11ea-88f4-5254008afee6
instance_name: node1:3306
role: HA
weight: NULL
addresses: {"mysqlX": "node1:33060", "grLocal": "node1:33061", "mysqlClassic": "node1:3306"}
attributes: {"recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1"}
version_token: NULL
description: NULL
CHANNEL_NAME: group_replication_applier
MEMBER_ID: cfe65629-3385-11ea-88f4-5254008afee6
MEMBER_HOST: node1
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.18
MEMBER_STATE_UPDATE: ONLINE
STATUS: 0
ROLE: 0
3 rows in set (0.0123 sec)

And when I place it in /usr/local/percona/pmm2/collectors/custom-queries/mysql/medium-resolution/queries-mysqld.yml, I get multiple error messages:


Jan 11 11:49:42 node1 pmm-agent: #033[36mINFO#033[0m[2020-01-11T11:49:42.627+00:00] time="2020-01-11T11:49:42Z" level=warning msg="Could not parse []byte: strconv.ParseFloat: parsing \"node3\": invalid syntax" source="custom_query.go:379" #033[36magentID#033[0m=/agent_id/4c402037-8d2f-4d6b-b134-a3e7382e4f03 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter
Jan 11 11:49:42 node1 pmm-agent: #033[36mINFO#033[0m[2020-01-11T11:49:42.627+00:00] time="2020-01-11T11:49:42Z" level=warning msg="Could not parse []byte: strconv.ParseFloat: parsing \"2fdffc14-3387-11ea-9ab7-5254008afee6\": invalid syntax" source="custom_query.go:379" #033[36magentID#033[0m=/agent_id/4c402037-8d2f-4d6b-b134-a3e7382e4f03 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter
Jan 11 11:49:42 node1 pmm-agent: #033[36mINFO#033[0m[2020-01-11T11:49:42.627+00:00] time="2020-01-11T11:49:42Z" level=warning msg="Could not parse []byte: strconv.ParseFloat: parsing \"node3:3306\": invalid syntax" source="custom_query.go:379" #033[36magentID#033[0m=/agent_id/4c402037-8d2f-4d6b-b134-a3e7382e4f03 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter
Jan 11 11:49:42 node1 pmm-agent: #033[36mINFO#033[0m[2020-01-11T11:49:42.627+00:00] time="2020-01-11T11:49:42Z" level=warning msg="Could not parse []byte: strconv.ParseFloat: parsing \"HA\": invalid syntax" source="custom_query.go:379" #033[36magentID#033[0m=/agent_id/4c402037-8d2f-4d6b-b134-a3e7382e4f03 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter
Jan 11 11:49:42 node1 pmm-agent: #033[36mINFO#033[0m[2020-01-11T11:49:42.627+00:00] time="2020-01-11T11:49:42Z" level=warning msg="Could not parse []byte: strconv.ParseFloat: parsing \"{\\\"mysqlX\\\": \\\"node3:33060\\\", \\\"grLocal\\\": \\\"node3:33061\\\", \\\"mysqlClassic\\\": \\\"node3:3306\\\"}\": invalid syntax" source="custom_query.go:379" #033[36magentID#033[0m=/agent_id/4c402037-8d2f-4d6b-b134-a3e7382e4f03 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter
Jan 11 11:49:42 node1 pmm-agent: #033[36mINFO#033[0m[2020-01-11T11:49:42.628+00:00] time="2020-01-11T11:49:42Z" level=warning msg="Could not parse []byte: strconv.ParseFloat: parsing \"{\\\"recoveryAccountHost\\\": \\\"%\\\", \\\"recoveryAccountUser\\\": \\\"mysql_innodb_cluster_1\\\"}\": invalid syntax" source="custom_query.go:379" #033[36magentID#033[0m=/agent_id/4c402037-8d2f-4d6b-b134-a3e7382e4f03 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter
...
Jan 11 11:49:42 node1 pmm-agent: #033[36mINFO#033[0m[2020-01-11T11:49:42.628+00:00] time="2020-01-11T11:49:42Z" level=info msg="unparseable column type - discarding: mysql_performance_schema_group_replication_replication_group_members, REGEXP_SUBSTR(i.instance_name, '.+?(?=:)'), %!s(<nil>)" source="custom_query.go:527" #033[36magentID#033[0m=/agent_id/4c402037-8d2f-4d6b-b134-a3e7382e4f03 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter
Jan 11 11:49:42 node1 pmm-agent: #033[36mINFO#033[0m[2020-01-11T11:49:42.628+00:00] time="2020-01-11T11:49:42Z" level=info msg="unparseable column type - discarding: mysql_performance_schema_group_replication_replication_group_members, mysql_server_uuid, %!s(<nil>)" source="custom_query.go:527" #033[36magentID#033[0m=/agent_id/4c402037-8d2f-4d6b-b134-a3e7382e4f03 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter
Jan 11 11:49:42 node1 pmm-agent: #033[36mINFO#033[0m[2020-01-11T11:49:42.628+00:00] time="2020-01-11T11:49:42Z" level=info msg="unparseable column type - discarding: mysql_performance_schema_group_replication_replication_group_members, instance_name, %!s(<nil>)" source="custom_query.go:527" #033[36magentID#033[0m=/agent_id/4c402037-8d2f-4d6b-b134-a3e7382e4f03 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter
...

Any help will be highly appreciated.

#2

There is probably some issue with .yml Are you able to share the full file ?

If not I will replace the query with trivial one something like select “a” as column_label, 2 as value; which can help to understand if it is something funky with query special characters or formatting or rest of YML

#3

Here’s my queries-mysqld.yml:


mysql_performance_schema_group_replication_replication_group_members:
query: "SELECT REGEXP_SUBSTR(i.instance_name, '.+?(?=:)'),c.cluster_name as CLUSTER_NAME,i.*, rgm.* ,IFNULL(rgm.MEMBER_STATE, 'OFFLINE') MEMBER_STATE_UPDATE,case rgm.MEMBER_STATE when 'ONLINE' then 0 when 'RECOVERING' then 1 else 2 end STATUS, case rgm.MEMBER_ROLE when 'PRIMARY' then 0 else 1 end ROLE FROM mysql_innodb_cluster_metadata.clusters c INNER JOIN mysql_innodb_cluster_metadata.instances i ON c.default_replicaset = i.replicaset_id LEFT JOIN performance_schema.replication_group_members rgm ON i.mysql_server_uuid = rgm.MEMBER_ID WHERE &#64;&#64;hostname = (SELECT MEMBER_HOST FROM performance_schema.replication_group_members WHERE MEMBER_ROLE = 'PRIMARY')"
metrics:
- CLUSTER_NAME:
usage: "LABEL"
description: "Name of the cluster"
- INSTANCE_NAME:
usage: "LABEL"
description: "IP adress of the host"
- CHANNEL_NAME:
usage: "LABEL"
description: "The name of the Group Replication channel."
- MEMBER_ID:
usage: "LABEL"
description: "Shows the value of the name of the group. It is always a valid UUID."
- MEMBER_HOST:
usage: "LABEL"
description: "Hostname"
- MEMBER_PORT:
usage: "LABEL"
description: "Port"
- MEMBER_STATE:
usage: "LABEL"
description: "OFFLINE, ERROR, etc"
- MEMBER_ROLE:
usage: "LABEL"
description: "Primary or Secondary"
- MEMBER_VERSION:
usage: "LABEL"
description: "Version"
- MEMBER_STATE_UPDATE:
usage: "LABEL"
description: "OFFLINE, ERROR, etc UPDATED"
- STATUS:
usage: "GAUGE"
description: "Status in an interger value"
- ROLE:
usage: "GAUGE"
description: "Role in an interger value"

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.