查询期间与 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屋！