CherryPy Ping Database Connection Or Restart Connection

Question

I use a CherryPy webserver (Python 2.7) with a local MySQL database. In short, after some days, I get the following error when performing a new SQL query:

MySQL server has gone away

Although I can find some sources to this problem, they are not related to CherryPy actually. Restarting CherryPy resolves the problem. I guess, CherryPy looses the connection and is not able to reconnect?.

Thus, my question is:

How can I check the database connection and if it is not alive, reconnect then? or
simply, how to avoid the error mentioned above?.

In the following, I show some simplified code version that I am using:

def dbconnect(thread_index):
    """
    Creates a database connection for each thread that CherryPy creates
    :param thread_index:
    """
    cherrypy.thread_data.db = MySQLdb.connect("localhost", "user",
                                          "password", "databasename")

class website(object):

    @cherrypy.expose
    def index(self):
        c = cherrypy.thread_data.db.cursor()
        c.execute("Select * from ATable")
        (output,) = c.fetchone()
        # generate some html output ...


cherrypy.engine.subscribe('start_thread', dbconnect)
website = website()
cherrypy.quickstart(website)

Now If I visit the index page after some days where nothing happened on the server, the index method throws the MySQL server has gone away error.

Hopefully, this question might be interesting for all who use CherryPy and get the SQL error one day. Thank you very much :-)

score 2 · Accepted Answer · edited May 23 '17 at 11:52

CherryPy keeps a pool of threads for servicing client requests. The start_thread is invoked when one of those threads is started, and creates the Database connection when the thread is created. After serving some requests and sitting idle, something is dropping the connection (maybe the mysql client, or maybe the server has disconnected (likely), or even an intermediate server on the network terminating an idle connection). It's only when the next request on that thread arrives that the error is raised.

One easy but possibly innefficient way to solve this is to open a new DB connection on each request - binding dbconnect at on_start_resource or similar.

Perhaps better would be to have both the start_thread set up the connection, but then have an on_start_resource to check the state of the connection and reconnect it if it's not connected.

Even better, and what I would probably do, is identify or create an object to manage disconnected connections, something like a MySQLReconnectDB which has the same interface as a normal mysql.Connection object, but will automatically reconnect when disconnected. This answer suggests a possible solution. Then, use an instance of that in your existing start_thread handler.

I realize that's not a complete answer, but I hope it provides a foundation to come up with a solution that works for you.

Ok, I tested the linked solution a while and now everything works so far. A quick and dirty solution is to catch the MySQLdb.OperationalError and to call the dbconnect method again so that the current thread restarts the database connection... — PeteChro, May 22 '16 at 20:52

CherryPy Ping Database Connection Or Restart Connection

1 Answers1