#164 ping() does not do reconnect when server has gone away

MySQLdb (285)

MySQL 5.0.16
MySQLdb 1.2
python 2.4.2
gentoo linux amd64

According to the docstring, ping() is supposed to
attempt a reconnect. Instead, if the current
connection is down, it only raises an exception.

I think the problem might be that check_connection is
done before the ping, and if opening the connection
fails, the exception is raised, and the ping to
reconnect does not happen.

Of course, I could be wrong. Here's a test.

In my.cnf, I set wait_timeout=10 and
interactive_timeout=10, then restart the mysql server.

in my.cnf, wait_timeout=10


import MySQLdb
import time
import _mysql_exceptions

db = MySQLdb.connect(user='testuser',passwd='passwd')
c = db.cursor()
c.execute('select 1')
d, = c.fetchone()
if d == 1:
print 'connection open and working'

c.execute('select 1')
print 'connection reaped by server'

print "trying to resurrect connection with ping()"
except _mysql_exceptions.OperationalError,e:
print "ping() raised an exception: %s" % e
print 'now, see if the connection is usable'
c.execute('select 1')
d, = c.fetchone()
if d == 1:
print 'connection resurrected with ping'
print 'connection is still down'


  • Dennis Allison

    Dennis Allison - 2006-01-18

    Logged In: YES

    After some investigation, this appears to be a mysql bug or
    brain-dead semantics for mysql_ping.

    See http://lists.mysql.com/mysql/190441 and

    Dieter Mauer suggests the right thing is to catch the
    exception and then

    reopen the connection (usually the "db" object has all
    information) and then raise an exception derived from
    "ZODB.POSException.ConflictError" (in order to let your
    be repeated).

    The problem appear to be present in 4.0.XX and 4.1.XX. Is
    it still there
    in MySQL 5.0. And does MySQL-Python and ZMYSQLDA grock


    On Mon, 16 Jan 2006, Dennis Allison wrote:

    I have been seeing occasional "release unlocked lock"
    errors from a
    storage error in a two-phase commit which really comes
    from a "Lost
    connection to MySQL server during query". Apparently this
    arises from
    a failure of self.db.ping() to find a connected database
    as the
    transaction is opened.

    It is possible that the connection has really timed out.

    Looking at the code it appears that ZMySQLDA does not
    attempt to
    recreate the connection. Is that correct?

    What's the recommend approach to re-establish the connection?

  • Andy Dustman

    Andy Dustman - 2006-01-18

    Logged In: YES

    ZMySQLDA should work with MySQL-5.0, AFAIK.

    According to another message I got:

    "Versions of mysql older than 5.0.3 were settings reconnect
    = 1, while
    newer versions set reconnect = 0 on newly created
    connections. As a
    result, mysqldb will no longer reconnect if the connection
    failed with
    mysql newer than 5.0.3."

    Transparently reconnecting is probably a bad idea anyway,
    since it can cause bad things to happen with transactions.

    I may not have time to work on this for awhile. Why don't
    you try Dieter's suggestion and work on a patch?

  • Andy Dustman

    Andy Dustman - 2006-02-27

    Logged In: YES

    ZMySQLDA is fixed in CVS. New ping() behavior is a feature
    of MySQL-5.0.


Log in to post a comment.