1

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!

Juan Carlos Sánchez García
  • 28
  • 5
  • I don't think this error has anything to do with how you close the connection; the error occurs long before that happens. See [Lost connection to MySQL server during query](https://stackoverflow.com/questions/1884859/lost-connection-to-mysql-server-during-query) for some other possibilities. You did not post your actual query or connection parameters or anything else that might shed light on what could be causing the problem, so it's difficult to be more definite. – Booboo Feb 09 '21 at 12:03
  • @Booboo hi thanks for your reply. I can not post neither the query or the parameters due to the private policy. In any case the script is functional, and does what it is supposed to do (insert registers to a database). The problem is that after a long time of execution I get that error. When I re-run again the script from the point of error, It works, until another long time of execution, when the same error appears again. I've added some loggin functionalities to know exactly how long it is working. – Juan Carlos Sánchez García Feb 09 '21 at 13:13
  • But you are getting new connections each time and not using pooling, correct? – Booboo Feb 09 '21 at 13:15
  • @Booboo exactly – Juan Carlos Sánchez García Feb 09 '21 at 13:16
  • I am no expert, but I would think that if there were a problem with closing the connection the symptom would be that you might eventually run out of getting connections and the error would occur on `connect`, not during a query after you had already successfully done a connect. But why are you not reusing the same connection for all your queries? – Booboo Feb 09 '21 at 13:21
  • I have already used the same connection for each query, but as the script takes a lot of hours running, I get time-out exceptions so I decided to generate new ones at each iteration. – Juan Carlos Sánchez García Feb 09 '21 at 13:29

2 Answers2

0

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.

Mark Mamore
  • 11
  • 1
0

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

Qiulang
  • 10,295
  • 11
  • 80
  • 129