大约一天后，Python失去了与MySQL数据库的连接 [英] Python loses connection to MySQL database after about a day

问题描述

我正在使用Python，Flask，MySQL和uWSGI开发基于Web的应用程序.但是，我 不是 使用SQL Alchemy或任何其他ORM.我正在使用一个旧的PHP应用程序中的一个预先存在的数据库，该数据库无论如何都不能与ORM很好地配合，所以我只是使用 mysql-connector 并手动编写查询.

I am developing a web-based application using Python, Flask, MySQL, and uWSGI. However, I am not using SQL Alchemy or any other ORM. I am working with a preexisting database from an old PHP application that wouldn't play well with an ORM anyway, so I'm just using mysql-connector and writing queries by hand.

当我第一次启动该应用程序时，它可以正常工作，但是第二天早上回来时，我发现它已经坏了.我会收到类似 mysql.connector.errors.InterfaceError:2013的错误:查询期间与MySQL服务器的连接丢失或类似的 mysql.connector.errors.OperationalError:2055:与MySQL的连接丢失服务器位于"10.0.0.25:3306"，系统错误:32管道损坏.

The application works correctly when I first start it up, but when I come back the next morning I find that it has become broken. I'll get errors like mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query or the similar mysql.connector.errors.OperationalError: 2055: Lost connection to MySQL server at '10.0.0.25:3306', system error: 32 Broken pipe.

我一直在研究它，我想我知道问题出在哪里.我只是无法找到一个好的解决方案.尽我所能，问题是我一直在全局引用数据库连接，并且由于Flask应用程序始终在服务器上运行，因此该连接最终会失效并变得无效.

I've been researching it and I think I know what the problem is. I just haven't been able to find a good solution. As best as I can figure, the problem is the fact that I am keeping a global reference to the database connection, and since the Flask application is always running on the server, eventually that connection expires and becomes invalid.

我想为每个查询创建一个新的连接就足够简单了，但这似乎不是理想的解决方案.我想我也可以建立某种连接缓存机制，该机制将在一个小时左右后关闭旧连接，然后重新打开它.这是我能够提出的最佳选择，但我仍然觉得应该有一个更好的选择.

I imagine it would be simple enough to just create a new connection for every query, but that seems like a far from ideal solution. I suppose I could also build some sort of connection caching mechanism that would close the old connection after an hour or so and then reopen it. That's the best option I've been able to come up with, but I still feel like there ought to be a better one.

我环顾四周，大多数收到这些错误的人的表很大或损坏了，或者有某种程度的影响.这里情况不同.旧的PHP应用程序仍然可以正常运行，所有表的行数少于50,000，少于30列，而Python应用程序运行良好，直到运行了一天左右.

I've looked around, and most people that have been receiving these errors have huge or corrupted tables, or something to that effect. That is not the case here. The old PHP application still runs fine, the tables all have less than about 50,000 rows, and less than 30 columns, and the Python application runs fine until it has sat for about a day.

因此，这是希望有人能够为保持与MySQL数据库的持续开放连接提供良好的解决方案.或者，如果我希望有人知道的话，也许我正在完全吠错树.

So, here's to hoping someone has a good solution for keeping a continually open connection to a MySQL database. Or maybe I'm barking up the wrong tree entirely, if so hopefully someone knows.

推荐答案

我现在可以使用它了.使用共用连接似乎可以解决此问题.

I have it working now. Using pooled connections seemed to fix the issue for me.

mysql.connector.connect(
    host='10.0.0.25',
    user='xxxxxxx', 
    passwd='xxxxxxx', 
    database='xxxxxxx',
    pool_name='batman',
    pool_size = 3
)

def connection():
    """Get a connection and a cursor from the pool"""
    db = mysql.connector.connect(pool_name = 'batman')
    return (db, db.cursor())

我在每个查询函数之前调用 connection()，然后在返回之前关闭游标和连接.似乎可以工作.不过，仍然可以寻求更好的解决方案.

I call connection() before each query function and then close the cursor and connection before returning. Seems to work. Still open to a better solution though.

此后，我找到了一个更好的解决方案.(我仍然偶尔会遇到池化连接的问题).实际上，有一个用于Flask的专用库来处理mysql连接，这几乎是一个替代品.

I have since found a better solution. (I was still occasionally running into issues with the pooled connections). There is actually a dedicated library for Flask to handle mysql connections, which is almost a drop-in replacement.

从bash中: pip install Flask-MySQL

在Flask配置中添加 MYSQL_DATABASE_HOST ， MYSQL_DATABASE_USER ， MYSQL_DATABASE_PASSWORD ， MYSQL_DATABASE_DB .然后在包含Flask App对象的主Python文件中:

Add MYSQL_DATABASE_HOST, MYSQL_DATABASE_USER, MYSQL_DATABASE_PASSWORD, MYSQL_DATABASE_DB to your Flask config. Then in the main Python file containing your Flask App object:

from flaskext.mysql import MySQL
mysql = MySQL()
mysql.init_app(app)

并获得连接: mysql.get_db().cursor()

所有其他语法都一样，此后我再也没有任何问题.长期使用此解决方案.

All other syntax is the same, and I have not had any issues since. Been using this solution for a long time now.

这篇关于大约一天后，Python失去了与MySQL数据库的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！