OperationalError: MySQL Connection not available

Question

I'm using Flask-SQLAlchemy 1.0, Flask 0.10, SQLAlchemy 0.8.2, and Python 2.7.5. I'm connecting to MySQL 5.6 with Oracle's MySQL Connector/Python 1.0.12.

When I restart my web server (either Apache2 or Flask's built-in), I receive the exception OperationalError: MySQL Connection not available after MySQL's wait_timeout expires (default 8 hours).

I've found people with similar problems and explicitly set SQLALCHEMY_POOL_RECYCLE = 7200, even though that's Flask-SQLAlchemy's default. When I put a breakpoint here, I see that the teardown function is successfully calling session.remove() after each request. Any ideas?

Update 7/21/2014:

Since this question continues to receive attention, I must add that I did try some of the proposals. Two of my attempts looked like the following:

First:

@contextmanager
def safe_commit():
    try:
        yield
        db.session.commit()
    except:
        db.session.rollback()
        raise

This allowed me to wrap my commit calls like so:

with safe_commit():
    model = Model(prop=value)
    db.session.add(model)

I am 99% certain that I did not miss any db.session.commit calls with this method and I still had problems.

Second:

def managed_session():
    def decorator(f):
        @wraps(f)
        def decorated_function(*args, **kwargs):
            try:
                response = f(*args, **kwargs)
                db.session.commit()
                return response
            except:
                db.session.rollback()
                raise
            finally:
                db.session.close()
        return decorated_function
    return decorator

To further ensure I wasn't missing any commit calls, I made a Flask wrapper that enabled code such as (if I remember correctly):

@managed_session()
def hello(self):
    model = Model(prop=value)
    db.session.add(model)

    return render_template(...

Unfortunately, neither method worked. I also recall trying to issue SELECT(1) calls in an attempt to re-establish the connection, but I don't have that code anymore.

To me, the bottom line is MySQL/SQL Alchemy has issues. When I migrated to Postgres, I didn't have to worry about my commits. Everything just worked.

Answer 1

I ran across the same issue recently - the first request to the MYSQL database after a long period of FLASK & SQLAlchemy application inactivity (at least 8 hours) results in an unhandled exception, which in turn implies 500 Internal Server Error: Connection Unavailable. All subsequent requests are just fine.

I managed to boil down the problem to MYSQL connection by decreasing the @@session.wait_timeout (and @@global just in case) value to 5 seconds. Then every odd request was just alright, while every second after 5-plus-second pause failed. The conclusion was obvious - SQLAlchemy was using open, but timeouted on the database end connection.

Solution

In my case it turned out the solution is spelled out in the SQLAlchemy – MYSQL has gone away blog post:

The first thing to make sure of is [...] the value of pool_recycle should be less than your MYSQLs wait_timeout value.

In MYSQL documentation you can find wait_timeout defaults to 8 hours (28 800 seconds), while SQLAlchemy engine's pool_recycle default value is -1, that entails no connection recycle whatsoever. I simply passed the value of 21 600 (6 hours) to the create_engine function and the error is gone.