Not the answer you need?
Register and ask your own question!

PMM2 custom mysql query fail

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.

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    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
  • sinist3rsinist3r Entrant Current User Role Novice
    Peter wrote: »
    There is probably some issue with .yml Are you able to share the full file ?

    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"
    
    
Sign In or Register to comment.

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