pymysql.err.InterfaceError: (0, '') error when doing a lot of pushes to sql table

Question

I am doing a lot of inserts to a mysql table in a short period of time from python code (using pymysql) that uses a lot of different threads.

Each thread, of which there are many, may or may not end up pushing data to a MySql table.

Here is the code block that causes the issue (this can be called for each thread running):

        sql = ("INSERT INTO LOCATIONS (location_id, place_name) VALUES (%s, %s)")
        cursor = self.connection.cursor()          
        cursor.execute(sql, (location_id, place_name))     
        cursor.close()

and it is specifically this line:

cursor.execute(sql, (location_id, place_name))

That causes this error:

pymysql.err.InterfaceError: (0, '')

Note also that i define self.connection in the init of the class the above block is in. so all threads share a self.connection object but get their own cursor object.

The error seems to happen randomly and only starts appearing (I think) after doing quite a few inserts into the mysql table. It is NOT consistent meaning it does not happen with every single attempt to insert into mysql.

I have googled this specific error and it seems like it could be from the cursor being closed before running the query. but i believe it is obvious i am closing the cursor after the query is executed.

Right now I think this is happening either because of:

1. Some sort of write limit to the MySql table, although the error of pymysql.err.InterfaceError doesn't seem to say this specifically
2. The fact that I have a connection defined at a high scope that is having cursors created from in threads could somehow be causing this problem.

Thoughts?

Answer 1

seems like the issue was related to me having a universal connection object. creating one per thread seems to have removed this issue.

Answer 2

I get the same problem. There is a global connection in my project code, and I find that this connection will be timed out if there is no mysql operation for a long time. This error will occur when execute sql tasks, because of the timed-out connection.

My solution is: reconnecting mysql before execute sql tasks.

    sql = ("INSERT INTO LOCATIONS (location_id, place_name) VALUES (%s, %s)")
    self.connnection.ping()  # reconnecting mysql
    with self.connection.cursor() as cursor:         
        cursor.execute(sql, (location_id, place_name))