Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

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

ZMySQLDA-2.0.9
closed
Andy Dustman
MySQLdb (285)
5
2012-09-19
2005-12-14
James Washington
No

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

interactive_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'

time.sleep(12)
try:
c.execute('select 1')
except:
print 'connection reaped by server'

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

Discussion

  • Dennis Allison
    Dennis Allison
    2006-01-18

    Logged In: YES
    user_id=31903

    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
    http://bugs.mysql.com/bug.php?id=14057
    http://bugs.mysql.com/bug.php?id=2845

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

    reopen the connection (usually the "db" object has all
    necessary
    information) and then raise an exception derived from
    "ZODB.POSException.ConflictError" (in order to let your
    request
    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
    MySQL-5.0?

    -d

    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
    user_id=71372

    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
    user_id=71372

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