Bug 963030 - no read timeout for database connections
no read timeout for database connections
Status: NEW
Product: Beaker
Classification: Community
Component: scheduler (Show other bugs)
Unspecified Unspecified
low Severity unspecified (vote)
: ---
: ---
Assigned To: beaker-dev-list
: Triaged
Depends On:
  Show dependency treegraph
Reported: 2013-05-14 22:24 EDT by Dan Callaghan
Modified: 2018-02-05 19:41 EST (History)
6 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

  None (edit)
Description Dan Callaghan 2013-05-14 22:24:43 EDT
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.
Comment 1 Dan Callaghan 2013-05-14 22:32:17 EDT
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.
Comment 2 Nick Coghlan 2013-05-14 22:35:05 EDT
Yeah, we should look into this further before 1.0
Comment 3 Dan Callaghan 2015-04-08 03:57:55 EDT
It looks like MySQL-python did acquire support for read timeouts way back in 2012, it just isn't documented:



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:

Comment 4 Dan Callaghan 2015-04-08 04:05:42 EDT
(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...

Note You need to log in before you can comment on or make changes to this bug.