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"