避免“MySQL 服务器已经消失"在不常使用的 Python/Flask 服务器上使用 SQLAlchemy [英] Avoiding "MySQL server has gone away" on infrequently used Python / Flask server with SQLAlchemy

问题描述

如果 Flask/SQLAlchemy 不存在，如何配置以创建新的数据库连接?

How can Flask / SQLAlchemy be configured to create a new database connection if one is not present?

我有一个使用 SQLAlchemy 的不常访问的 Python/Flask 服务器.它每隔几天就会被访问一次，并且在第一次访问时它经常抛出MySQL 服务器已经消失"的错误.后续的页面浏览量还好，但出现这个初始错误看起来很不专业.

I have an infrequently visited Python / Flask server which uses SQLAlchemy. It gets visited every couple of days, and on the first visit it often throws a "MySQL server has gone away" error. Subsequent page views are fine, but it looks unprofessional to have this initial error.

我想知道处理这个问题的正确方法 - 像让一个非常长的超时时间"这样的建议(在这种情况下大约需要 4 天)似乎不正确.如何测试是否缺少数据库连接并在需要时创建一个?

I'd like to know the correct way to handle this - advice like "make a really long time out", which would be about 4 days long in this case, doesn't seem correct. How can I test for the lack of a database connection and create one if needed?

推荐答案

我以前遇到过这个问题，发现处理它的方法是不保留会话.问题是您试图保持连接打开的时间太长.相反，在 __init__.py 或在任何地方导入的实用程序包中使用线程局部作用域会话:

I've had trouble with this before, and found that the way to handle it is by not keeping sessions around. The trouble is you are trying to keep a connection open for way too long. Instead, use a thread local scoped session like so either in __init__.py or in a utility package that you import everywhere:

from sqlalchemy.orm import scoped_session, sessionmaker
Session = scoped_session( sessionmaker() )

然后设置您的引擎和元数据一次.这允许您在每次连接/断开连接时跳过配置机制.之后，您可以像这样进行数据库工作:

Then set up your engines and metadata once. This allows you to skip configuration mechanics every time you connect/disconnect. After that, you can do your db work like this:

session = Session()
someObject = session.query( someMappedClass ).get( someId )
# use session like normal ...
session.close()

如果您想保留旧对象并且不想让会话保持打开状态，那么您可以使用上述模式并像这样重用旧对象:

If you want to hold on to old objects and you don't want to leave your session open, then you can use the above pattern and reuse old objects like this:

session = Session()
someObject = session.merge( someObject )
# more db stuff
session.close()

关键是，您要打开会话，完成工作，然后关闭会话.这可以很好地避免超时..merge 和 .add 有很多选项，允许您包含对分离对象所做的更改或从数据库加载新数据.文档非常冗长，但是一旦您知道要查找的内容，可能会更容易找到.

The point is, you want to open your session, do your work, then close your session. This avoids timeouts very well. There are lots of options for .merge and .add that allow you to either include changes you've made to detached objects or to load new data from the db. The docs are very verbose, but once you know what you are looking for it might be a little easier to find.

要真正做到这一点并防止 MySQL消失"，您需要解决连接池保持连接打开时间过长并为您检查旧连接的问题.

To actually get all the way there and prevent the MySQL from "going away", you need to solve the issue of your connection pool keeping connections open too long and checking out an old connection for you.

要获得新的连接，您可以在 create_engine 调用中设置 pool_recycle 选项.将此 pool_recycle 设置为结帐之间连接池中您希望创建新连接而不是返回现有连接的秒数.

To get a fresh connection, you can set the pool_recycle option in your create_engine call. Set this pool_recycle to the number of seconds of time in the connection pool between checkouts that you want a new connection to be created instead of an existing connection to be returned.

这篇关于避免“MySQL 服务器已经消失"在不常使用的 Python/Flask 服务器上使用 SQLAlchemy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！