per those docs callTimeout is new as of oracle client 18c , that's extremely new. i run everything from the 11g rpm still. On Thu, Oct 25, 2018 at 8:49 AM Simon King <si...@simonking.org.uk> wrote: > > On Thu, Oct 25, 2018 at 12:50 PM Sebastian Meinhardt > <seb.meinha...@gmail.com> wrote: > > > > Our database environment drops idle connections after an hour. My > > application uses the SQLAlchemy connection pool to deal with this. The > > problem is: When a connection is closed, it takes the pool a real long time > > (15-20 minutes) to recover. This happens with both strategies, > > pool_pre_ping and pool_recylce. Apparently, the pool hangs when trying to > > operate on the already dead connections. > > > > Here is an excerpt from the logs. Look at the timestamps! > > > > With pool_pre_ping: > > > > 2018-10-25 12:30:38,866:DEBUG:Connection <cx_Oracle.Connection to > > USER@DATABASE> checked out from pool > > 2018-10-25 12:30:38,868:DEBUG:Pool pre-ping on connection > > <cx_Oracle.Connection to USER@DATABASE> > > 2018-10-25 12:46:26,624:DEBUG:Pool pre-ping on connection > > <cx_Oracle.Connection to USER@DATABASE> failed, will invalidate pool > > 2018-10-25 12:46:26,626:INFO:Disconnection detected on checkout, > > invalidating all pooled connections prior to current timestamp (reason: > > InvalidatePoolError()) > > > > > > With pool_recycle: > > > > 2018-10-25 12:40:51,869:INFO:Connection <cx_Oracle.Connection to > > USER@DATABASE> exceeded timeout; recycling > > 2018-10-25 12:40:51,871:DEBUG:Closing connection <cx_Oracle.Connection to > > USER@DATABASE> > > 2018-10-25 12:56:39,493:ERROR:Exception closing connection > > <cx_Oracle.Connection to USER@DATABASE> > > Traceback (most recent call last): > > File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line > > 314, in _close_connection > > self._dialect.do_close(connection) > > File > > "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line > > 465, in do_close > > dbapi_connection.close() > > cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel > > Process ID: 0 > > Session ID: 540 Serial number: 46277 > > 2018-10-25 12:56:39,674:DEBUG:Created new connection <cx_Oracle.Connection > > to USER@DATABASE> > > > > > > As this is an interactive application, I cannot let the user wait for 16 > > minutes. While I could implement an application side timeout that creates a > > new pool every 59 minutes, it feels like that would be missing the point. > > Is there an SQLAlchemy solution to my problem? Decrease the timeout on > > ping/close? Recycle connections in a background threads? Am I doing > > something fundamentally wrong here? > > > > Here's how SQLAlchemy pings the database: > > https://bitbucket.org/zzzeek/sqlalchemy/src/580d114405f3d200682f3a219dcb9004c81300c7/lib/sqlalchemy/pool/base.py?at=master&fileviewer=file-view-default#base.py-767 > > if pool._pre_ping: > if fairy._echo: > pool.logger.debug( > "Pool pre-ping on connection %s", > fairy.connection) > > result = pool._dialect.do_ping(fairy.connection) > # <-- this is apparently taking 16 minutes > if not result: > if fairy._echo: > pool.logger.debug( > "Pool pre-ping on connection %s failed, " > "will invalidate pool", fairy.connection) > raise exc.InvalidatePoolError() > > https://bitbucket.org/zzzeek/sqlalchemy/src/580d114405f3d200682f3a219dcb9004c81300c7/lib/sqlalchemy/engine/default.py?at=master&fileviewer=file-view-default#default.py-471 > > def do_ping(self, dbapi_connection): > cursor = None > try: > cursor = dbapi_connection.cursor() > try: > cursor.execute(self._dialect_specific_select_one) > finally: > cursor.close() > except self.dbapi.Error as err: > if self.is_disconnect(err, dbapi_connection, cursor): > return False > else: > raise > else: > return True > > So the first question is: do you see this 15-20 minute timeout with a > plain DBAPI connection? From a python prompt, create a cx_Oracle > connection, then kill the connection from the DB side (or wait an > hour). Then try to create a cursor and execute a trivial query. > > If it takes a very long time to return, you need a DBAPI-level fix. > The cx_Oracle docs suggest there is a configurable timeout parameter > (https://cx-oracle.readthedocs.io/en/latest/connection.html#Connection.callTimeout). > > If it fails immediately, repeat the experiment using an SQLAlchemy engine. > > Let us know how you get on, > > Simon > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout.
-- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
