"MySQL server has gone away"
I have written a few Twisted scripts at work that parse incoming data from a socket and save it in a MySQL database, using the MySQLdb package. It's a well-known fact that the MySQL server will close connections that are inactive for some time and yet I forgot to handle it in last script I wrote. Previously I solved the problem by remembering the last time I used the connection and forcing a reconnect based on this value or the
recycle option in SQLAlchemy's connection pool when I needed a connection pool (which does basically the same as the former). But when I found the problem in the latest script today, I thought I should finally solved it properly, so I started Googling...
I found out about the
mysql_ping() function, which seemed perfect for this, especially in combination with the
MYSQL_OPT_RECONNECT option. The MySQLdb User's Guide mentions a wrapper for
mysql_ping(), but nothing about the
MYSQL_OPT_RECONNECT option. It wouldn't be me if I didn't download the source code to check if there really isn't any way to set the option.
It turns out that the wrapper for
mysql_ping() accepts a boolean argument, to set the option locally. It's even nicely documented in the docstring for the method. Too bad I didn't look at the API documentation before reading the source code. :)
Anyway, I ended up with code like this and it seems to be working nicely:
if self.db is None: self.db = MySQLdb.connect(...) else: self.db.ping(True)