python - flask -SQLAlchemy -"MySQL Connection not available."

Question

在大约一个小时的不活动之后，任何查询数据库的尝试都会挂起大约 30 秒，然后导致 500 内部服务器错误。基本错误信息如下:

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

我已将完整的堆栈跟踪放在问题的末尾。

现在我访问了this question并在那里尝试了解决方案，但无济于事。我已经使 pool_recycle 值大于、等于和小于 MySQL 超时值(当前为 28800s)。

还有哪些值得尝试的事情？

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 1

所以我得到了一个可以接受的解决方案，但不是一个完美的解决方案。发生此错误的唯一一次是当我在一段时间不活动后尝试登录时。我的原代码如下:

@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()

这是我所做的更改，它阻止了 500 个内部服务器错误的发生。

@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()

这不是一个完美解决方案的原因是，在我等待第一个 User.query 失败并再次尝试相同查询时会出现延迟。这也不是很好的解决方案，但我不再看到错误消息，即使在长时间不活动后页面也能按预期工作。

更新

事实证明，消除此错误并使一切恢复正常的真正解决方案是将 MySQL wait_timeout 和 interactive_timeout 从 28800 秒更改为与我的 sqlalchemy 池回收选项相同的值，该选项设置为 1600 .

问题消失了!