An error in one SQL query breaks the execution of all queries with MS SQL Server

[Gordon-F]

Describe the bug

An error in one SQL query breaks the execution of all queries with MS SQL Server. This problem is reproduced only with MS SQL server. Just tested with SQLite and everything works as expected. This error is similar to #72, but unfortunately autocommit: false didn't help.

Based on logs (see below):

1. We have N queries
2. One of N queries failed with pyodbc.ProgrammingError
3. Next one failed with (pyodbc.Error) ('HY010', '[HY010] [unixODBC][Driver Manager]Function sequence error (0) (SQLFetch)')
4. All other queries failed with (pyodbc.ProgrammingError) The cursor's connection has been closed.

Installation details

• operating system: Windows/Linux
• query-exporter installation type: docker pull adonato/query-exporter:2.9.2

To Reproduce

1. config.yaml:

databases:
  db1:
    dsn: mssql+pyodbc://sa:yourStrong(!)Password@ms_sql:1433/master?TrustServerCertificate=yes&driver=ODBC+Driver+18+for+SQL+Server&MARS_Connection=Yes
    autocommit: false

metrics:
  metric1:
    type: gauge
  metric2:
    type: gauge
  metric3:
    type: gauge
  metric4:
    type: gauge

queries:
  query1:
    interval: 5
    databases: [db1]
    metrics: [metric1]
    sql: SELECT RAND() AS metric1
  query2:
    interval: 5
    databases: [db1]
    metrics: [metric2]
    # SQL error here. Should be RAND()
    sql: SELECT RANDOM() AS metric2
  query3:
    interval: 5
    databases: [db1]
    metrics: [metric3]
    sql: SELECT RAND() AS metric3
  query4:
    interval: 10
    databases: [db1]
    metrics: [metric4]
    sql: SELECT RAND() AS metric4

2. GET /metrics result:

# HELP database_errors_total Number of database errors
# TYPE database_errors_total counter
# HELP queries_total Number of database queries
# TYPE queries_total counter
queries_total{database="db1",query="query2",status="error"} 11.0
queries_total{database="db1",query="query1",status="error"} 11.0
queries_total{database="db1",query="query3",status="error"} 11.0
queries_total{database="db1",query="query4",status="error"} 6.0
# HELP queries_created Number of database queries
# TYPE queries_created gauge
queries_created{database="db1",query="query2",status="error"} 1.7007537888406882e+09
queries_created{database="db1",query="query1",status="error"} 1.7007537888448114e+09
queries_created{database="db1",query="query3",status="error"} 1.7007537888456993e+09
queries_created{database="db1",query="query4",status="error"} 1.7007537888465378e+09
# HELP query_latency Query execution latency
# TYPE query_latency histogram
# HELP metric1
# TYPE metric1 gauge
# HELP metric2
# TYPE metric2 gauge
# HELP metric3
# TYPE metric3 gauge
# HELP metric4
# TYPE metric4 gauge

3. Logs

