# PMM2 custom mysql query fail **URL:** \ **Category:** PMM 2.x\ **Created:** [January 11, 2020, 6:45am UTC](https://forums.percona.com/t/pmm2-custom-mysql-query-fail/7376 "2020-01-11T06:45:56Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![sinist3r](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@sinist3r](https://forums.percona.com/u/sinist3r)\ **Post date:** [January 11, 2020, 6:45am UTC](https://forums.percona.com/t/pmm2-custom-mysql-query-fail/7376/1 "2020-01-11T06:45:56Z")
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: ```auto ***************************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: ```auto 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()" 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()" 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()" 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. ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [January 11, 2020, 9:07am UTC](https://forums.percona.com/t/pmm2-custom-mysql-query-fail/7376/2 "2020-01-11T09:07:50Z")
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 ---
**Author:** ![sinist3r](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@sinist3r](https://forums.percona.com/u/sinist3r)\ **Post date:** [January 13, 2020, 6:15am UTC](https://forums.percona.com/t/pmm2-custom-mysql-query-fail/7376/3 "2020-01-13T06:15:46Z")
> [@Peter;55194](#): > > There is probably some issue with .yml Are you able to share the full file ? Here’s my queries-mysqld.yml: ```auto 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 @@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" ```