SQLAlchemy error MySQL server has gone away

SQLAlchemy now has a great write-up on how you can use pinging to be pessimistic about your connection's freshness:

http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic

From there,

from sqlalchemy import excfrom sqlalchemy import eventfrom sqlalchemy.pool import Pool@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()

And a test to make sure the above works:

from sqlalchemy import create_enginee = create_engine("mysql://scott:tiger@localhost/test", echo_pool=True)c1 = e.connect()c2 = e.connect()c3 = e.connect()c1.close()c2.close()c3.close()# pool size is now three.print "Restart the server"raw_input()for i in xrange(10):    c = e.connect()    print c.execute("select 1").fetchall()    c.close()

Looking at the mysql docs, we can see that there are a bunch of reasons why this error can occur. However, the two main reasons I've seen are:

1) The most common reason is that the connection has been dropped because it hasn't been used in more than 8 hours (default setting)

By default, the server closes the connection after eight hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld

I'll just mention for completeness the two ways to deal with that, but they've already been mentioned in other answers:

A: I have a very long running job and so my connection is stale. To fix this, I refresh my connection:

create_engine(conn_str, pool_recycle=3600)  # recycle every hour

B: I have a long running service and long periods of inactivity. To fix this I ping mysql before every call:

create_engine(conn_str, pool_pre_ping=True)

2) My packet size is too large, which should throw this error:

_mysql_exceptions.OperationalError: (1153, "Got a packet bigger than 'max_allowed_packet' bytes")

I've only seen this buried in the middle of the trace, though often you'll only see the generic _mysql_exceptions.OperationalError (2006, 'MySQL server has gone away'), so it's hard to catch, especially if logs are in multiple places.

The above doc say the max packet size is 64MB by default, but it's actually 16MB, which can be verified with SELECT @@max_allowed_packet

To fix this, decrease packet size for INSERT or UPDATE calls.

I just faced the same problem, which is solved with some effort. Wish my experience be helpful to others.

Fallowing some suggestions, I used connection pool and set pool_recycle less than wait_timeout, but it still doesn't work.

Then, I realized that global session maybe just use the same connection and connection pool didn't work. To avoid global session, for each request generate a new session which is removed by Session.remove() after processing.

Finally, all is well.