查询期间丢失与 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)
#

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