1

I use pymysql to connect mysql and I set a read_timeout when connecting:

conn = pymysql.connect(host=host,port=port,user=user,passwd=passwd,read_timeout=60)

and my query code is here:

try:
    cur.execute(sql)
    rows = cur.fetchall()
except:
    # timeout
    logging.error()
finally:
    cur.close()
    conn.close()

When I execute a long time query, the code raise a timeout error and finally cursor and connection are both closed(I debug conn._closed to confirm the connection is closed).But when I login to mysql and show processlist, I found the process is still alive.

My question is, is this normal and How can I kill the process when query timeout in my code? I figure out a workaround that execute set session max_execution_time=60 before query, but I think it's not the best practice.

Here is show processlist output, I use sleep to represent long time query, I think the result is the same, connection is closed in my code, but process is still there.

show processlist output

zhenwu liu
  • 21
  • 1
  • 4

2 Answers2

2

read_timeout on PyMySQL connection objects is used as timeout value on the underlying socket during read operations. If the timeout is reached, the socket raises a subclass of OSError (socket.timeout), which is caught in the connection's _read_bytes method.
During the handling of that exception, _force_close is executed, which ultimately sets the self._sock attribute of the connection to None.

When your code reaches the finally block, all that is left to do during the execution of conn.close() is setting _closed to True.
If _sock was not None at this point, the close method would also attempt to send COM_QUIT to the server via the socket, but since the client-side socket is already closed, that does not happen.

From the server's point of view, the client wordlessly dropped the connection. Unfortunately, it's hard to find meaningful documentation on what exactly happens in that case.

Normally, on connection timeout, the pending transaction should be rolled back. However, I'm unsure if the client closing its socket would result in an immediate server-side timeout, as suggested in this Q&A.
Also, using MySQL's sleep function for testing might be a bad idea. It could completely suspend the thread handling your connection and query, rendering it unable to detect the lost connection in time.

You might want to take a look at MySQL's kill command, that is also implemented in the PyMySQL connection, although undocumented. However, this will need an external timeout in your script when doing cursor.execute with statements that might take too long to complete.

shmee
  • 4,721
  • 2
  • 18
  • 27
0

You need to commit the transaction before closing the connection. The process must be alive since the transaction has not been committed to the database. Add conn.autocommit=true in the beginning or during conn initialization,

conn = pymysql.connect(host=host,port=port,user=user,passwd=passwd,read_timeout=60, autocommit=True)

Or, you can use conn.commit() before closing the connection.

Pragya
  • 101
  • 4