MySQL server has gone away - Disconnect handling via checkout event handler doesn't work

Question

Update 3/4:

I've done some testing and proved that using checkout event handler to check disconnects works with Elixir. Beginning to think my problem has something to do with calling session.commit() from a subprocess? Update: I just disproved myself by calling session.commit() in a subprocess, updated example below. I'm using the multiprocessing module to create the subprocess.

Here's the code that shows how it should work (without even using pool_recycle!):

from sqlalchemy import exc
from sqlalchemy import event
from sqlalchemy.pool import Pool
from elixir import *
import multiprocessing as mp

class SubProcess(mp.Process):
    def run(self):
        a3 = TestModel(name="monkey")
        session.commit()

class TestModel(Entity):
    name = Field(String(255))

@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    cursor = dbapi_connection.cursor()
    try:
        cursor.execute("SELECT 1")
    except:
        # optional - dispose the whole pool
        # instead of invalidating one at a time
        # connection_proxy._pool.dispose()

        # raise DisconnectionError - pool will try
        # connecting again up to three times before raising.
        raise exc.DisconnectionError()
    cursor.close()

from sqlalchemy import create_engine
metadata.bind = create_engine("mysql://foo:bar@localhost/some_db", echo_pool=True)
setup_all(True)

subP = SubProcess()

a1 = TestModel(name='foo')
session.commit()

# pool size is now three.

print "Restart the server"
raw_input()

subP.start()

#a2 = TestModel(name='bar')
#session.commit()

Update 2:

I'm forced to find another solution as post 1.2.2 versions of MySQL-python drops support for the reconnect param. Anyone got a solution? :\

Update 1 (old-solution, doesn't work for MySQL-python versions > 1.2.2):

Found a solution: passing connect_args={'reconnect':True} to the create_engine call fixes the problem, automagically reconnects. Don't even seem to need the checkout event handler.

So, in the example from the question:

metadata.bind = create_engine("mysql://foo:bar@localhost/db_name", pool_size=100, pool_recycle=3600, connect_args={'reconnect':True})

Original question:

Done quite a bit of Googling for this problem and haven't seem to found a solution specific to Elixir - I'm trying to use the "Disconnect Handling - Pessimistic" example from the SQLAlchemy docs to handle MySQL disconnects. However, when I test this (by restarting the MySQL server), the "MySQL server has gone away" error is raised before before my checkout event handler.

Here's the code I use to initialize elixir:

##### Initialize elixir/SQLAlchemy
# Disconnect handling
from sqlalchemy import exc
from sqlalchemy import event
from sqlalchemy.pool import Pool

@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    logging.debug("***********ping_connection**************")
    cursor = dbapi_connection.cursor()
    try:
        cursor.execute("SELECT 1")
    except:
        logging.debug("######## DISCONNECTION ERROR #########")            
        # optional - dispose the whole pool
        # instead of invalidating one at a time
        # connection_proxy._pool.dispose()

        # raise DisconnectionError - pool will try
        # connecting again up to three times before raising.
        raise exc.DisconnectionError()
    cursor.close()

metadata.bind= create_engine("mysql://foo:bar@localhost/db_name", pool_size=100, pool_recycle=3600)

setup_all()

I create elixir entity objects and save them with session.commit(), during which I see the "ping_connection" message generated from the event defined above. However, when I restart the mysql server and test it again, it fails with the mysql server has gone away message just before the ping connection event.

Here's the stack trace starting from the relevant lines:

  File "/usr/local/lib/python2.6/dist-packages/elixir/entity.py", line 1135, in get_by
    return cls.query.filter_by(*args, **kwargs).first()
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1963, in first
    ret = list(self[0:1])
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1857, in __getitem__
    return list(res)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 2032, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 2047, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1399, in execute
    params)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1532, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1640, in _execute_context
    context)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1633, in _execute_context
    context)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/default.py", line 330, 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') 

Answer 1

The final workaround was calling session.remove() in the start of methods before manipulating and loading elixir entities. What this does is it will return the connection to the pool, so that when it's used again, the pool's checkout event will be fired, and our handler will detect the disconnection. From SQLAlchemy docs:

It’s not strictly necessary to remove the session at the end of the request - other options include calling Session.close(), Session.rollback(), Session.commit() at the end so that the existing session returns its connections to the pool and removes any existing transactional context. Doing nothing is an option too, if individual controller methods take responsibility for ensuring that no transactions remain open after a request ends.

Quite an important little piece of information I wish it were mentioned in the elixir docs. But then I guess it assumes prior knowledge with SQLAlchemy?

Answer 2

the actual problem is sqlalchemy giving you the same session every time you call the sessionmaker factory. Due to this it can happen that a later query is performed with a much earlier opened session as long as you did not call session.remove() on the session. Having to remember calling remove() every time you request a session however is no fun and sqlalchemy provides a much simpler thing: contexual "scoped" sessions.

To create a scoped session simply wrap your sessionmaker:

from sqlalchemy.orm import scoped_session, sessionmaker
Session = scoped_session(sessionmaker())

This way you get a contexual bound session every time you call the factory, meaning sqlalchemy calls the session.remove() for you as soon as the calling function exits. See here: sqlalchemy - lifespan of a contextual session