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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

Hmm, transactions are only an issue if you use InnoDB and transactions! The python-mysqldb people have made a political, not technical decision to remove this option. Screws people over who actually use this feature.

Answer 5

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