SQLAlchemy/MySQL 在查询期间丢失了与MySQL服务器的连接

Question

SQLAlchemy (0.9.8) 和 mysql-5.6.21-osx10.8-x86_64 以及 MAC OS X 10.3.3（Yosemite）。

我一直遇到间歇性问题：

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

我已经阅读了一些帖子，大多数情况都可以通过将此添加到my.cnf文件中来解决问题。

   max_allowed_packet = 1024M

这个容量应该足够我的需求。在这之后，我会间断地进行测试。然后将此行代码放入 /etc/my.cnf 文件中：

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

我希望能获得更多细节，但我看到的只是这样的东西：

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

我已经到达这样一个点，认为更详细的日志记录可能会有所帮助，或者在此之前还有其他可尝试的方法。

谢谢。

- kawingkelvin

Answer 1

看起来你的 MySQL 连接在长时间不活动后会超时，如果你保持与现有设置的 DB 不断查询，我敢打赌这种情况不会发生。在 MySQL 和 SQL 两侧都有一些设置，应该可以解决这个问题：

1. 检查你的 SQL 引擎的 pool_recycle 值，尝试不同 / 较小的值，例如 1800（秒）。如果你从文件中读取 DB 设置，请将其设置为

   pool_recycle: 1800

否则，在引擎初始化时指定它，例如：

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

2. 请检查/修改您的MySQL变量wait_timeout，具体信息请参见https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout该变量表示非交互式连接在关闭之前等待活动的秒数，例如：

   show global variables like 'wait_timeout';

找到适合您环境的组合。

- MOCKBA

---

我不认为我的情况是超时引起的。在连续请求数据库时，我遇到了这个错误，因此我的代码不断地查询数据库。我怀疑这是由于“缓冲区”相关的问题，因为我的“累积”的结果集字节大小可能相当大，尽管单个请求的结果集可能并不是很大。 - kawingkelvin

我也遇到了同样的问题，我正在每小时的第10分钟运行一个cronjob。为此，我建立了连接，但每隔10个小时它就会显示2013连接丢失错误。有什么建议吗？ - venkatesh

这个解决方案对我从来没有起过作用。我设置了 pool_recycle 和 pool_pre_ping，但它从未解决过问题。 - nicbou

有没有想法如何重现这个问题？如果我们有一个可重现的场景，测试解决方案会更容易。 - ItayB

Answer 2

有两个参数可以帮助，pool_recycle和pool_pre_ping。 pool_recycle决定连接在空闲后回收的秒数。默认值是mysql的8小时，而sqlalchemy的默认值是-1，表示不回收。这就是区别所在，如果mysql已经回收了连接而sqlalchemy没有，会引发Lost connection exception异常。 pool_pre_ping将测试连接的活性。据我理解，这可以用作备份策略，如果连接由mysql回收但未被sqlalchemy识别，则sqlalchemy将进行检查，并避免使用无效连接。

create_engine(<mysql conn url>, pool_recycle=60 * 5, pool_pre_ping=True)

- buxizhizhoum

Answer 3

之前的解决方案都没有起作用。我设法解决了问题并提出了一个理论。我认为自己在MySQL架构方面是个门外汉，所以如果您更好地理解，请补充我的建议。

在我的情况下，我遇到了这个错误，但是有问题的查询并不是问题所在。问题也不是它之前的查询。问题在于我将一些先前查询的结果保存在实例中，我相信这维护了与数据库的连接。经过一系列处理后，几分钟后我只执行了另一个查询。

我拥有的这个连接最终无预警地死亡，当尝试执行新的查询时，mysql抛出了这个错误。由于某种原因，增加连接时间并没有帮助。我注意到随着时间的推移进行空提交可以解决问题。

db.session.commit()

- Mithsew

---

在我的情况下，我可以很容易地通过运行带有sqlalchemy的tornado应用程序，然后在某个时间点手动重启mysqld来重现该问题 - 然后我会看到错误，刷新页面后就可以解决了（因为它被一个新的 session/engine 替换掉了，不知道是怎么做到的）。tornado-sqlalchemy（或者在其他情况下是Flask-sqlalchemy）如何一直保持会话而不进行回收，以及回收参数对我无效（被忽略），这也是一个问题。 - Ricky Levi

Answer 4

基于this和this以及互联网上的许多其他文章的建议，使用以下装饰器包装所有函数帮助我解决了 mariadb 作为后端数据库的“连接丢失”问题。请注意，下面的db是flask_sqlalchemy.SQLAlchemy的实例，但对于 sqlalchemy 会话，概念仍将保持不变。

def manage_session(f):
    def inner(*args, **kwargs):

        # MANUAL PRE PING
        try:
            db.session.execute("SELECT 1;")
            db.session.commit()
        except:
            db.session.rollback()
        finally:
            db.session.close()

        # SESSION COMMIT, ROLLBACK, CLOSE
        try:
            res = f(*args, **kwargs)
            db.session.commit()
            return res
        except Exception as e:
            db.session.rollback()
            raise e
            # OR return traceback.format_exc()
        finally:
            db.session.close()
    return inner

我还在Flask SQLAlchemy配置中添加了50秒的pool_recycle，但这似乎对解决方案没有明显的贡献。

编辑1：

以下是最终代码中如何使用它的示例片段：

from flask_restful import Resource

class DataAPI(Resource):

    @manage_session
    def get(self):
        # Get data rows from DB

- Srmsbrmnm

---

你怎么称呼它？ - RyanH

@RyanH - 在答案中添加了一个示例作为编辑。 - Srmsbrmnm

天哪，为什么这是必要的... - trpt4him