Metrics are not updated in Kubernetes using the pymssql driver

[bcarlock-emerge]

Describe the bug query-exporter is installed by Helm. query-exporter never updates the target metrics, but it does update the "queries" metric.

Installation details

• operating system: Docker container
• query-exporter installation type:
• pip:

        Package                Version
        ---------------------- -----------
        aiohttp                3.7.4.post0
        argcomplete            1.12.2
        async-timeout          3.0.1
        attrs                  20.3.0
        chardet                4.0.0
        croniter               1.0.10
        cx-Oracle              8.1.0
        ibm-db                 3.0.3
        ibm-db-sa              0.3.6
        idna                   3.1
        jsonschema             3.2.0
        multidict              5.1.0
        mysqlclient            2.0.3
        outcome                1.1.0
        pip                    21.0.1
        prometheus-aioexporter 1.6.3
        prometheus-client      0.9.0
        psycopg2               2.8.6
        pymssql                2.2.5
        pyodbc                 4.0.30
        pyrsistent             0.17.3
        python-dateutil        2.8.1
        PyYAML                 5.4.1
        query-exporter         2.7.0
        Represent              1.6.0.post0
        setuptools             49.2.1
        six                    1.15.0
        SQLAlchemy             1.3.23
        sqlalchemy-aio         0.16.0
        toolrack               3.0.1
        typing-extensions      3.7.4.3
        yarl                   1.6.3

• docker image: adonato/query-exporter:latest customized with pymssql
• snap: N/A

To Reproduce

If possible, please provide detailed steps to reproduce the behavior:

1. Config file content (redacted of secrets if needed)

env:
  CONNECTION_STRING: "mssql+pymssql://username:[email protected]:1433/staging"

exporter_config: |
  databases:
    staging:
      dsn: env:CONNECTION_STRING
      labels: 
        app: myapp
  metrics:
    metric1:
      type: gauge
  queries:
    staging_query:
        interval: 30s
        timeout: 15
        databases: [staging]
        metrics: [metric1]
        sql: | 
          SELECT SUM(Events * Participants) as metric1
          FROM dbo.Events
          where EndDate = DATETRUNC(day, getdate())

3. Ran query-exporter with the following command line ... query-exporter -L DEBUG -H 0.0.0.0 -- /config/config.yaml
4. Got the error when ... No error, just no metrics are emitted

Example logs:

2022-10-25 22:27:29,990 - INFO - aiohttp.web - Listening on http://0.0.0.0:9560
2022-10-25 22:27:29,990 - INFO - query-exporter - version 2.8.3 starting up
2022-10-25 22:27:30,517 - DEBUG - query-exporter - connected to database "staging"
2022-10-25 22:27:30,517 - DEBUG - query-exporter - running query "staging_query" on database "staging"
2022-10-25 22:27:30,580 - DEBUG - query-exporter - updating metric "queries" inc 1 {app="myapp",database="staging",query="staging_query",status="success"}
2022-10-25 22:28:00,018 - DEBUG - query-exporter - running query "staging_query" on database "staging"
2022-10-25 22:28:00,073 - DEBUG - query-exporter - updating metric "queries" inc 1 {app="myapp",database="staging",query="staging_query",status="success"}
2022-10-25 22:28:30,020 - DEBUG - query-exporter - running query "staging_query" on database "staging"
2022-10-25 22:28:30,075 - DEBUG - query-exporter - updating metric "queries" inc 1 {app="myapp",database="staging",query="staging_query",status="success"}
2022-10-25 22:28:52,893 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"
2022-10-25 22:28:58,639 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"
2022-10-25 22:28:58,639 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"
2022-10-25 22:28:59,992 - DEBUG - query-exporter - running query "staging_query" on database "staging"
2022-10-25 22:29:00,048 - DEBUG - query-exporter - updating metric "queries" inc 1 {app="myapp",database="staging",query="staging_query",status="success"}
2022-10-25 22:29:28,639 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"
2022-10-25 22:29:28,645 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"
2022-10-25 22:29:29,992 - DEBUG - query-exporter - running query "staging_query" on database "staging"
2022-10-25 22:29:30,047 - DEBUG - query-exporter - updating metric "queries" inc 1 {app="myapp",database="staging",query="staging_query",status="success"}
2022-10-25 22:29:58,639 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"
2022-10-25 22:29:58,639 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"
2022-10-25 22:29:59,992 - DEBUG - query-exporter - running query "staging_query" on database "staging"
2022-10-25 22:30:00,049 - DEBUG - query-exporter - updating metric "queries" inc 1 {app="myapp",database="staging",query="staging_query",status="success"}
2022-10-25 22:30:28,640 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"
2022-10-25 22:30:28,640 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"
2022-10-25 22:30:29,992 - DEBUG - query-exporter - running query "staging_query" on database "staging"
2022-10-25 22:30:30,049 - DEBUG - query-exporter - updating metric "queries" inc 1 {app="myapp",database="staging",query="staging_query",status="success"}
2022-10-25 22:30:58,640 - INFO - aiohttp.access - 10.11.28.169 "GET / HTTP/1.1" 200 685 "-" "kube-probe/1.23"

Screenshot

Edit: Screenshot to show that the query returns numeric data

[bcarlock-emerge]

FYI- switching to pyodbc with driver=ODBC+Driver+17+for+SQL+Server appears to allow metrics to update.