Currently if something goes wrong with Beaker's connections to the database causing them to stay open but never receive a response (for example, if cfengine munges iptables every 5 minutes) they will hang until the kernel's TCP connection timeout kicks in (default 10 days!). This can leave beakerd and/or the web application running but in an unusable state. As a last resort, Beaker should configure a read timeout on the MySQL database connections so that we get an exception after a decent amount of time (5-10 minutes perhaps) instead of everything hanging forever.
I originally recommended this in an off-list mail back in June 2011: > Maybe we could set a read timeout for Beaker's MySQL connections. As of > 5.0.25 the C API supports MYSQL_OPT_READ_TIMEOUT [1]. I'm not sure if that > would catch runaway queries though. > > The next problem is that MySQL-python doesn't let us set that (there is > a patch for it which has been untouched for two years [2]). I did, however, > come across "oursql" which is apparently a newer, improved set of Python > bindings for the MySQL API. It would let us set the read timeout [3]. > However, support for oursql was only added to sqlalchemy in 0.6... > > Maybe this is something to revisit after Beaker 0.7? > > [1] http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html > [2] http://sourceforge.net/tracker/?func=detail&aid=2809208&group_id=22307&atid=374935 > [3] http://packages.python.org/oursql/api.html The bad news is, there has still been no movement in the timeout patch for MySQL-python (for almost four years!). I see this as a strong reason why we should be looking to switch to alternative MySQL bindings. Oursql is probably still a good choice, but there are other more recently developed ones which interoperate with async libraries like gevent, which might be desireable for the (very long term) future where we might want to support ZeroMQ/Comet/etc using an async library. The good news is, we are now on sqlalchemy 0.6 which means we are in a position to pick alternative MySQL bindings.
Yeah, we should look into this further before 1.0
It looks like MySQL-python did acquire support for read timeouts way back in 2012, it just isn't documented: http://sourceforge.net/p/mysql-python/patches/75/ http://stackoverflow.com/questions/17416961/mysqldb-read-timeout Just need to double check if that's in MySQL-python on RHEL6. If so, we can just add a query string arg &read_timeout=300 to sqlalchemy.dburi in server.cfg as per: http://docs.sqlalchemy.org/en/latest/core/engines.html#custom-dbapi-connect-arguments
(In reply to Dan Callaghan from comment #3) > Just need to double check if that's in MySQL-python on RHEL6. Nope :-( TypeError: 'read_timeout' is an invalid keyword argument for this function Build Date: Sat 05 Dec 2009 02:54:41 AEST This will have to wait until Beaker is on RHEL7 I suppose...