python - 优雅处理 "MySQL has gone away"

Question

我正在用 Python 编写一个小型数据库适配器，主要是为了好玩。我试图让代码从 MySQL 连接“消失”的情况中正常恢复，也就是 wait_timeout 被超过。我已将 wait_timeout 设置为 10，所以我可以试试这个。

这是我的代码:

def select(self, query, params=[]):
        try:
            self.cursor = self.cxn.cursor()
            self.cursor.execute(query, params)
        except MySQLdb.OperationalError, e:
            if e[0] == 2006:
                print "We caught the exception properly!"
                print self.cxn
                self.cxn.close()
                self.cxn = self.db._get_cxn()
                self.cursor = self.cxn.cursor()
                self.cursor.execute(query, params)
                print self.cxn

        return self.cursor.fetchall()

接下来，我等待十秒钟并尝试发出请求。这是 CherryPy 的样子:

[31/Dec/2009:20:47:29] ENGINE Bus STARTING
[31/Dec/2009:20:47:29] ENGINE Starting database pool...
[31/Dec/2009:20:47:29] ENGINE POOL Connecting to MySQL...
[31/Dec/2009:20:47:29] ENGINE POOL Connecting to MySQL...
[31/Dec/2009:20:47:29] ENGINE POOL Connecting to MySQL...
[31/Dec/2009:20:47:29] ENGINE POOL Connecting to MySQL...
[31/Dec/2009:20:47:29] ENGINE POOL Connecting to MySQL...
[31/Dec/2009:20:47:29] ENGINE Started monitor thread '_TimeoutMonitor'.
[31/Dec/2009:20:47:29] ENGINE Started monitor thread 'Autoreloader'.
[31/Dec/2009:20:47:30] ENGINE Serving on 0.0.0.0:8888
[31/Dec/2009:20:47:30] ENGINE Bus STARTED
We caught the exception properly!   <====================================== Aaarg!
<_mysql.connection open to 'localhost' at 1ee22b0>
[31/Dec/2009:20:48:25] HTTP Traceback (most recent call last):
  File "/usr/local/lib/python2.6/dist-packages/CherryPy-3.1.2-py2.6.egg/cherrypy/_cprequest.py", line 606, in respond
cherrypy.response.body = self.handler()
  File "/usr/local/lib/python2.6/dist-packages/CherryPy-3.1.2-py2.6.egg/cherrypy/_cpdispatch.py", line 25, in __call__
    return self.callable(*self.args, **self.kwargs)
  File "adp.py", line 69, in reports
    page.sources = sql.GetSources()
  File "/home/swoods/dev/adp/sql.py", line 45, in __call__
    return getattr(self.formatter.cxn, parsefn)(sql, sql_vars)
  File "/home/swoods/dev/adp/database.py", line 96, in select
    self.cursor.execute(query, params)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (2006, 'MySQL server has gone away')

[31/Dec/2009:20:48:25] HTTP 
Request Headers:
  COOKIE: session_id=e14f63acc306b26f14d966e606612642af2dd423
  HOST: localhost:8888
  CACHE-CONTROL: max-age=0
  ACCEPT: application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5
  ACCEPT-CHARSET: ISO-8859-1,utf-8;q=0.7,*;q=0.3
  USER-AGENT: Mozilla/5.0 (X11; U; Linux x86_64; en-US) AppleWebKit/532.5 (KHTML, like     Gecko) Chrome/4.0.249.43 Safari/532.5
  CONNECTION: keep-alive
  Remote-Addr: 127.0.0.1
  ACCEPT-LANGUAGE: en-US,en;q=0.8
  ACCEPT-ENCODING: gzip,deflate
127.0.0.1 - - [31/Dec/2009:20:48:25] "GET /reports/1 HTTP/1.1" 500 1770 "" "Mozilla/5.0 (X11; U; Linux x86_64; en-US) AppleWebKit/532.5 (KHTML, like Gecko) Chrome/4.0.249.43 Safari/532.5"

为什么这不起作用？我清楚地捕捉到了异常，重新生成了连接和光标，但它仍然不起作用。是否与MySQLdb如何获取连接有关？

Answer 1

无法从代码中看到，但我的猜测是 db._get_cxn() 方法正在执行某种连接池并返回现有的连接对象，而不是创建一个新的连接对象.您不能在 db 上调用来刷新现有的无用连接吗？ (你真的应该调用一个以 _ 为前缀的内部方法吗？)

为了防止MySQL has gone away我通常更喜欢在我最后一次使用它的连接上保留一个时间戳。然后在再次尝试使用它之前，我查看时间戳并关闭/丢弃连接，如果它最后一次使用是在几个小时前。这样可以节省用 try...except OperationalError...try again 包装每个可能的查询。