search

albertodonato / query-exporter

Export Prometheus metrics from SQL queries
GNU General Public License v3.0
447 stars 103 forks source link

Two queries use the same database, in different time periods, the last query reports an error #143

Open ilanni2460 opened 1 year ago

ilanni2460 commented 1 year ago

Two queries use the same database, in different time periods, the last query reports an error.

this is config： ` databases: mysql: dsn: mysql://root:XXXk@172.XXX.XXX.XX:3306/test1 labels: env: PROD

metrics: ncc_file: type: gauge description: NCC_DFT expiration: 1h

tem_file: type: gauge description:TEM_DFT expiration: 1h

queries: ncc_file: timeout: 1 schedule: "05 22 *" databases: [mysql] metrics:

this is error log: 2023-03-27 22:30:00,001 - DEBUG - query-exporter - running query "tem_file" on database "mysql" 2023-03-27 22:30:00,003 - ERROR - query-exporter - query "tem_file" on database "mysql" failed: (MySQLdb.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: SELECT count(*) as tem_fileFROM transfer_info WHERE file_name LIKE '%%TEM%%';] (Background on this error at: http://sqlalche.me/e/13/e3q8) 2023-03-27 22:30:00,097 - DEBUG - query-exporter - File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 312, in execute result = await self._execute_query(query) File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 345, in _execute_query return await self.execute_sql( File "/virtualenv/lib/python3.10/site-packages/query_exporter/db.py", line 338, in execute_sql return await asyncio.wait_for( File "/usr/local/lib/python3.10/asyncio/tasks.py", line 445, in wait_for return fut.result() 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 138, 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_dbapiexception 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) File "/virtualenv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/virtualenv/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/virtualenv/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query _mysql.connection.query(self, query)

2023-03-27 22:30:00,098 - DEBUG - query-exporter - updating metric "queries" inc 1 {database="mysql",env="PROD",query="tem_file",status="error"} 2023-03-27 22:30:04,219 - INFO - aiohttp.access - 169.254.30.1 "GET /metrics HTTP/1.1" 200 2957 "-" "Prometheus/2.41.0" 2023-03-27 22:30:19,219 - INFO - aiohttp.access - 169.254.30.1 "GET /metrics HTTP/1.1" 200 2957 "-" "Prometheus/2.41.0" 2023-03-27 22:30:34,219 - INFO - aiohttp.access - 169.254.30.1 "GET /metrics HTTP/1.1" 200 2957 "-" "Prometheus/2.41.0" 2023-03-27 22:30:49,219 - INFO - aiohttp.access - 169.254.30.1 "GET /metrics HTTP/1.1" 200 2957 "-" "Prometheus/2.41.0" 2023-03-27 22:31:04,218 - INFO - aiohttp.access - 169.254.30.1 "GET /metrics HTTP/1.1" 200 2957 "-" "Prometheus/2.41.0" `

yumitsu commented 1 year ago

@ilanni2460 Try to add keep-connected: true configuration option to databases section of your config.yaml.

yumitsu commented 1 year ago

Also try to slightly increase timeout value in queries section.