问题描述
我有一个Web应用程序，该应用程序运行独立于用户会话的长时间作业.为了实现这一点，我为线程本地的Flask-SQLAlchemy会话提供了一个实现.问题是一天几次，访问网站时出现MySQL server has gone away错误.该站点始终在刷新时加载.我认为该问题与这些线程本地会话有关，但我不确定.
I have a web application that runs long jobs that are independent of user sessions. To achieve this, I have an implementation for a thread-local Flask-SQLAlchemy session. The problem is a few times a day, I get a MySQL server has gone away error when I visit my site. The site always loads upon refresh. I think the issue is related to these thread-local sessions, but I'm not sure.
这是我对线程本地会话作用域的实现:
This is my implementation of a thread-local session scope:
@contextmanager
def thread_local_session_scope():
"""Provides a transactional scope around a series of operations.
Context is local to current thread.
"""
# See this StackOverflow answer for details:
# http://stackoverflow.com/a/18265238/1830334
Session = scoped_session(session_factory)
threaded_session = Session()
try:
yield threaded_session
threaded_session.commit()
except:
threaded_session.rollback()
raise
finally:
Session.remove()
这是我的标准Flask-SQLAlchemy会话:
And here is my standard Flask-SQLAlchemy session:
@contextmanager
def session_scope():
"""Provides a transactional scope around a series of operations.
Context is HTTP request thread using Flask-SQLAlchemy.
"""
try:
yield db.session
db.session.commit()
except Exception as e:
print 'Rolling back database'
print e
db.session.rollback()
# Flask-SQLAlchemy handles closing the session after the HTTP request.
然后我像这样使用两个会话上下文管理器:
Then I use both session context managers like this:
def build_report(tag):
report = _save_report(Report())
thread = Thread(target=_build_report, args=(report.id,))
thread.daemon = True
thread.start()
return report.id
# This executes in the main thread.
def _save_report(report):
with session_scope() as session:
session.add(report)
session.commit()
return report
# These executes in a separate thread.
def _build_report(report_id):
with thread_local_session_scope() as session:
report = do_some_stuff(report_id)
session.merge(report)
引擎配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://<username>:<password>@<server>:3306/<db>?charset=utf8'
app.config['SQLALCHEMY_POOL_RECYCLE'] = 3600
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
推荐答案
尝试添加
app.teardown_request(Exception=None)
装饰器，在每个请求的末尾执行.我目前正在遇到类似的问题，好像今天我已经使用来解决了.
Decorator, which executes at the end of each request. I am currently experiencing a similar issue, and it seems as if today I have actually resolved it using.
@app.teardown_request
def teardown_request(exception=None):
Session.remove()
if exception and Session.is_active:
print(exception)
Session.rollback()
我不只使用Flask-SQLAlchemy Raw SQLAlchemy，所以它可能对您有所不同.
I do not use Flask-SQLAlchemy Only Raw SQLAlchemy, so it may have differences for you.
来自文档
就我而言，我为每个请求打开一个新的scoped_session，要求我在每个请求结束时将其删除(Flask-SQLAlchemy可能不需要此).另外，如果在上下文中发生了催泪交接请求功能，则会将其传递给Exception.在这种情况下，如果发生异常(可能导致交易未删除或需要回滚)，我们将检查是否存在异常，然后回滚.
In my case, I open a new scoped_session for each request, requiring me to remove it at the end of each request (Flask-SQLAlchemy may not need this). Also, the teardown_request function is passed an Exception if one occured during the context. In this scenario, if an exception occured (possibly causing the transaction to not be removed, or need a rollback), we check if there was an exception, and rollback.
如果这对于我自己的测试不起作用，那么我接下来要做的就是在每次拆机时都按session.commit()，以确保一切都正常进行
If this doesnt work for my own testing, the next thing I was going to do was a session.commit() at each teardown, just to make sure everything is flushing
更新:8小时后，MySQL也似乎使连接无效，从而导致会话被破坏.
UPDATE : it also appears MySQL invalidates connections after 8 hours, causing the Session to be corrupted.
在引擎配置上设置pool_recycle=3600，或设置为< MySQL超时.结合适当的会话范围(结束会话)应该可以做到这一点.
set pool_recycle=3600 on your engine configuration, or to a setting < MySQL timeout. This in conjunction with proper session scoping (closing sessions) should do it.
这篇关于此线程本地的Flask-SQLAchemy会话是否会导致"MySQL服务器已消失"?错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！