SQLAlchemy/MySQL查询期间失去与MySQL服务器的连接 [英] SQLAlchemy/MySQL Lost connection to MySQL server during query

问题描述

SQLAlchemy(0.9.8)和mysql-5.6.21-osx10.8-x86_64和MAC OS X 10.3.3(Yosemite)

SQLAlchemy (0.9.8) and mysql-5.6.21-osx10.8-x86_64 and MAC OS X 10.3.3 (Yosemite)

我不断断断续续:

InterfaceError: (InterfaceError) 2013: Lost connection to MySQL server during query u'SELECT..... '

我已经阅读了一些线程，大多数情况下，将其添加到my.cnf中即可解决

I have read up a few thread and most cases are resolved by adding this to my.cnf

   max_allowed_packet = 1024M

对于我尝试做的事情，它应该足够大.完成此操作后，我会间歇性地踩到它.并将此行放在/etc/my.cnf中:

which should be more than big enough for what I tried to do. After doing this, I step hit it intermittently. And putting this line in /etc/my.cnf:

   log-error = "/Users/<myname>/tmp/mysql.err.log"
   log-warnings = 3

我希望获得更多详细信息，但是我看到的只是这样:

I am hoping to get more details, but all I see is something like this:

   [Warning] Aborted connection 444 to db: 'dbname' user: 'root' host: 'localhost' (Got an error reading communication packets)

我到达了一个点，我认为更好的日志记录可能会提供更多细节，或者在此之前我可以尝试其他方法.

I have reached a point where i think more detail better logging may help, or if there's something else i could try before this.

谢谢.

推荐答案

看来，您的MySQL连接在长时间不活动后就超时了，我敢打赌，如果您不断使用现有设置来查询数据库，就不会发生这种情况.MySQL和sql方面都有一些设置可以解决此问题:

looks like your MySQL connection is timing out after a long period of inactivity, I bet it won't happen if you're constantly querying your DB with existing settings. There are couple of settings on both MySQL and sql sides which should resolve this issue:

1. 检查您的SQLa引擎的 pool_recycle 值，尝试使用其他/较小的值，例如1800(秒).如果您要从文件中读取数据库设置，请将其设置为

1. check your SQLa engine's pool_recycle value, try different / smaller value, e.g. 1800 (secs). If you're reading DB settings from file, set it as

pool_recycle:1800

pool_recycle: 1800

否则在引擎初始化期间指定它，例如

otherwise specify it during engine init, e.g.

from sqlalchemy import create_engine
e = create_engine("mysql://user:pass@localhost/db", pool_recycle=1800)

1. 检查/修改您的 wait_timeout MySQL变量，请参见

1. check / modify your wait_timeout MySQL variable, see https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout which is the number of seconds the server waits for activity on a noninteractive connection before closing it. e.g.

显示诸如"wait_timeout"之类的全局变量；

show global variables like 'wait_timeout';

找到适合您的环境的组合.

find a combination that works for your environment.

这篇关于SQLAlchemy/MySQL查询期间失去与MySQL服务器的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！