2

I have a series of python scripts that are designed to be long running. When I look at the MySQL process monitor, I see that three of them will always have a time of 0, then 2 of them that I use to actually insert data into the database will have values that are always counting up, and never get reset to 0 meaning that eventually they encounter the dreaded 'MySQL has gone away'.

I am using MySQLdb.

Can someone please explain why this happens?

Is there a way to make sure that they reset this value to 0?

Thanks

Jonny Flowers
  • 631
  • 1
  • 9
  • 20

1 Answers1

1

It sounds like your idle timeout is expiring. This is set in the mysqld server configuration, though, and is not really an issue with MySQLdb. You need to adjust the wait-timeout system variable, either in the server configuration, or with an SQL statement when you connect.

farcepest
  • 145
  • 6
  • What could I use to create a persistent connection with MySQLdb? – Jonny Flowers Sep 24 '12 at 18:57
  • 4
    There is a `connection.ping()` method that can be used to test whether the connection is still open. It'll raise an exception if it's not open, and then you can manually reopen it. Or, you can use `connection.ping(True)` and this will reopen the connection silently. Note that you should use `ping()` before starting a transaction (executing any kind of query). – farcepest Sep 28 '12 at 18:50
  • Taking a cue from this answer http://stackoverflow.com/a/3104681/323404 is it a good idea to ping before request? – Primal Pappachan Jul 29 '13 at 17:27
  • 1
    Is it a good idea to ping before each query? No. It may be sane to ping before you start a transaction (i.e. the first query), if the issue is idle connections being closed. The connection is unlikely to disappear after that, and even if it does, if you're in a transaction, it's effectively rolled back. In practice, I have never had to use ping for this purpose. – farcepest Aug 09 '13 at 19:40