Python和Django OperationalError（2006年，“MySQL服务器已经消失”） [英] Python and Django OperationalError (2006, 'MySQL server has gone away')

问题描述

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

views.py

 code> DBNAME =test
 DBIP =localhost
 DBUSER =django
 DBPASS =password
 db = MySQLdb.connect（DBIP，DBUSER ，DBPASS，DBNAME）
 cursor = db.cursor（）
 
 def list（request）：
 statement =从列表中选择的项目= 1
 cursor.execute（statement）
 results = cursor.fetchall（）
  

以下，但仍然不起作用：

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（）
 ret urn self.conn.cursor（）
 
 db = DB（）
 cursor = db.cursor（）
 
 def list（request）：
 cursor = db.cursor（）
 statement =从列表中选择的项目= 1
 cursor.execute（语句）
 results = cursor.fetchall（）
  

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

编辑：我意识到我最近写了一块中间件来追踪功能，这是问题的原因。但是，我不知道为什么。这是中间件的代码

  def process_request_handler（sender，** kwargs）：
t =线程。线程（target = dispatch.execute，
 args = [kwargs ['nodes']，kwargs ['callback']]，
 kwargs = {}）
 t.setDaemon（True）
 t.start（）
 return 
 process_request.connect（process_request_handler）
  

解决方案

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

MySQL文档提供了一些其他可能的原因，这可能值得研究，以确定它们是否符合您的情况。

在每个函数调用 connect（）的替代方法是调查使用 ping（） 方法在连接对象上;这将使用尝试自动重新连接的选项来测试连接。我很难找到一些体面的文档在线的 ping（）方法，但是这个问题可能有帮助。注意，自动重新连接在处理事务时可能会很危险，因为重新连接会导致隐式回滚（并且看起来是自动重新连接不是该功能的主要原因） MySQLdb实现）。

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()

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?

Edit: 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)

解决方案

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.

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

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.

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服务器已经消失”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！