How to check if "'MySQL server has gone away'"

Question

    cursor.execute(sql)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

I'm running an infinite loop, but every 30-60 minutes, I get the error above. I think I want to add a conditional at the start of each loop iteration to check if MySQL server connection is still connected and OK. If it has gone away, then I'd like to establish a new connection and cursor before continuing the next iteration.

How can I test if the server is here or not? And is this a proper solution?

Thank-you!

Answer 1

You can catch the OperationalError exception and reconnect.

from MySQLdb import OperationalError

try:
    my_sql_operation()
except OperationalError as e:
    #do what you want to do on the error
    reconnect()
    print e

If you explicitly want to check if the connection is gone before running queries, you can run a test query to check if the exception occurs.

I am not sure in what other cases OperationalError is raised. But if you want to catch only the MySQL server has gone away error, you can do something like this.

from MySQLdb import OperationalError

try:
    my_sql_operation()
except OperationalError as e:
    if 'MySQL server has gone away' in str(e):
        #do what you want to do on the error
        reconnect()
        print e
    else:
        raise e()

it will catch only the 'gone away' error and let OperationalError exceptions raised due to other reasons be raised.

UPDATE

As I said in the comment my querying function would be like this:

def fetch_data(query):
    try:
        cursor = conn.Cursor()
        cursor.execute(query)
        return cursor.fetchall()
    except OperationalError as e:
        reconnect()
        print 'reconnecting and trying again...'
        fetch_data(query)

Thats just an example and may not be suitable for in your case. The point i am trying to make is its better to have different functions for different things so you can handle these situations easily.