how to recycle connections grown stale after checkout

[Andy Crain]

Hi,

What is the best way to forcefully/manually “recycle” a checked out connection that I know to have become stale since it was checked out? And by stale, I mean this is a MySQL connection that has idled beyond MySQL’s wait_timeout (triggering a "MySQL has gone away" error when it's eventually accessed). While I’ve set pool_recycle to a value less than MySQL’s wait_timeout, this doesn’t help, as pool_recycle recycles connections only at checkout, and my connection is live at checkout but expiring after being checked out (due to some unavoidable long-running code).

Here’s an illustration of the problem:

    engine = create_engine('mysql://…', pool_recycle=10)
    Session = sessionmaker(bind=engine)
    Base = declarative_base()
    session = Session()

    row = engine.execute(text("show session variables like 'wait_timeout'")).first()
    wait_timeout = int(row[1])

    print session.query(MyModel).count()
    sleepfor = wait_timeout + 5
    print "Sleep for wait_timeout + 5 (sleeping for {} seconds)...".format(sleepfor)
    sleep(sleepfor)
    # this next query will fail with OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away')
    print session.query(MyModel).count()

I’ve come up with a few workarounds that seem to work, including:

a) creating a new session for the second query, e.g.

    sleep(sleepfor)
    new_session = Session()
    print new_session.query(MyModel).count()

...and b) closing the session before the second query in order to trigger a recycle, e.g.

    sleep(sleepfor)
    session.close()
    print session.query(MyModel).count()

But these seem heavy handed and wrong. What I’d like to do is just discard this stale connection (in my session and in the pool) and get a fresh one, but I can’t determine how to.

Thanks for any help.

Andy

[Jonathan Vanasco]

http://docs.sqlalchemy.org/en/latest/core/pooling.html#dealing-with-disconnects

[Mike Bayer]

On 6/29/15 2:33 PM, Andy Crain wrote:

But these seem heavy handed and wrong. What I’d like to do is just discard this stale connection (in my session and in the pool) and get a fresh one, but I can’t determine how to.

at the bottom of that section, the important part is about invalidation:

http://docs.sqlalchemy.org/en/rel_1_0/core/pooling.html#more-on-invalidation

specifically invalidate():

http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html?highlight=invalidate#sqlalchemy.engine.Connection.invalidate


from session:

session.connection().invalidate()

however, you've now killed off your transaction, and you will lose whatever isn't committed on it.    If you don't need the transaction to last this long, then you shouldn't have a connection checked out - the Session only has a connection checked out when you are in a transaction.  So I think the original assertion "due to some unavoidable long-running code" doesn't really make any sense, especially if you are using an ORM Session.    If this isn't impacting you because you are on MyISAM and are essentially using autocommit, then just set your Session to use autocommit: http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#autocommit-mode; no more stale connections.

Thanks for any help.

Andy

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Andy Crain]

Jonathan,

Thanks, but I'm attempting to deal with connections that have expired after checkout. The strategies discussed at that URL address freshness of connections upon checkout. From that page: 

 Note that the invalidation only occurs during checkout - not on any connections that are held in a checked out state.

Thanks,

Andy

On Monday, June 29, 2015 at 3:46:29 PM UTC-4, Jonathan Vanasco wrote:

http://docs.sqlalchemy.org/en/latest/core/pooling.html#dealing-with-disconnects

[Andy Crain]

Michael,

Thanks very much. This helps.

I'm using InnoDB without autocommit. The reason for the odd, long-idling connection is that I'm actually using Flask-SQLAlchemy, and the long-idling connection is held by Flask-SQLAlchemy's primary, request-scoped session. I do some long-running, non-transactional work in my request, then come back to the session and try to commit, and the connection, checked out at the start of the request, has become stale and been dropped by MySQL. Although this is specific to Flask-SQLAlchemy, the actual events are reproducible in SQLAlchemy (as above) so I figured it must be common and asked here.

I recognize that session.connection().invalidate(), or close(), would result in loss of session state, but I need a way to pick up at this point and continue with additional queries even after the disconnect, so getting a fresh connection at that point seemed like the best approach.

Thanks,

Andy