How to enable MySQL client auto re-connect with MySQLdb?

Question

I came across PHP way of doing the trick:

my_bool reconnect = 1;
mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);

but no luck with MySQLdb (python-mysql).

Can anybody please give a clue? Thanks.

Deva

Answer 1

I solved this problem by creating a function that wraps the cursor.execute() method since that's what was throwing the MySQLdb.OperationalError exception. The other example above implies that it is the conn.cursor() method that throws this exception.

import MySQLdb

class DB:
  conn = None

  def connect(self):
    self.conn = MySQLdb.connect()

  def query(self, sql):
    try:
      cursor = self.conn.cursor()
      cursor.execute(sql)
    except (AttributeError, MySQLdb.OperationalError):
      self.connect()
      cursor = self.conn.cursor()
      cursor.execute(sql)
    return cursor

db = DB()
sql = "SELECT * FROM foo"
cur = db.query(sql)
# wait a long time for the Mysql connection to timeout
cur = db.query(sql)
# still works

Frowst

@garret-heaton - why reconnect on AttributeError? Is that also thrown when the connection becomes stale? – Thyme

For quite some time I tried to figure out occasional mysql errors in my wrapper. Initially I thought conn.open will do the trick nope... but this worked. – Waldgrave

If you re-connect, you should be aware that you have lost any @variables and SETtings. Also, a half-finished transaction will have been ROLLBACK'd. This can lead to some messes. – Harlene

