See EDIT at the bottom for tested solution
I didn't try it, but maybe using PoolListener is a way to go?
You could do something like this:
class MyListener(sqlalchemy.interfaces.PoolListener):
def __init__(self):
self.retried = False
def checkout(self, dbapi_con, con_record, con_proxy):
try:
dbapi_con.info() # is there any better way to simply check if connection to mysql is alive?
except sqlalchemy.exc.OperationalError:
if self.retried:
self.retried = False
raise # we do nothing
self.retried = True
raise sqlalchemy.exc.DisconnectionError
# next, code according to documentation linked above follows
e = create_engine("url://", listeners=[MyListener()])
This way every time connection is about to be checked out from the pool we test if it's actually connected to the server. If not, we give sqlalchemy one chance to reconnect. After that, if problem is still there, we let it go.
PS: I didn't test if this works.
Edit: As for the Pylons, modifications to the engine initialization showed above would need to be done in your_app.model.init_model (Pylons 0.9.7) or your_app.config.environment.load_environment (Pylons 1.0) function - these are this is the places place where engine instance gets created.
EDIT
Ok. I was able to reproduce described situation. The code above needs some changes in order to work. Below is how it should be done. Also it doesn't matter whether it's 0.9.7 or 1.0.
You need to edit your_app/config/environment.py. Put these exports at top of the file:
import sqlalchemy
import sqlalchemy.interfaces
import _mysql_exceptions
And the end of load_environment function should look like that:
class MyListener(sqlalchemy.interfaces.PoolListener):
def __init__(self):
self.retried = False
def checkout(self, dbapi_con, con_record, con_proxy):
try:
dbapi_con.cursor().execute('select now()')
except _mysql_exceptions.OperationalError:
if self.retried:
self.retried = False
raise
self.retried = True
raise sqlalchemy.exc.DisconnectionError
config['sqlalchemy.listeners'] = [MyListener()]
engine = engine_from_config(config, 'sqlalchemy.')
init_model(engine)
This time I was able to test it (on Pylons 1.0 + SQLAlchemy 0.6.1) and it works. :)
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…