Not the answer you need?
Register and ask your own question!
An upgrade to Forum ranks and points will occur about noon New York time today, Tue 22 Sep. The Forum may be briefly in read-only mode. [email protected]

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.