How do you control MySQL timeouts from SQLAlchemy?

Question

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.

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?

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"

Answer 1

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.

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.

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.

Answer 2

I believe you are reaching a totally different error, this is a dreaded "mysql has gone away" error, If I'm right the solution is to update to a newer mysqldb driver as the bug has been patches in the driver.

If for some reason you can't/won't update you should try the SA fix for this

db= create_engine('mysql://root@localhost/test', pool_recycle=True) 

Answer 3

Could this be a bug in the mysql/python connector? https://bugs.launchpad.net/myconnpy/+bug/328998 which says the time out is hard-coded to 10 seconds.

To really see where the breakdown is, you could use a packet sniffer to checkout the conversation between the server and the client. wireshark + tcpdump works great for this kind of thing.