An error in one SQL query breaks the execution of all queries with MS SQL Server
Gordon-F opened this issue · 0 comments
Gordon-F commented
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):
- 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.
Installation details
- operating system: Windows/Linux
- query-exporter installation type:
docker pull adonato/query-exporter:2.9.2
To Reproduce
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
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
- 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"}
- 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