@aaa90210: The AttributeError is to catch the case where self.conn is still 'None' (the db hasn't been connected to yet) and the query method tries 'cursor = self.conn.cursor()'. – Ossification

@RickJames I understand what you're saying, but don't most timeouts have to do with the connection being idle for a long time, meaning the problem you mention would be rare? – Capitalize

@Capitalize - "Idle" is relative. If you get a project started, then take a coffee break, or compute something that takes a long time, and then come back, you may find that the connection is closed -- because from MySQL's point of view it was idle. – Harlene

I'd just like to point out that this will also catch other errors: OperationalError: (1054, "Unknown column 'countryy' in 'field list'"). Maybe this was different back in 2009, but just saying. – Prowler

Answer 2

I had problems with the proposed solution because it didn't catch the exception. I am not sure why.

I have solved the problem with the ping(True) statement which I think is neater:

import MySQLdb
con=MySQLdb.Connect()
con.ping(True)
cur=con.cursor()

Got it from here: http://www.neotitans.com/resources/python/mysql-python-connection-error-2006.html

Gloriole

I was spawning my script into 16-20 subprocesses, and i was having problems with mysql gone away. The con.ping(True) fixed my issue.Thanks. – Wherewith

Pinging before running a query is considered an anti-pattern that wastes resources and is unreliable: percona.com/blog/2010/05/05/… – Electrical

Answer 3

If you are using ubuntu Linux there was a patch added to the python-mysql package that added the ability to set that same MYSQL_OPT_RECONNECT option (see here). I have not tried it though.

Unfortunately, the patch was later removed due to a conflict with autoconnect and transations (described here).

The comments from that page say: 1.2.2-7 Published in intrepid-release on 2008-06-19

python-mysqldb (1.2.2-7) unstable; urgency=low

[ Sandro Tosi ] * debian/control - list items lines in description starts with 2 space, to avoid reformat on webpages (Closes: #480341)

[ Bernd Zeimetz ] * debian/patches/02_reconnect.dpatch: - Dropping patch: Comment in Storm which explains the problem:

    # Here is another sad story about bad transactional behavior. MySQL
    # offers a feature to automatically reconnect dropped connections.
    # What sounds like a dream, is actually a nightmare for anyone who
    # is dealing with transactions. When a reconnection happens, the
    # currently running transaction is transparently rolled back, and
    # everything that was being done is lost, without notice. Not only
    # that, but the connection may be put back in AUTOCOMMIT mode, even
    # when that's not the default MySQLdb behavior. The MySQL developers
    # quickly understood that this is a terrible idea, and removed the
    # behavior in MySQL 5.0.3. Unfortunately, Debian and Ubuntu still
    # have a patch right now which *reenables* that behavior by default
    # even past version 5.0.3.

Plunger

I guess whatever the client does, a lost connection may indicate loss of a transaction (server power cycled) or loss of connectivity. So I agree that simple re-connecting may hide loss of data against expectation of mysql client user. – Fonteyn

Answer 4

I needed a solution that works similarly to Garret's, but for cursor.execute(), as I want to let MySQLdb handle all escaping duties for me. The wrapper module ended up looking like this (usage below):

#!/usr/bin/env python

import MySQLdb

class DisconnectSafeCursor(object):
    db = None
    cursor = None

    def __init__(self, db, cursor):
        self.db = db
        self.cursor = cursor

    def close(self):
        self.cursor.close()

    def execute(self, *args, **kwargs):
        try:
            return self.cursor.execute(*args, **kwargs)
        except MySQLdb.OperationalError:
            self.db.reconnect()
            self.cursor = self.db.cursor()
            return self.cursor.execute(*args, **kwargs)

    def fetchone(self):
        return self.cursor.fetchone()

    def fetchall(self):
        return self.cursor.fetchall()

class DisconnectSafeConnection(object):
    connect_args = None
    connect_kwargs = None
    conn = None

    def __init__(self, *args, **kwargs):
        self.connect_args = args
        self.connect_kwargs = kwargs
        self.reconnect()

    def reconnect(self):
        self.conn = MySQLdb.connect(*self.connect_args, **self.connect_kwargs)

    def cursor(self, *args, **kwargs):
        cur = self.conn.cursor(*args, **kwargs)
        return DisconnectSafeCursor(self, cur)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

disconnectSafeConnect = DisconnectSafeConnection

Using it is trivial, only the initial connect differs. Extend the classes with wrapper methods as per your MySQLdb needs.

import mydb

db = mydb.disconnectSafeConnect()
# ... use as a regular MySQLdb.connections.Connection object

cursor = db.cursor()

# no more "2006: MySQL server has gone away" exceptions now
cursor.execute("SELECT * FROM foo WHERE bar=%s", ("baz",))

Archidiaconal

This should be the first answer. Much more powerful than the rest. – Urian

Really nice! Thanks! – Sarmentose

Answer 5

you can separate the commit and the close for the connection...that's not cute but it does it.

class SqlManager(object):
 """
 Class that handle the database operation
 """
 def __init__(self,server, database, username, pswd):

      self.server = server
      self.dataBase = database
      self.userID = username
      self.password = pswd

def Close_Transation(self):
      """
      Commit the SQL Query
      """
      try:
        self.conn.commit()
      except Sql.Error, e:
        print "-- reading SQL Error %d: %s" % (e.args[0], e.args[1])

 def Close_db(self):
    try:
        self.conn.close()
    except Sql.Error, e:
        print "-- reading SQL Error %d: %s" % (e.args[0], e.args[1])

 def __del__(self):
    print "close connection with database.."
    self.conn.close() 

Quechuan

Answer 6

I had a similar problem with MySQL and Python, and the solution that worked for me was to upgrade MySQL to 5.0.27 (on Fedora Core 6; your system may work fine with a different version).

I tried a lot of other things, including patching the Python libraries, but upgrading the database was a lot easier and (I think) a better decision.

Willful

Answer 7

In addition to Liviu Chircu solution ... add the following method to DisconnectSafeCursor:

def __getattr__(self, name):
    return getattr(self.cursor, name)

and the original cursor properties like "lastrowid" will keep working.

Wroth

Answer 8

My approach is based on Liviu Chircu solution.

I have added control over too many retries. The original answer from Liviu can easily get to a "Too many connections" OperationalError if a query doesn't match the fields in the table's structure.

MySQLdb.OperationalError: (1054, "Unknown column 'xxxxxxx' in 'field list'")

This error is not a connection error. In my version, I have removed also the class variables as I believe they are not necessary.

import MySQLdb


class MySQLdbReconnectableCursor(object):
    def __init__(self, db, cursor):
        self.db = db
        self.cursor = cursor

    def __getattr__(self, name):
        return getattr(self.cursor, name)

    def __iter__(self, *args, **kwargs):
        return self.cursor.__iter__(*args, **kwargs)

    def __next__(self, *args, **kwargs):
        return self.cursor.__next__(*args, **kwargs)

    def close(self):
        self.cursor.close()

    def execute(self, *args, **kwargs):
        try:
            result = self.cursor.execute(*args, **kwargs)
            self.db.refresh_retries()
            return result
        except MySQLdb.OperationalError:
            self.db.reconnect(reraise=True)
            self.cursor = self.db.cursor()
            return self.cursor.execute(*args, **kwargs)

    def fetchone(self):
        return self.cursor.fetchone()

    def fetchall(self):
        return self.cursor.fetchall()


class MySQLdbReconnectable(object):
    def __init__(self, *args, **kwargs):
        self.conn = None
        self.retries = kwargs.get("max_retries", 3)
        self.connect_args = args
        self.connect_kwargs = kwargs
        self.refresh_retries()
        self.reconnect()

    def refresh_retries(self):
        self.__retries = self.retries

    def reconnect(self, reraise=False):
        if self.__retries:
            self.__retries -= 1
            self.conn = MySQLdb.connect(
                *self.connect_args, **self.connect_kwargs
            )
        else:
            if reraise:
                raise
            else:
                raise IOError("Can not retry anymore!")

    def cursor(self, *args, **kwargs):
        cur = self.conn.cursor(*args, **kwargs)
        return MySQLdbReconnectableCursor(self, cur)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

Racon

Answer 9

You other bet it to work around dropped connections yourself with code.

One way to do it would be the following:

import MySQLdb

class DB:
    conn = None

    def connect(self):
        self.conn = MySQLdb.connect()

    def cursor(self):
        try:
            return self.conn.cursor()
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            return self.conn.cursor()

db = DB()
cur = db.cursor()
# wait a long time for the Mysql connection to timeout
cur = db.cursor()
# still works

Plunger

Actually this one doesn't work. Creating a cursor doesn't make a connection with the DB and therefore doesn't throw the OperationError exception. – Somerville