查询期间丢失与 MySQL 服务器的连接

本教程将介绍查询期间丢失与 MySQL 服务器的连接的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

查询期间丢失与 MySQL 服务器的连接 教程 第1张

问题描述

I have a huge table and I need to process all rows in it. I'm always getting this Lost connection message and I'm not able to reconnect and restore the cursor to the last position it was. This is basically the code I have here:

#
import MySQLdb

class DB:
  conn = None

  def connect(self):
 self.conn = MySQLdb.connect('hostname', 'user', '*****', 'some_table', cursorclass=MySQLdb.cursors.SSCursor)

  def query(self, sql):
 try:
  cursor = self.conn.cursor()
  cursor.execute(sql)
except (AttributeError, MySQLdb.OperationalError):
  self.connect()
  cursor = self.conn.cursor()
  cursor.execute(sql)
return cursor
#

#
db = DB()
sql = "SELECT bla FROM foo"
data = db.query(sql)

for row in data:
 do_something(row)
#

But I'm always getting this:

#
Traceback (most recent call last):
  File "teste.py", line 124, in <module>
run()
 File "teste.py", line 109, in run
for row in data:
 File "/usr/lib64/python2.5/site-packages/MySQLdb/cursors.py", line 417, in next
row = self.fetchone()
 File "/usr/lib64/python2.5/site-packages/MySQLdb/cursors.py", line 388, in fetchone
r = self._fetch_row(1)
 File "/usr/lib64/python2.5/site-packages/MySQLdb/cursors.py", line 285, in _fetch_row
return self._result.fetch_row(size, self._fetch_type)
_mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')
 Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query') in <bound method SSCursor.__del__ of <MySQLdb.cursors.SSCursor object at 0x7f7e3c8da410>> ignored
#

Do you have any idea?

解决方案

The mysql docs have a whole page dedicated to this error:
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

of note are

    You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section B.5.2.10, "Packet too large".

    You can get more information about the lost connections by starting mysqld with the --log-warnings=2 option. This logs some of the disconnected errors in the hostname.err file

好了关于查询期间丢失与 MySQL 服务器的连接的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。