Pool_recycle in Oracle (aka "Oracle has gone away")

[Ben Hitz]

Has anyone ever tested the pool_recycle in Oracle when a user has a maximum idle time?   We are having an issue with a flask/sqla/cx_oracle stack where when the DB_USERs idle time expires, Oracle throws us:

ORA-02396: exceeded maximum idle time, please connect again.  Which is no big deal... except in flask the connection is done at start up and so further db.blah() will not reconnect.
Theoretically we have user/profiles in Oracle with no max idle time but they are (currently) read only. 

We are working on a minimalistic test case in case we actually want to suggest a patch (possibly in cx_Oracle or even flask itself), but I just thought I would throw the issue out there.

It's my understanding that SQLA connection pooling is "supposed to" handle the issue of stale connections with a persistent app.  But possibly I misinterpret.  I did confirm with our DBA that we do NOT have connection pooling enabled at the Oracle level... and my guess is that this is correct if SQLA is handling the pooling... but I could be wrong here as well.

Probably we can refactor the DB connection so that it pings before trying to connect, or otherwise disconnects after a user has done his business (or browser timeouts).    At the moment I am not really sure if this should be implemented at our (flask) app level, with some flask hook, or with an SQLA hook (subclass of pooling method???)... or even if it's just a cx_Oracle driver issue.

I believe, but am not 100% certain that this issue is very similar to the "mysql has gone away" thread that winds it's way through the internet.  However, some basic attempts of using POOL_RECYCLE and
try:
   db.doSomething()
except:
  db.connect // again

Have failed.   But as I say, we have to make a minimal test case to demonstrate where the problem is.

Thanks for you time, and interested to hear thoughts.

Ben

--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO Consortium
Stanford University ** hi...@stanford.edu

[Michael Bayer]

On Oct 16, 2012, at 5:56 PM, Ben Hitz wrote:

> Has anyone ever tested the pool_recycle in Oracle when a user has a maximum idle time? We are having an issue with a flask/sqla/cx_oracle stack where when the DB_USERs idle time expires, Oracle throws us:
>
> ORA-02396: exceeded maximum idle time, please connect again. Which is no big deal... except in flask the connection is done at start up

that's not entirely accurate. The Flask-SQLAlchemy extension checks out a connection from the connection pool at request start, returns it at request end. The pool is responsible for dealing with the lifecycle of database connections and can be confiugured to deal with this. To deal with connections that time out after a certain time idle, use the pool_recycle option: http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html?highlight=pool_timeout#setting-pool-recycle .

[Ben Hitz]

that's not entirely accurate.  The Flask-SQLAlchemy extension checks out a connection from the connection pool at request start, returns it at request end.   The pool is responsible for dealing with the lifecycle of database connections and can be confiugured to deal with this.  To deal with connections that time out after a certain time idle, use the pool_recycle option: http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html?highlight=pool_timeout#setting-pool-recycle .

Oh thanks, I hadn't dug that deeply into FSQLA yet.    This is narrowing down the issue though.  Our anecdotal experience is that pool_recycle is not doing anything with Oracle... hopefully have a test (or ideally a "never mind") follow up in a few days.

Am I correct in assuming that SQLA connection pool is wholly independent of Oracle's connection pooling (which we have disabled)?

Ben

[Michael Bayer]

On Oct 17, 2012, at 3:48 PM, Ben Hitz wrote:

>
> Oh thanks, I hadn't dug that deeply into FSQLA yet. This is narrowing down the issue though. Our anecdotal experience is that pool_recycle is not doing anything with Oracle... hopefully have a test (or ideally a "never mind") follow up in a few days.
>
> Am I correct in assuming that SQLA connection pool is wholly independent of Oracle's connection pooling (which we have disabled)?

yes SQLA's system is just dealing with whatever cx_oracle.connect() gives us, I'm not sure if cx_oracle itself has pooling options but we don't call upon those.

the key aspect of pool_recycle is that it is only checked when the connection is retrieved from the pool. so the app needs to be closing/returning those connections at the end of each request.