如何从SQLAlchemy控制MySQL超时? [英] How do you control MySQL timeouts from SQLAlchemy?

问题描述

使用SQLAlchemy在MySQL数据库上运行时，从客户端控制超时的正确方法是什么? connect_timeout URL参数似乎不足.

What's the right way to control timeouts, from the client, when running against a MySQL database, using SQLAlchemy? The connect_timeout URL parameter seems to be insufficient.

我对正在运行数据库的计算机(例如，意外从网络中消失)会发生什么感兴趣.我不担心查询本身花费的时间太长.

I'm more interested in what happens when the machine that the database is running on, e.g., disappears from the network unexpectedly. I'm not worried about the queries themselves taking too long.

如果 somehost 在之前不可用，则以下脚本将执行您期望的操作(即，大约一秒钟后超时).但是，如果 somehost 在while循环中期间发生故障(例如，在循环开始后尝试拉出其网络电缆)，则超时时间似乎至少要达到18秒.我还缺少其他一些设置或参数吗?

The following script does what you'd expect (i.e., time out after approximately one second) if somehost is unavailable before the while loop is ever reached. But if somehost goes down during the while loop (e.g., try yanking out its network cable after the loop has started), then the timeout seems to take at least 18 seconds. Is there some additional setting or parameter I'm missing?

wait_timeout会话变量不起作用也就不足为奇了，因为我认为这是服务器端变量.但是我把它扔在那里只是为了确保.

It's not surprising that the wait_timeout session variable doesn't work, as I think that's a server-side variable. But I threw it in there just to make sure.

from sqlalchemy import *
from sqlalchemy.exc import *
import time
import sys

engine = create_engine("mysql://user:password@somehost/test?connect_timeout=1")
try:
    engine.execute("set session wait_timeout = 1;")
    while True:
        t = time.time()
        print t
        engine.execute("show tables;")
except DBAPIError:
    pass
finally:
    print time.time() - t, "seconds to time out"

推荐答案

由于TCP的工作方式，这是不可能的.如果另一台计算机断开了网络连接，它将仅停止响应传入的数据包.您看到的"18秒"是TCP堆栈上的某些内容由于无响应而超时.

this isn't possible due to the way TCP works. if the other computer drops off the network, it will simply stop responding to incoming packets. the "18 seconds" you're seeing is something on your TCP stack timing out due to no response.

获得所需行为的唯一方法是让计算机在死亡之前立即生成"i'm dying"消息.如果死亡是意外的，那是完全不可能的.

the only way you can get your desired behavior is to have the computer generate a "i'm dying" message immediately before it dies. which, if the death is unexpected, is completely impossible.

您听说过听音吗?这些是高可用性系统每隔一秒钟或更短的时间就互相发送一次的数据包，以使对方知道它们仍然存在.如果要让应用程序立即"知道服务器已消失，则必须首先确定立即"有多长时间(1秒，200毫秒等)，然后设计一个系统(例如心跳)来检测何时另一个系统不再存在.

have you ever heard of hearbeats? these are packets that high-availability systems send to each other every second or less to let the other one know they still exist. if you want your application to know "immediately" that the server is gone, you first have to decide how long "immediate" is (1 second, 200 ms, etc.) and then designed a system (such as heartbeats) to detect when the other system is no longer there.

这篇关于如何从SQLAlchemy控制MySQL超时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！