如何启用MySQL客户端自动重新连接MySQLdb？

我遇到了PHP的做法:

my_bool reconnect = 1;
mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);

但MySQLdb(python-mysql)没有运气.

任何人都可以提一个线索吗？谢谢.

1> 小智..：

---

我通过创建一个包装cursor.execute()方法的函数解决了这个问题,因为这就是抛出MySQLdb.OperationalError异常的原因.上面的另一个例子暗示它是conn.cursor()抛出此异常的方法.

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

---

如果你重新连接,你应该知道你丢失了任何`@ variables`和`SETtings`.此外,半成品交易将是"ROLLBACK".这可能会导致一些混乱.

2> 小智..：

---

我对提议的解决方案有问题,因为它没有捕获异常.我不知道为什么.

我已经用ping(True)我认为更整洁的陈述解决了这个问题:

import MySQLdb
con=MySQLdb.Connect()
con.ping(True)
cur=con.cursor()

从这里得到它:http://www.neotitans.com/resources/python/mysql-python-connection-error-2006.html

---

在运行查询之前进行Ping操作被认为是一种浪费资源并且不可靠的反模式:https://www.percona.com/blog/2010/05/05/checking-for-a-live-database-connection-considered-有害/

3> Paul D. Eden..：

---

如果您使用的是ubuntu Linux,则会在python-mysql包中添加一个补丁,该补丁添加了设置相同MYSQL_OPT_RECONNECT选项的功能(请参阅此处).我没有尝试过.

不幸的是,由于与自动连接和转换的冲突(此处描述),该补丁后来被删除.

该页面的评论说:1.2.2-7发表于2008-06-19的无畏发布

python-mysqldb(1.2.2-7)不稳定; 急=低

[Sandro Tosi]*debian/control - 列表中的项目行以2个空格开头,以避免在网页上重新格式化(关闭:#480341)

[Bernd Zeimetz]*debian/patches/02_reconnect.dpatch: - 删除补丁:在Storm中评论解释了问题:

    # 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.