ping causes InterfaceError (0,'') ??

[Leon Starr]

I want my code to anticipate and recover from losing the MySQL server connection. I've googled around for hours and my best guess at the correct way to do this is:

try:
db.ping()
except MySQLdb.OperationalError:
db.connect()

execute query...

To test, I close the connection and trigger the above. Oddly, I get:
InterfaceError: (0,'')

If I change the except clause to trap MySQLdb.Error, I reconnect okay, but I get the sense that something is horribly wrong here.

Help gladly appreciated! And before you ask:
Python 2.4.3, MySQLdb 1.2.1, MySQL 5.0 and, sadly,
Windows XP Prof

• Leon Starr

[Andy Dustman]

Um, you closed the connection, so calling any methods will raise that. For a real test, open a connection, kill it by something other than close, and then try ping(). In the command-line client, this looks like this (it automatically reconnects):

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 1 | andy | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.05 sec)

mysql> kill 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: NONE

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | andy | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

[Leon Starr]

That was quite helpful. Still not getting expected results, but the error message looks better!

As suggested, I had my Python process connect, then executed the kill manually. Then, from the Python process, executed db.ping() and trapped the error. Rather than reconnect as expected, I received a 2013 'Lost connection...' error.

I know that ping() is supposed to attempt an automatic reconnect, but under what circumstances should that succeed? (Or why didn't it in my test?)

Thanks again for your time.

[Andy Dustman]

By default, MySQL-5.0 does not automatically reconnect. See the comments here:

http://dev.mysql.com/doc/refman/5.0/en/mysql-ping.html

If you are using transactiions (and you likely are if you are using MySQL 5.0), an automatic reconnect is tantamount to an invisible rollback. You are probably better off catching the exception you got and raising a new one that causes your transaction to restart at some higher level.

Though really, you shouldn't be pinging in the middle of a transaction, and maybe you are only pinging at the beginning. Like maybe your connection has potentially sat idle for a long time and has been timed out by the server, and you'd like ping() to transparently reopen it. This is more reasonable, but arguably "Explicit is better than implicit", and it's probably better to do this up-front than to rely on some behind-the-scenes magic.

1.3 will have a more generalized option passing method, so you'll be able to turn this option on if you really need it.

[Leon Starr]

Okay, my scattered research led me to the mistaken assumption that ping-autoreconnect was the accepted idiom.

It seems that the correct strategy is:

Ping prior to any query to ensure connectivity and then, if MySQLdb.Error is raised,
explicitly reconnect.