SQLAlchemy error MySQL server has gone away

Question

Error OperationalError: (OperationalError) (2006, 'MySQL server has gone away') i'm already received this error when i coded project on Flask, but i cant understand why i get this error.

I have code (yeah, if code small and executing fast, then no errors) like this \

db_engine = create_engine('mysql://[email protected]/mind?charset=utf8', pool_size=10, pool_recycle=7200) Base.metadata.create_all(db_engine)  Session = sessionmaker(bind=db_engine, autoflush=True) Session = scoped_session(Session) session = Session()  # there many classes and functions  session.close() 

And this code returns me error 'MySQL server has gone away', but return it after some time, when i use pauses in my script.

Mysql i use from openserver.ru (it's web server like such as wamp).

Thanks..

Answer 1

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 exc from sqlalchemy import event from 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_engine e = 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() 

Answer 2

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.