MySQL server has gone away when use "executemany" in python code

Question

I wrote this function which is run without any error.

    def insertRows(self, query,data):
        self.cursor.executemany(query,data)
        self.connection.commit()

But, I got the following error when I run my code on another computer for same data.

MySQL Error [2006]: MySQL server has gone away
Traceback (most recent call last):
  File "main.py", line 157, in <module>
    writePostingLists(lst)
  File "main.py", line 32, in writePostingLists
    mydb.insertKNNIndex(postingLists,tabelName)
  File "/home/pythonCode/Doc2Vec_Annoy/KNN/MySQLRepository.py", line 78, in insertKNNIndex
    self.insertRows(query,inputList)
  File "/home/pythonCode/Doc2Vec_Annoy/KNN/MySQLRepository.py", line 31, in insertRows
    self.connection.rollback()
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

I found out that the problem is the length of list that I give to executemany function.

The above code is executed without any problem when the length of data is less than 300.

How can I solve this problem? Is it related to setting of MySQl?

David542 · Accepted Answer · 2017-06-29 03:15:15Z

1

One option here would be to split up the list into rows of 100 or so. For example:

def insertRows(self, query,data, LIST_LENGTH=100):
    START_INDEX = 0
    while data[START_INDEX:START_INDEX+LIST_LENGTH]:
        self.cursor.executemany(query,data[START_INDEX:START_INDEX+LIST_LENGTH])
        START_INDEX += LIST_LENGTH
    self.connection.commit()

I suppose mysql (or mysql-python) probably has a maximum character length of the amount of data you can pass to it.

Regarding your error (from https://piwik.org/faq/troubleshooting/faq_183/):

If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. To fix, you can increase the maximal packet size limit max_allowed_packet in my.cnf file, eg. set max_allowed_packet = 128M, then restart your MySQL server: sudo /etc/init.d/mysql restart.

edited Jun 29, 2017 at 3:15

answered Jun 28, 2017 at 21:35

David542

112k211 gold badges581 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fatima Over a year ago

I am looking for the reason of get this error on a computer. It is not the solution that I am looking for.

Collectives™ on Stack Overflow

MySQL server has gone away when use "executemany" in python code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related