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):
We have N queries
One of N queries failed with pyodbc.ProgrammingError
Next one failed with (pyodbc.Error) ('HY010', '[HY010] [unixODBC][Driver Manager]Function sequence error (0) (SQLFetch)')
All other queries failed with (pyodbc.ProgrammingError) The cursor's connection has been closed.
2. `GET /metrics` result:
```txt
# 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
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"}
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: falsedidn't help.Based on logs (see below):
pyodbc.ProgrammingError(pyodbc.Error) ('HY010', '[HY010] [unixODBC][Driver Manager]Function sequence error (0) (SQLFetch)')(pyodbc.ProgrammingError) The cursor's connection has been closed.Installation details
docker pull adonato/query-exporter:2.9.2To Reproduce
config.yaml: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()
query3: interval: 5 databases: [db1] metrics: [metric3] sql: SELECT RAND() AS metric3 query4: interval: 10 databases: [db1] metrics: [metric4] sql: SELECT RAND() AS metric4
Logs
Docker compose for fast testing:
services: exporter: image: adonato/query-exporter:latest ports: