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