PyMySQL with Django, Multithreaded application

Question

We are trying to use PyMySQL (==0.7.11) in our Django (==1.11.4) environment. But we are encountered with problems when multiple actions are performed at the same time (Multiple requests sent to the same API function).

We get this error:

pymysql.err.InternalError: Packet sequence number wrong - got 6 expected 1

We are trying to delete records from the DB (some time massive requests come from multiple users).

Code:

def delete(self, delete_query):
    self.try_reconnect()
    return self._execute_query(delete_query)

def try_reconnect(self):
    if not self.is_connected:
        self.connection.ping(reconnect=True)

@property
def is_connected(self)
    try:
        self.connection.ping(reconnect=False)
        return True
    execpt:
        return False

def _execute_query(self, query):
    with self.connection.cursor() as cursor:
        cursor.execute(query)
        self.connection.commit()
        last_row_id = cursor.lastrowid
    return last_row_id

I didn't think it necessary to point out that those functions are part of DBHandler class, and self.connection initialized in def connect(self) function.

def connect(self):
    self.connection = pymysql.connect(...)

This connect function run once in Django startup, we create a global instance(varaible) of DBHandler for the whole project, and multiple files importing it.

We are using the delete function as our gateway to execute delete query.

What we are doing wrong ? And how can we fix it ?

Answer 1

Found the problem,

PyMySQL is not thread safty to share connections as we did (we shared the class instance between multiple files as a global instance - in the class there is only one connection), it is labled as 1:

threadsafety = 1

According to PEP 249:

1 - Threads may share the module, but not connections.

One of the comments in PyMySQL github issue:

you need one pysql.connect() for each process/thread. As far as I know that's the only way to fix it. PyMySQL is not thread safe, so the same connection can't be used across multiple threads.

Any way if you were thinking of using other python package called MySQLdb for your threading application, notice to MySQLdb message:

Don't share connections between threads. It's really not worth your effort or mine, and in the end, will probably hurt performance, since the MySQL server runs a separate thread for each connection. You can certainly do things like cache connections in a pool, and give those connections to one thread at a time. If you let two threads use a connection simultaneously, the MySQL client library will probably upchuck and die. You have been warned. For threaded applications, try using a connection pool. This can be done using the Pool module.

Eventually we managed to use Django ORM and we are writing only for our specific table, managed by using inspectdb.