Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am using Python and mySQL, and there is a long lag between queries. As a result, I get an 'MySQL connection has gone away' error, that is wait_timeout is exceeded.

This has been discussed e.g. in Gracefully handling "MySQL has gone away"

but this does not specifically answer my query.

So my approach to handling this - I have wrapped all my sql execute statements in a method as -

  def __execute_sql(self,sql,cursor):
        cursor.execute(sql)
    except MySQLdb.OperationalError, e:            
        if e[0] == 2006:
            self.logger.do_logging('info','DB', "%s : Restarting db" %(e))
            self.start_database()
I have several places in the code which calls this query. The thing is, I also have several cursors, so the method invocations look like-
self.__execute_sql(sql,self.cursor_a)
self.__execute_sql(sql,self.cursor_b)
and so on
I need a way to gracefully re-execute the query after the db has been started. I could wrap the calls in an if statement, and re-execute so it would be 
def __execute_sql(self,sql,cursor):
        cursor.execute(sql)
        return 1
except MySQLdb.OperationalError, e:            
    if e[0] == 2006:
        self.logger.do_logging('info','DB', "%s : Restarting db" %(e))
        self.start_database()
        return 0
and then
if (self.__execute_sql(sql,self.cursor_a) == 0):
   self.__execute_sql(sql,self.cursor_a)
But this is clunky. Is there a better way to do this?
Thanks!!!
I tried Crasched's approach, which got me to a new OperationalError:
OperationalError: (2013, 'Lost connection to MySQL server during query')
My final solution was to first try the ping, and if another OperationalError was raised, to reconnect and recreate the cursor with the new connection, like so:
    self.connection.ping(True)
except MySQLdb.OperationalError:
    self.connection = MySQLdb.connect(
        self.db_host,
        self.db_user,
        self.db_passwd,
        self.db_dbase,
        self.db_port)
    # reconnect your cursor as you did in __init__ or wherever    
    self.cursor = self.connection(
        MySQLdb.cursors.DictCursor)
Back in business!
Python 2.7, MySQL 5.5.41
                Pinging before running a query is considered an anti-pattern that wastes resources and is unreliable: percona.com/blog/2010/05/05/…
– Jeff Widman
                Aug 19, 2016 at 19:43
                @kiminoa - Try executing the query, and catching any MySQL errors, in which case you re-try the query.
– Ben Wheeler
                Aug 29, 2017 at 3:29
I had the same problem and wanted to wrap the exception to capture it but instead I solved it by using the following.
Before calling the execute, call

self.con.ping(TRUE)
http://www.neotitans.com/resources/python/mysql-python-connection-error-2006.html
http://mysql-python.sourceforge.net/MySQLdb.html
I can no longer find the original source material I found this out from, but this solved the problem immediately.
                Pinging before running a query is considered an anti-pattern that wastes resources and is unreliable: percona.com/blog/2010/05/05/…
– Jeff Widman
                Aug 19, 2016 at 19:43
I was running into mystifying "MySQL server has gone away" errors, and here is my solution.
This solution will let you retry through MySQL errors, handle pretty much any type of query, include query variables in the query str or in a separate tuple, and collect and return all of the success and error messages you encounter along the way:
def execute_query(query_str, values=None):
  # defaults
  num_affected_rows = 0
  result_rows = None
  success = False
  message = "Error executing query: {}".format(query_str)
  # run the query
    mysql_conn = get_existing_mysql_connection()
    cur = mysql_conn.cursor()
    if values == None or len(values) < 1:
      num_affected_rows = cur.execute(query_str)
    else:
      num_affected_rows = cur.execute(query_str, values)
    result_rows = cur.fetchall() # only relevant to select, but safe to run with others
    cur.close()
    mysql_conn.commit()
    success = True
    message = "Mysql success for query: {}".format(query_str)
  except BaseException as e:
    message = "Mysql error: {}; for query: {}".format(repr(e), query_str)
  return (success, num_affected_rows, result_rows, message)
def execute_query_with_retry(query_str, values=None, num_tries=3, message=""):
  # defaults
  success = False
  num_affected_rows = 0
  result_rows = None
  this_message = "Error executing query: {}".format(query_str)
  # should we still try?
  if num_tries < 1:
    this_message = "Ran out of tries for query: {}".format(query_str)
    return (False, 0, None, message + '; ' + this_message)
  num_tries_after_this = num_tries - 1
  # try to execute query
    (success, num_affected_rows, result_rows, this_message) = execute_query(query_str, values)
  except BaseException as e:
    success = False
  # handle success or failure
  if success == True:
    return (True, num_affected_rows, result_rows, message + '; ' + this_message)
  else:
    open_new_mysql_connection() # reconnect using password etc.
    return(execute_query_with_retry(query_str, values=values, num_tries=num_tries_after_this, message=(message + '; ' + this_message)))
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.