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)