Issue with pool when connections become invalid

[kesavkolla]

I'm using SQL Server 2019 with aioodbc I'm observing a situation wherein when the connection becomes stale the I'm getting exception when try to execute any query. Currently there is no check for validating connection when we acquire connection from pool. Check for connection validity happens when there is error in statement execution. When statement gets errored out the caller also gets exception. The logic is falling apart for my long running python server. Currently I've a pool size of 25 so I've to wait for 25 requests to fail so that connections get closed in pool. The 26th request will get a fresh connection.

I think the behavior of acquire should run a validation query on connection object if connection validation is failed then it should remove connection from pool and try to obtain another valid one.

[reedjosh]

Wondering where the contributors for this project have ran off to?

Anyway...

I've solved what you're looking for in my own way specific to my use case. Maybe it can help you?

def _is_conn_closed_error(e):
    """ Check if error indicates a connection has closed. """
    # ORA-01012 -- Not logged on.
    # ORA-02396 -- Connection timeout.
    if not isinstance(e, pyodbc.Error) or len(e.args) < 2:
        return False

    sqlstate, msg = e.args[0], e.args[1]
    if sqlstate != "HY000":
        return False

    return "ORA-01012" in msg or "ORA-02396" in msg


async def _recycle_inuse_connection(self, conn):
    """ Recycle a connection that is currently checked out from the pool.
        Use when connection has gone stale.
    """
    assert conn in self._used
    if not conn.closed:
        await conn.close()
    self._used.remove(conn)
    conn = await connect(echo=self._echo, loop=self._loop, **self._conn_kwargs)
    self._used.add(conn)
    return conn

 pool._recycle_inuse_connection = MethodType(
        _recycle_inuse_connection, DBConn.pool
  )

And then I check for connection prior to running my real queries.

    async def fetch_all(self, query):
        """ Fetch all results. """
        conn = await self.pool.acquire()

        # Check connection is alive.
        # Recycle if it has timed out.
        try:
            await conn.execute("select 1 from dual")
        except pyodbc.Error as exc:
            logging.debug(exc)
            if _is_conn_closed_error(exc):
                conn = await self.pool._recycle_inuse_connection(conn)
            else:
                raise (exc)

        async with conn.cursor() as crsr:
            await crsr.execute(query)
            data = await crsr.fetchall()
            columns = [column[0] for column in crsr.description]

        results = [dict(zip(columns, row)) for row in data]
        await self.pool.release(conn)
        return results

I think the workaround could be added into the acquire() function in with the _recycle_connection logic. And the function to use to verify the connection could be a function provided by the user.