查询期间失去与MySQL服务器的连接 [英] Lost connection to MySQL server during query

问题描述

我有一个巨大的表，我需要处理其中的所有行.我总是收到此丢失的连接"消息，并且无法重新连接并将光标恢复到原来的位置.这基本上是我在这里的代码:

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

但是我总是得到这个:

#
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
#

你有什么主意吗?

推荐答案

mysql文档有专门针对此错误的整个页面: http://dev.mysql.com/doc/refman/5.0 /en/gone-away.html

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

笔记是

• 如果向服务器发送不正确或太大的查询，也会出现这些错误.如果mysqld收到的数据包太大或顺序混乱，则认为客户端出了点问题，并关闭了连接.如果需要大型查询(例如，如果使用大型BLOB列)，则可以通过设置服务器的max_allowed_pa​​cket变量来增加查询限制，该变量的默认值为1MB.您可能还需要增加客户端上的最大数据包大小.有关设置数据包大小的更多信息，请参见第B.5.2.10节数据包太大".

• 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".

通过使用--log-warnings = 2选项启动mysqld，可以获得有关丢失的连接的更多信息.这会将一些断开连接的错误记录在hostname.err文件中

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