2023-11-23 15:40:35,650 - DEBUG - query-exporter - connected to database "db1"
2023-11-23 15:40:35,650 - DEBUG - query-exporter - running query "query1" on database "db1"
2023-11-23 15:40:35,650 - DEBUG - query-exporter - running query "query2" on database "db1"
2023-11-23 15:40:35,650 - DEBUG - query-exporter - running query "query3" on database "db1"
2023-11-23 15:40:35,651 - DEBUG - query-exporter - running query "query4" on database "db1"
2023-11-23 15:40:35,653 - ERROR - query-exporter - query "query2" on database "db1" failed: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]'RANDOM' is not a recognized built-in function name. (195) (SQLExecDirectW)")
[SQL: SELECT RANDOM() AS metric2]
(Background on this error at: http://sqlalche.me/e/13/f405)
2023-11-23 15:40:35,654 - DEBUG - query-exporter -   File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 317, in execute 
    result = await self._execute_query(query)
  File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 350, in _execute_query
    return await self.execute_sql(
  File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 343, in execute_sql
    return await asyncio.wait_for(
  File "/usr/local/lib/python3.10/asyncio/tasks.py", line 408, in wait_for
    return await fut
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/base.py", line 291, in execute
    rp = await self._run_in_thread(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/base.py", line 234, in _run_in_thread
    return await _self._worker.run(_func, args, kwargs)
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/asyncio.py", line 70, in run
    return request.response.unwrap()
  File "/virtualenv/lib/python3.10/site-packages/outcome/_impl.py", line 213, in unwrap
    raise captured_error
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
query-exporter-exporter-1  |
2023-11-23 15:40:35,654 - DEBUG - query-exporter - updating metric "queries" inc 1 {database="db1",query="query2",status="error"}
2023-11-23 15:40:35,658 - ERROR - query-exporter - query "query1" on database "db1" failed: (pyodbc.Error) ('HY010', '[HY010] [unixODBC][Driver Manager]Function sequence error (0) (SQLFetch)')
(Background on this error at: http://sqlalche.me/e/13/dbapi)
2023-11-23 15:40:35,658 - DEBUG - query-exporter -   File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 318, in execute 
    return query.results(await QueryResults.from_results(result))
  File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 148, in from_results
    await results.keys(), await results.fetchall(), latency=latency
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/base.py", line 506, in fetchall
    return await self._run_in_thread(self._result_proxy.fetchall)
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/base.py", line 234, in _run_in_thread
    return await _self._worker.run(_func, args, kwargs)
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/asyncio.py", line 70, in run
    return request.response.unwrap()
  File "/virtualenv/lib/python3.10/site-packages/outcome/_impl.py", line 213, in unwrap
    raise captured_error
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1288, in fetchall
    self.connection._handle_dbapi_exception(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1284, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1230, in _fetchall_impl
    return self.cursor.fetchall()
query-exporter-exporter-1  |
2023-11-23 15:40:35,658 - DEBUG - query-exporter - updating metric "queries" inc 1 {database="db1",query="query1",status="error"}
2023-11-23 15:40:35,659 - ERROR - query-exporter - query "query3" on database "db1" failed: (pyodbc.ProgrammingError) The cursor's connection has been closed.
(Background on this error at: http://sqlalche.me/e/13/f405)
2023-11-23 15:40:35,659 - DEBUG - query-exporter -   File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 318, in execute 
    return query.results(await QueryResults.from_results(result))
  File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 148, in from_results
    await results.keys(), await results.fetchall(), latency=latency
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/base.py", line 506, in fetchall
    return await self._run_in_thread(self._result_proxy.fetchall)
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/base.py", line 234, in _run_in_thread
    return await _self._worker.run(_func, args, kwargs)
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/asyncio.py", line 70, in run
    return request.response.unwrap()
  File "/virtualenv/lib/python3.10/site-packages/outcome/_impl.py", line 213, in unwrap
    raise captured_error
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1288, in fetchall
    self.connection._handle_dbapi_exception(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1284, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1230, in _fetchall_impl
    return self.cursor.fetchall()
query-exporter-exporter-1  |
2023-11-23 15:40:35,659 - DEBUG - query-exporter - updating metric "queries" inc 1 {database="db1",query="query3",status="error"}
2023-11-23 15:40:35,660 - ERROR - query-exporter - query "query4" on database "db1" failed: (pyodbc.ProgrammingError) The cursor's connection has been closed.
(Background on this error at: http://sqlalche.me/e/13/f405)
2023-11-23 15:40:35,660 - DEBUG - query-exporter -   File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 318, in execute 
    return query.results(await QueryResults.from_results(result))
  File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 148, in from_results
    await results.keys(), await results.fetchall(), latency=latency
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/base.py", line 506, in fetchall
    return await self._run_in_thread(self._result_proxy.fetchall)
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/base.py", line 234, in _run_in_thread
    return await _self._worker.run(_func, args, kwargs)
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy_aio/asyncio.py", line 70, in run
    return request.response.unwrap()
  File "/virtualenv/lib/python3.10/site-packages/outcome/_impl.py", line 213, in unwrap
    raise captured_error
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1288, in fetchall
    self.connection._handle_dbapi_exception(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1284, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "/virtualenv/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1230, in _fetchall_impl
    return self.cursor.fetchall()
query-exporter-exporter-1  |
2023-11-23 15:40:35,660 - DEBUG - query-exporter - updating metric "queries" inc 1 {database="db1",query="query4",status="error"}

4. Docker compose for fast testing:

version: "3.7"

services:
  exporter:
    image: adonato/query-exporter:latest
    ports:
      - 9560:9560
    volumes:
      - "$CONFIG_PATH:/config.yaml"
    command:
      - -L
      - DEBUG
  ms_sql:
    image: mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04
    environment:
      - ACCEPT_EULA=Y,
      - MSSQL_SA_PASSWORD=yourStrong(!)Password
      - MSSQL_PID=Evaluation
    ports:
      - 1433:1433