Handle mysql restart in SQLAlchemy

Question

My Pylons app uses local MySQL server via SQLAlchemy and python-MySQLdb. When the server is restarted, open pooled connections are apparently closed, but the application doesn't know about this and apparently when it tries to use such connection it receives "MySQL server has gone away":

File '/usr/lib/pymodules/python2.6/sqlalchemy/engine/default.py', line 277 in do_execute
  cursor.execute(statement, parameters)
File '/usr/lib/pymodules/python2.6/MySQLdb/cursors.py', line 166 in execute
  self.errorhandler(self, exc, value)
File '/usr/lib/pymodules/python2.6/MySQLdb/connections.py', line 35 in defaulterrorhandler
  raise errorclass, errorvalue
OperationalError: (OperationalError) (2006, 'MySQL server has gone away')

This exception is not caught anywhere so it bubbles up to the user. If I should handle this exception somewhere in my code, please show the place for such code in a Pylons WSGI app. Or maybe there is a solution in SA itself?

Answer 1

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. :)

Answer 2

You can use SQLAlchemy proxy for exception handling on each sql query:

from sqlalchemy.interfaces import ConnectionProxy
class MyProxy(ConnectionProxy):
    def cursor_execute(self, execute, cursor, statement, parameters, context, executemany):
        try:
            return execute(cursor, statement, parameters, context)
        except sqlalchemy.exc.OperationalError:
            # Handle this exception
            pass

To connect this proxy you must do that in config/enviroment.py

engine = engine_from_config(config, 'sqlalchemy.', proxy=MyProxy())

Or write middleware for exception handling on each http query:

class MyMiddleware(object):
    def __init__(self, app):
        self.app = app

    def __call__(self, environ, start_response):
        try:
            return self.app(environ, start_response)
        except sqlalchemy.exc.OperationalError:
            start_response(
                '500 Internal Server Error',
                [('content-type', 'text/html')])
            return ['error page\n']

To connect this middleware in stack order as you need or simply in config/middleware.py:

# CUSTOM MIDDLEWARE HERE (filtered by error handling middlewares)
app = MyMiddleware(app)