No metrics gathered

[zahlio]

Hello i just finished setting up the exporter however i cant seem to get my queries to generate metrics? Here is my queries file:

- num_companies:
    # Name of the driver to use.
    driver: mysql

    # Connection information.
    connection:
        host: ...
        port: ...
        user: ...
        password: ...
        database: ...

    sql: >
        select name, count(1) as cnt from companies

    interval: 5s

    data-field: cnt

It runs fine (using docker) and the sql-agent also seems to run fine. However when i visit /metrics i do not get the expected metric: query_result_num_companies.

[haxorof]

Hi @zahlio !

I think I can contribute with an answer to your issue. The SQL statement is incorrect because the GROUP BY clause is missing. I think the example available in this repo is also incorrect.

By having a MySQL setup with a table like this:

+----------+---------+
| name     | country |
+----------+---------+
| Company1 | USA     |
| Company1 | SWE     |
| Company1 | IRL     |
| Company2 | IRL     |
+----------+---------+
4 rows in set (0.00 sec)

And then a queries.yml like below:

- companies_by_name:
    driver: mysql
    connection:
        host: mysql
        port: 3306
        user: root
        password: unsecure
        database: test
    sql: >
        select name, count(1) as cnt from companies group by name
    interval: 5s
    data-field: cnt

At the metrics endpoint you will be able to see this:

# HELP query_result_companies_by_name Result of an SQL query
# TYPE query_result_companies_by_name gauge
query_result_companies_by_name{name="company1"} 3
query_result_companies_by_name{name="company2"} 1

Hope it will help you move forward!

If not have a look at the logs in both prometheus-sql and sqlagent container.