Sqlalchemy + MySQL connection timeout

Question

I have a daemon that uses sqlalchemy to interact with a MySQL database. Because communication is rare, connections are subject to temporary disconnection. I tried to fix the problem by setting various flags when creating the database engine, for example. pool_recycle=3600 , but nothing helps.

To help me debug the problem, I set the timeout of my local mysql server to 10 seconds and tried the following program.

 import time import sqlalchemy engine = sqlalchemy.engine.create_engine("mysql://localhost") while True: connection = engine.connect() result = connection.execute("SELECT 1") print result.fetchone() connection.close() time.sleep(15) 

Surprisingly, I keep getting exceptions, such as:

 sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has gone away') 

However, if I delete the connection.close() call, the problem will disappear. What's going on here? Why is sqlalchemy trying to establish a new connection every time I call connect() ?

I am using Python 2.7.3 with sqlalchemy 0.9.8 and MySQL 5.5.40.

Answer 1

the document mentions:

MySQL has a function to automatically close the connection,
for compounds that have stood idle for eight hours or more. To work around this problem, use the pool_recycle parameter, which controls the maximum age of any connection:

engine = create_engine ('mysql + mysqldb: // ...', pool_recycle = 3600)

You can simply specify the pool_recycle parameter when calling create_engine.

Answer 2

I am not 100% sure if this will fix your problem or not, but I ran into a similar problem when working with mysql. This was fixed when I used pymysql to connect to the database.

You can set the following:

 pip install pymysql 

What will work for both Linux and windows (if you installed it)

Then enter the connection string as follows:

 import time import sqlalchemy engine = sqlalchemy.engine.create_engine("mysql+pymysql://localhost") while True: connection = engine.connect() result = connection.execute("SELECT 1") print result.fetchone() connection.close() time.sleep(15) 

I get the following output when I run it:

 (1,) (1,) (1,) (1,) 

In another note, I found some queries for breaking with SQLAlchemy 0.9.8. I had to install version 0.9.3 so that my applications no longer break.