MySQLdb connection issue

Question

I am writing a server which provide a service by query MySQL database. Everything goes fine, except when run one or two days the connection will go away. The error code is (2600, MySQL has gone away)

I try to wrap the cursor object by:

def cursor(self):    
    try:
        return self.connection.cursor()
    except:
        self.connection = MySQLdb.connect(**self.kwargs)
        return self.connection.cursor()

This seems not work, I still got error while I the cursor from this wrapper class when I call cursor.execute(..).

Any idea that I can keep consistent connection?

Answer 1

You would be better served by a connection pool and minimizing the length of time that a connection stays open. It'll be more scalable, and you won't suffer from this issue.

Answer 2

@duffymo's idea about not keeping connections open too long is viable, but so is your original idea about catching the exception and retrying (I'm not so sure about which one is better).

Since the exception is raised by cursor.execute, that's where you must catch and remedy it (by renewing the connection and cursor and repeating the attempt to execute). I would do it via a simple class wrapper, holding the connection (and parameters needed to establish or re-establish it) and cursor as members, and exposing methods for executing and fetching.

Note that by encapsulating connection and cursor that way, you could then keep exactly the same interface (with a completely different implementation that gets the connection from a pool and returns it there when done with it) if you do decide to switch to @duffymo's strategy instead (an explicit method "done with fetching from this execute" will help;-).