SQLAlchemy/MySQL Lost connection to MySQL server during query

Question

SQLAlchemy (0.9.8) and mysql-5.6.21-osx10.8-x86_64 and MAC OS X 10.3.3 (Yosemite)

I keep getting intermittent:

InterfaceError: (InterfaceError) 2013: Lost connection to MySQL server during query u'SELECT..... '

I have read up a few thread and most cases are resolved by adding this to my.cnf

   max_allowed_packet = 1024M

which should be more than big enough for what I tried to do. After doing this, I step hit it intermittently. And putting this line in /etc/my.cnf:

   log-error = "/Users/<myname>/tmp/mysql.err.log"
   log-warnings = 3

I am hoping to get more details, but all I see is something like this:

   [Warning] Aborted connection 444 to db: 'dbname' user: 'root' host: 'localhost' (Got an error reading communication packets)

I have reached a point where i think more detail better logging may help, or if there's something else i could try before this.

Thanks.

Answer 1

looks like your MySQL connection is timing out after a long period of inactivity, I bet it won't happen if you're constantly querying your DB with existing settings. There are couple of settings on both MySQL and sql sides which should resolve this issue:

1. check your SQLa engine's pool_recycle value, try different / smaller value, e.g. 1800 (secs). If you're reading DB settings from file, set it as

   pool_recycle: 1800

otherwise specify it during engine init, e.g.

from sqlalchemy import create_engine
e = create_engine("mysql://user:pass@localhost/db", pool_recycle=1800)

2. check / modify your wait_timeout MySQL variable, see https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout which is the number of seconds the server waits for activity on a noninteractive connection before closing it. e.g.

   show global variables like 'wait_timeout';

find a combination that works for your environment.

Answer 2

There are two params that could help, pool_recycle, pool_pre_ping.

pool_recycle decides the seconds to recycle the connection after it is inactivity. The default value of mysql is 8 hours, and the default value of sqlalchemy is -1, which means not to recycle, this is the gap, if mysql has recycled the connection and sqlalchemy did not, the Lost connection exception will be raise.

pool_pre_ping will test the connection liveness, as my understanding this could be used as a back-up strategy, if a connection is recycled by mysql but not recognized by sqlalchemy, sqlalchemy will do a check, and avoid to use an invalid connection.

create_engine(<mysql conn url>, pool_recycle=60 * 5, pool_pre_ping=True)