Kamil Gorlo wrote:
> On Thu, Jun 4, 2009 at 4:20 PM, Michael Bayer<mike...@zzzcomputing.com> wrote:
>> the connection went from good to dead within a few seconds (assuming SQL
>> was successfully emitted on the previous checkout). Your database was
>> restarted or a network failure occurred.
>
> There is no other option? I'm pretty sure that DB was not restarted,
> network failure is of course possible but still.. (this is the same
> LAN).
Another cause of "went away" messages is a query that exceeds the
configured memory resources on the server. Taking a look at MySQL's
logs may shed more light & give hints for which buffers need tuning if
that's the problem.
> But, assuming this is external problem - is there any way to tell
> SQLAlchemy to try another connection for the same request (instead of
> returning HTTP 500 for user), or maybe other pooling strategy or even
> something else?
Yes, with a simple pool event listener you can ensure the liveliness of
connections before the pool hands them out for use. Usage example is
attached.
Cheers,
Jason
class LookLively(object):
"""Ensures that MySQL connections checked out of the
pool are alive."""
def checkout(self, dbapi_con, con_record, con_proxy):
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError, ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
# caught by pool, which will retry with a new connection
raise exc.DisconnectionError()
else:
raise
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
from sqlalchemy import exc
class LookLively(object):
"""Ensures that MySQL connections checked out of the pool are alive.
Specific to the MySQLdb DB-API. Note that this can not totally
guarantee live connections- the remote side can drop the connection
in the time between ping and the connection reaching user code.
This is a simplistic implementation. If there's a lot of pool churn
(i.e. implicit connections checking in and out all the time), one
possible and easy optimization would be to add a timer check:
1) On check-in, record the current time (integer part) into the
connection record's .properties
2) On check-out, compare the current integer time to the (possibly
empty) record in .properties. If it is still the same second as
when the connection was last checked in, skip the ping. The
connection is probably fine.
Something much like this logic will go into the SQLAlchemy core
eventually.
-jek
"""
def checkout(self, dbapi_con, con_record, con_proxy):
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError, ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
raise exc.DisconnectionError()
else:
raise
# To see a connection die post-ping, take the sleep out of reap()
# below and run this in a tight loop. It should happen eventually on
# a fast machine.
#
# $ while thisscript.py; do echo; done
if __name__ == '__main__':
import sys, time
if len(sys.argv) > 1:
from pkg_resources import require
require('mysql-python==%s' % sys.argv[1])
from sqlalchemy import *
e = create_engine('mysql:///test?unix_socket=/var/tmp/mysql.sock',
max_overflow=0, pool_size=2, # constrain our test
listeners=[LookLively()])
# reserve a connection.
reaper = e.connect()
def reap(id):
reaper.execute(text('KILL :thread'), thread=id)
time.sleep(0.15) # give the thread a chance to die
c2 = e.connect()
c2_threadid = c2.scalar('SELECT CONNECTION_ID()')
print "Connection c2 id=%s" % c2_threadid
# return c2 to the pool. (the db-api connection will remain open)
c2.close()
del c2
reap(c2_threadid)
c2 = e.connect()
new_threadid = c2.scalar('SELECT CONNECTION_ID()')
print "Connection c2 now has id=%s" % new_threadid
try:
# connection is still alive, kill it mid-stream
reap(new_threadid)
c2.execute('SELECT 1')
assert False
except Exception, ex:
print "Expected: Did not reconnect mid-transaction, exception:", ex
c2 = e.connect()
final_threadid = c2.scalar('SELECT CONNECTION_ID()')
print "Connection c2 now has id=%s" % final_threadid
sys.exit(0)
