Not recovering from interrupted database connection while query is executing

[itssimon]

If the connection to the ClickHouse database is interrupted while a query is executing, asynch is unable to recover from it. Any subsequent queries fail with the following exception:

ProgrammingError: some records have not been fetched. fetch the remaining records before executing the next query

It looks like this is because the is_query_executing attribute on the Connection remains True if the end of stream packet was missed.

I'm not even sure how to work around this. I've had to restart my application to recover from this, which is far from ideal.

[itssimon]

This may also be related to #71

[tiejunhu]

still got this issue with latest master version

[boolka]

Got this error too. And think i get the point. When we was release the connection to the pool we need to call reset_state. Then connection will be passed to new consumer clean

[boolka]

@tiejunhu you can use my hint right now.

...
try:
    await cursor.execute(...)
except (SocketTimeoutError, NetworkError, gaierror):
    await conn.close()

You must close connection mannually on some network errors

[tiejunhu]

@tiejunhu you can use my hint right now.

...
try:
    await cursor.execute(...)
except (SocketTimeoutError, NetworkError, gaierror):
    await conn.close()

You must close connection mannually on some network errors

Thank you. I used a similar solution: close the connection before returning it to the pool, then retry the request.

[stankudrow]

@tiejunhu , @itssimon , @boolka , hi. The Pool API changed, could you see if the reconnection is broken and the issue is back?

[misuzu]

Still reproducible on 9ae48be

   result = await conn.execute(q)
  File "sqlalchemy/ext/asyncio/engine.py", line 657, in execute
    result = await greenlet_spawn(
  File "sqlalchemy/util/_concurrency_py3k.py", line 201, in greenlet_spawn
    result = context.throw(*sys.exc_info())
  File "sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
  File "sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
  File "sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
  File "sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
  File "sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "sqlalchemy/engine/base.py", line 2358, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "clickhouse_sqlalchemy/drivers/asynch/base.py", line 36, in do_execute
    cursor.execute(statement, parameters, context)
  File "clickhouse_sqlalchemy/drivers/asynch/connector.py", line 57, in execute
    return self.await_(self._execute_async(operation, params, context))
  File "sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
    return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
  File "sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
    value = await result
  File "clickhouse_sqlalchemy/drivers/asynch/connector.py", line 61, in _execute_async
    result = await self._cursor.execute(
  File "asynch/cursors.py", line 70, in execute
    self._check_query_executing()
  File "asynch/cursors.py", line 288, in _check_query_executing
    raise ProgrammingError(

$ pip freeze
alembic==1.14.0
asttokens==3.0.0
asynch @ git+https://github.com/long2ice/asynch.git@9ae48be16560921c2b62bca639e31cc5c2503165
attrs==25.3.0
cattrs==23.2.3
certifi==2025.1.31
charset-normalizer==3.4.1
ciso8601==2.3.2
clickhouse-driver==0.2.9
clickhouse-sqlalchemy==0.3.2
cramjam==2.9.1
dnspython==2.7.0
exceptiongroup==1.2.2
executing==2.2.0
greenlet==3.1.1
idna==3.10
leb128==1.0.8
lz4==4.4.3
Mako==1.3.9
MarkupSafe==3.0.2
motor==3.7.0
pkg_resources==0.0.0
pure_eval==0.2.3
pymongo==4.10.1
python-snappy==0.7.3
pytz==2025.2
requests==2.32.3
sentry-sdk==1.29.2
SQLAlchemy==2.0.36
typing_extensions==4.13.0
tzlocal==5.3.1
urllib3==2.3.0
zstandard==0.23.0
zstd==1.5.6.6

[SkytAsul]

Hi, this is quite an annoying issue. When writing a proxy or such a system, incorrect requests that makes the ClickHouse server reply angrily are frequent. Closing the connections on every error is not a valid solution since it completely defeats the point of a pool: to have multiple, pre-established connections to use for maximum performance.

[nils-borrmann-tacto]

@SkytAsul @misuzu I fixed this bug in #150 (or checkout my fork for that and some other fixes).