Dan Safee Dan Safee - 18 days ago 10
Python Question

Flask-SQLAlchemy -"MySQL Connection not available."

After about an hour of inactivity, any attempt to query the database hangs for about 30 seconds and then results in a 500 Internal Server Error. The basic error message is as follows:

sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available.


I've put the full stack trace at the end of the question.

Now I've visited this question and tried the solution there, but to no avail. I've made the pool_recycle value greater than, equal to, and less than the MySQL timeout value (28800s currently).

What are some other things worth trying?

Traceback (most recent call last):
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/flask/app.py", line 1817, in wsgi_app
response = self.full_dispatch_request()
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/flask/app.py", line 1477, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/flask/app.py", line 1381, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/flask/_compat.py", line 33, in reraise
raise value
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/flask/app.py", line 1475, in full_dispatch_request
rv = self.dispatch_request()
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/flask/app.py", line 1461, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/var/www/html/benefits_app/benefits_app/auth.py", line 18, in login
user = User.query.filter_by(username=form.username.data).first()
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2445, in first
ret = list(self[0:1])
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2281, in __getitem__
return list(res)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2516, in __iter__
return self._execute_and_instances(context)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2531, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
return meth(self, multiparams, params)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
compiled_sql, distilled_params
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1078, in _execute_context
None, None)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
exc_info
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 181, in reraise
raise value.with_traceback(tb)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1073, in _execute_context
context = constructor(dialect, self, conn, *args)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 556, in _init_compiled
self.cursor = self.create_cursor()
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 745, in create_cursor
return self._dbapi_connection.cursor()
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/sqlalchemy/pool.py", line 847, in cursor
return self.connection.cursor(*args, **kwargs)
File "/var/www/html/benefits_app/benefits_app/venv/lib/python3.4/site-packages/mysql/connector/connection.py", line 1383, in cursor
raise errors.OperationalError("MySQL Connection not available.")
sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available. [SQL:'blah blah query']

Answer

So I got to an acceptable solution, but not a perfect one. The one only time this error occurred was when I was attempting to log in after a period of inactivity. My original code is as follows:

@auth_blueprint.route('/login', methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        try:
            user = User.query.filter_by(username=form.username.data).first()
        except:
            user = User.query.filter_by(username=form.username.data).first()

Here is the change I made that stopped the 500 internal server errors from occurring.

@auth_blueprint.route('/login', methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        try:
            user = User.query.filter_by(username=form.username.data).first()
        except:
            db.session.rollback()
            user = User.query.filter_by(username=form.username.data).first()

The reason this is not a perfect solution is that there is a delay while I wait for the first User.query to fail and try the same query again. It is also not very nice solution, but I no longer see an error message and the page works as intended even after an extended period of inactivity.

UPDATE

As it turns out, the REAL solution that removed this error and made everything work great again was to change the MySQL wait_timeout and interactive_timeout from 28800 seconds down to the same value as my pool recycle option for sqlalchemy, which was set to 1600.

Problems Gone!

Comments