优雅地处理“MySQL已经消失”
问题描述:
我正在Python中编写一个小型的数据库适配器,主要是为了好玩。我试图让代码从MySQL连接“消失”的情况中正常恢复,也就是wait_timeout
被超出。我已经设置wait_timeout
在10
,所以我可以试试这个。优雅地处理“MySQL已经消失”
这里是我的代码:
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()
接下来,我等待10秒钟,并尝试提出请求。以下是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如何获得连接有关?
答
从代码中看不到,但我的猜测是db._get_cxn()
方法正在做某种连接池并返回现有连接对象而不是创建新连接对象。是否没有打电话给db
来冲洗现有的无用连接? (你应该真的打电话给一个内部的_
- 前缀的方法?)
为了防止MySQL has gone away
我通常更喜欢保留一个时间戳与我最后一次使用它的连接。然后再尝试再次使用它,我看看时间戳,并关闭/放弃连接,如果它是最近几个小时以前使用的。这节省了用try...except OperationalError...try again
包装每一个可能的查询。
正确的你是在这两个帐户。这件事已经走向了许多不同的方向。我实际上实施了第2段的建议,这是我喜欢的做事方式。我已经重写了代码来修复你在第一段中提出的错误(错误就是这样的......我是我自己重构的受害者)。 非常感谢您的帮助。新年快乐! – 2010-01-01 05:33:27
@SeanWoods - 你能分享一下你正确的代码吗?我遭受同样的问题,你做... – Jonathan 2011-10-20 09:56:29
@SeanWoods你有没有想过分享代码的任何想法?看起来我也遭受同样的错误。我已经在我的初始数据库连接周围放了一个while循环来进行3次尝试,但正如bobince所提到的,除了每个查询之外,我都试过了。我确实尝试除了每一个查询,但我没有真正捕捉到服务器发生异常。 – 2013-03-23 16:25:32