Python Mysql-Connector. Which is better connection.close() or connection.disconnect() or connection.shutdown()

Question

I have a question and I hope that someone could help me.

To give you some context, imagine a loop like this:

while True:

 conn = mysql.connector.connect(**args) #args without specifying poolname
 conn.cursor().execute(something)
 conn.commit()
 conn.cursor.close()
 
 #at this point what is better:
 conn.close()
 #or
 conn.disconnect()
 #or
 conn.shutdown()

In my case, I'm using conn.close() but after a long time of execution, the script I always get an error:

mysql.connector.errors.OperationalError: 2013 (HY000): Lost connection to MySQL server during query

Aparently I'm exceeding the time-out of the mysql connection which is by default 8 hours. But looking at the loop, it's creating and closing new connections on each iteration. I'm pretty sure that the cursor execution takes no more than an hour.

So the question is: doesn't the close() method close the connection? Should I use disconnect() or shutdown() instead? What are the differences between using one or the other.

I hope I've explained myself well, best regards!

Answer 1

There might be a problem inside your code. Normally, close() will work everytime even if you are using loop. But still try to trial and error those three command and see what suits your code.

Answer 2

The doc say that clearly

close() is a synonym for disconnect().

For a connection obtained from a connection pool, close() does not actually close it but returns it to the pool and makes it available for subsequent connection requests

disconnect() tries to send a QUIT command and close the socket. It raises no exceptions. MySQLConnection.close() is a synonymous method name and more commonly used.

To shut down the connection without sending a QUIT command first, use shutdown().

For shutdown

Unlike disconnect(), shutdown() closes the client connection without attempting to send a QUIT command to the server first. Thus, it will not block if the connection is disrupted for some reason such as network failure.

But I do not figure out why you get Lost connection to MySQL server during query You may check this discussion Lost connection to MySQL server during query