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

[James Washington]

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'

[Dennis Allison]

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]

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]

Logged In: YES
user_id=71372

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