3/4 更新:

我已经做了一些测试并证明使用结帐事件处理程序来检查断开连接是否适用于 Elixir。 开始认为我的问题与调用 session.commit() 有关来自子流程？ 更新:我刚刚通过调用 session.commit() 反驳了自己在子流程中，更新了下面的示例。我正在使用多处理模块来创建子进程。

这是显示它应该如何工作的代码(甚至没有使用 pool_recycle !):

from sqlalchemy import exc
from sqlalchemy import event
from sqlalchemy.pool import Pool
from elixir import *
import multiprocessing as mp

class SubProcess(mp.Process):
    def run(self):
        a3 = TestModel(name="monkey")
        session.commit()

class TestModel(Entity):
    name = Field(String(255))

@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    cursor = dbapi_connection.cursor()
    try:
        cursor.execute("SELECT 1")
    except:
        # optional - dispose the whole pool
        # instead of invalidating one at a time
        # connection_proxy._pool.dispose()

        # raise DisconnectionError - pool will try
        # connecting again up to three times before raising.
        raise exc.DisconnectionError()
    cursor.close()

from sqlalchemy import create_engine
metadata.bind = create_engine("mysql://foo:bar@localhost/some_db", echo_pool=True)
setup_all(True)

subP = SubProcess()

a1 = TestModel(name='foo')
session.commit()

# pool size is now three.

print "Restart the server"
raw_input()

subP.start()

#a2 = TestModel(name='bar')
#session.commit()

更新 2:

我被迫寻找另一个解决方案，因为 1.2.2 版本的 MySQL-python 放弃了对重新连接参数的支持。有人有解决办法吗？ :\

更新 1(旧解决方案，不适用于 MySQL-python 版本 > 1.2.2):

找到解决方案:通过connect_args={'reconnect':True}到 create_engine调用修复了问题，自动重新连接。甚至似乎不需要结帐事件处理程序。

因此，在问题的示例中:

metadata.bind = create_engine("mysql://foo:bar@localhost/db_name", pool_size=100, pool_recycle=3600, connect_args={'reconnect':True})

原始问题:

针对这个问题做了相当多的谷歌搜索，但似乎还没有找到特定于 Elixir 的解决方案 - 我正在尝试使用 SQLAlchemy 文档中的“Disconnect Handling - Pessimistic”示例来处理 MySQL 断开连接。但是，当我对此进行测试时(通过重新启动 MySQL 服务器)，在我的结帐事件处理程序之前出现了“MySQL 服务器已消失”错误。

这是我用来初始化 elixir 的代码:

##### Initialize elixir/SQLAlchemy
# Disconnect handling
from sqlalchemy import exc
from sqlalchemy import event
from sqlalchemy.pool import Pool

@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    logging.debug("***********ping_connection**************")
    cursor = dbapi_connection.cursor()
    try:
        cursor.execute("SELECT 1")
    except:
        logging.debug("######## DISCONNECTION ERROR #########")            
        # optional - dispose the whole pool
        # instead of invalidating one at a time
        # connection_proxy._pool.dispose()

        # raise DisconnectionError - pool will try
        # connecting again up to three times before raising.
        raise exc.DisconnectionError()
    cursor.close()

metadata.bind= create_engine("mysql://foo:bar@localhost/db_name", pool_size=100, pool_recycle=3600)

setup_all()

我创建了 elixir 实体对象并用 session.commit() 保存它们，在此期间我看到从上面定义的事件生成的“ping_connection”消息。但是，当我重新启动 mysql 服务器并再次测试时，它失败了，并在 ping 连接事件之前显示 mysql 服务器已消失消息。

这是从相关行开始的堆栈跟踪:

  File "/usr/local/lib/python2.6/dist-packages/elixir/entity.py", line 1135, in get_by
    return cls.query.filter_by(*args, **kwargs).first()
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1963, in first
    ret = list(self[0:1])
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1857, in __getitem__
    return list(res)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 2032, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 2047, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1399, in execute
    params)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1532, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1640, in _execute_context
    context)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1633, in _execute_context
    context)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/default.py", line 330, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (OperationalError) (2006, 'MySQL server has gone away') 

最佳答案

最后的解决方法是在操作和加载 elixir 实体之前在方法的开头调用 session.remove()。它所做的是将连接返回到池中，以便再次使用时，将触发池的结帐事件，并且我们的处理程序将检测到断开连接。 From SQLAlchemy docs :

It’s not strictly necessary to remove the session at the end of the request - other options include calling Session.close(), Session.rollback(), Session.commit() at the end so that the existing session returns its connections to the pool and removes any existing transactional context. Doing nothing is an option too, if individual controller methods take responsibility for ensuring that no transactions remain open after a request ends.

我希望在 elixir 文档中提到一条非常重要的小信息。但我猜它假设先验知识是 SQLAlchemy？

关于python - MySQL 服务器已经消失 - 通过结帐事件处理程序的断开连接处理不起作用，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/7912731/