问题描述

原文:我最近开始从我的一些旧代码中获取 MySQL OperationalErrors，但似乎无法追溯到问题所在.由于它之前可以工作，我认为它可能是一个破坏某些东西的软件更新.我正在将 python 2.7 与 django runfcgi 与 nginx 一起使用.这是我的原始代码:

Original: I have recently started getting MySQL OperationalErrors from some of my old code and cannot seem to trace back the problem. Since it was working before, I thought it may have been a software update that broke something. I am using python 2.7 with django runfcgi with nginx. Here is my original code:

views.py

DBNAME = "test"
DBIP = "localhost"
DBUSER = "django"
DBPASS = "password"
db = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
cursor = db.cursor()

def list(request):
    statement = "SELECT item from table where selected = 1"
    cursor.execute(statement)
    results = cursor.fetchall()

我尝试了以下方法，但还是不行:

I have tried the following, but it still does not work:

views.py

class DB:
    conn = None
    DBNAME = "test"
    DBIP = "localhost"
    DBUSER = "django"
    DBPASS = "password"
def connect(self):
    self.conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
def cursor(self):
    try:
        return self.conn.cursor()
    except (AttributeError, MySQLdb.OperationalError):
        self.connect()
        return self.conn.cursor()

db = DB()
cursor = db.cursor()

def list(request):
    cursor = db.cursor()
    statement = "SELECT item from table where selected = 1"
    cursor.execute(statement)
    results = cursor.fetchall()

目前，我唯一的解决方法是在每个使用 mysql 的函数中执行 MySQLdb.connect().我还注意到，当使用 django 的 manage.py runserver 时，我不会遇到这个问题，而 nginx 会抛出这些错误.我怀疑我的连接超时，因为 list() 在启动服务器的几秒钟内被调用.我正在使用的软件是否有任何更新会导致此问题中断/是否有任何解决方案?

Currently, my only workaround is to do MySQLdb.connect() in each function that uses mysql. Also I noticed that when using django's manage.py runserver, I would not have this problem while nginx would throw these errors. I doubt that I am timing out with the connection because list() is being called within seconds of starting the server up. Were there any updates to the software I am using that would cause this to break/is there any fix for this?

我意识到我最近写了一个中间件来守护一个函数，这就是问题的原因.但是，我无法弄清楚原因.这是中间件的代码

I realized that I recently wrote a piece of middle-ware to daemonize a function and this was the cause of the problem. However, I cannot figure out why. Here is the code for the middle-ware

def process_request_handler(sender, **kwargs):
    t = threading.Thread(target=dispatch.execute,
        args=[kwargs['nodes'],kwargs['callback']],
        kwargs={})
    t.setDaemon(True)
    t.start()
    return
process_request.connect(process_request_handler)

推荐答案

根据 MySQL 文档，当客户端无法向服务器发送问题时会引发您的错误消息，很可能是因为服务器本身已关闭连接.在最常见的情况下，服务器将在(默认)8 小时后关闭空闲连接.这可以在服务器端进行配置.

As per the MySQL documentation, your error message is raised when the client can't send a question to the server, most likely because the server itself has closed the connection. In the most common case the server will close an idle connection after a (default) of 8 hours. This is configurable on the server side.

MySQL 文档 提供了许多其他可能值得研究的可能原因，看看它们是否适合您的情况.

The MySQL documentation gives a number of other possible causes which might be worth looking into to see if they fit your situation.

在每个函数中调用 connect() 的替代方法(最终可能会不必要地创建新连接)是在连接上使用 ping() 方法进行调查目的;这通过尝试自动重新连接的选项来测试连接.我努力为ping() 方法在线，但这个问题的答案可能帮助.

An alternative to calling connect() in every function (which might end up needlessly creating new connections) would be to investigate using the ping() method on the connection object; this tests the connection with the option of attempting an automatic reconnect. I struggled to find some decent documentation for the ping() method online, but the answer to this question might help.

请注意，处理事务时自动重新连接可能很危险，因为重新连接会导致隐式回滚(这似乎是自动重新连接不是 MySQLdb 实现的功能的主要原因).

Note, automatically reconnecting can be dangerous when handling transactions as it appears the reconnect causes an implicit rollback (and appears to be the main reason why autoreconnect is not a feature of the MySQLdb implementation).

这篇关于Python 和 Django OperationalError(2006，'MySQL 服务器已经消失')的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！