sqlalchemy + MySQL connection timeouts

Question

I have a daemon that uses sqlalchemy to interact with MySQL database. Since interaction is seldom, the connections are prone to timing out. I've tried to fix the problem by setting various flags when creating the database engine, e.g. pool_recycle=3600, but nothing seems to help.

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 continue to get exceptions like the following:

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

However, if I remove the call to connection.close(), the problem goes away. What is going on here? Why isn't sqlalchemy trying to establish a new connection each time I call connect()?

I'm using Python 2.7.3 with sqlalchemy 0.9.8 and MySQL 5.5.40.

Answer 1

the document mention:

MySQL features an automatic connection close behavior,
for connections that have been idle for eight hours or more. To circumvent having this issue, use the pool_recycle option which controls the maximum age of any connection:

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

You can just put "pool_recycle" parameter when the create_engine is invoked.

Answer 2

I'm not 100% sure if this is going to fix your problem or not, but I ran into a similar issue when dealing with mysql. It was only fixed when I used pymysql to connect to the database.

You can install like this:

pip install pymysql

Which will work for both linux and windows (if you have it installed)

Then give your connection string like this:

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,)

On another note, I have found certain queries to break with SQLAlchemy 0.9.8. I had to install version 0.9.3 in order for my applications to not break anymore.