我的SQLAlchemy MySQL连接总是睡不着觉吗？ [英] Is it odd that my SQLAlchemy MySQL connection always ends up sleeping?

问题描述

我有一个使用SQLAlchemy和MySQL的Flask Web应用程序，我已经设置了scopedsession（）。我还有一个teardown_request处理程序，在每个请求完成后调用session.remove（）。出于某种奇怪的原因，如果一天或一天​​以上没有在网络应用程序上发出请求，应用程序将获得Operationalerror：MySQL服务器已经消失。

调试任务，我看了SHOW PROCESSLIST，看到以下内容：

  39817253 | sqladmin | my_host | kb_dev |睡眠| 174 | 
  

174是我的应用程序连接已经睡眠的秒数。它继续计数，如果应用程序没有提出另一个请求。

似乎我的应用程序仍然保持与MySQL的连接，甚至在我的请求完成后！通常只有一个进程，无论我的应用程序有多少请求，同时与否。

我的问题是，如果连接长时间睡眠是正常的？我很确定扩展睡眠正在导致MySQL在一定的超时之后切断连接，这又导致OperationalError：Mysql已经消失错误。

解决方案

SQLAlchemy的默认行为是在引擎内建立连接池：

http://www.sqlalchemy.org/docs/core/engines.html

http：// www .sqlalchemy.org/docs/core/pooling.html

至于断断续续的事情，这是一个已知的MySQL行为，SQLAlchemy提供了pool_recycle国旗解决它。这里有很多描述它的链接：

http://www.sqlalchemy.org/docs/dialects/mysql.html#connection-timeouts

http://www.sqlalchemy.org/docs/core/pooling.html#setting -pool-recycle

http://www.sqlalchemy.org/docs/core/engines.html#sqlalchemy.create_engine （pool_recycle）

< a href =http://www.sqlalchemy.org/trac/wiki/FAQ#MySQLserverhasgoneaway =noreferrer> http://www.sqlalchemy.org/trac/wiki/FAQ#MySQLserverhasgoneaway

从几天前的博客文章：

http://douglatornell.ca/blog/2012/01/08/保持活力/

I have a Flask web application using SQLAlchemy with MySQL, and I have set up a scopedsession(). I also have a teardown_request handler that calls session.remove() after every request is finished. For some odd reason, if no requests are made on the web app for a day or more, the app gets "Operationalerror: MySQL Server has gone away".

In my debugging mission, I looked at SHOW PROCESSLIST and saw the following:

39817253 | sqladmin | my_host | kb_dev   | Sleep   |  174 |

The 174 is the number of seconds the connection from my application has been "sleeping". It keeps counting up if the application doesn't make another request.

It seems like my application holds on to the connection to MySQL even after my request has finished! And there is usually only one process no matter how many requests I make with my application, simultaneously or not.

My question is if it is normal for the connection to be "sleeping" this long? I'm pretty sure the extended sleeping is causing MySQL to cut the connection after a certain timeout which in turn is causing the "OperationalError: Mysql has gone away" error.

解决方案

SQLAlchemy's default behavior is to pool connections within the Engine:

http://www.sqlalchemy.org/docs/core/engines.html

http://www.sqlalchemy.org/docs/core/pooling.html

As far as the disconnect overnight thing, this is a known MySQL behavior, SQLAlchemy provides the pool_recycle flag to work around it. Here are many links which describe it:

http://www.sqlalchemy.org/docs/dialects/mysql.html#connection-timeouts

http://www.sqlalchemy.org/docs/core/pooling.html#setting-pool-recycle

http://www.sqlalchemy.org/docs/core/engines.html#sqlalchemy.create_engine (pool_recycle)

http://www.sqlalchemy.org/trac/wiki/FAQ#MySQLserverhasgoneaway

Blog post from just a few days ago:

http://douglatornell.ca/blog/2012/01/08/staying-alive/

这篇关于我的SQLAlchemy MySQL连接总是睡不着觉吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！