Add statement timeouts to some web sql queries

The SQL queries are designed to be highly optimized and should return
in milliseconds even with millions of rows.  However, sometimes
query planners are misled by certain characteristics and can end
up performing suboptimally.

To protect the web server in case that happens, set a statement or
query timeout for the queries which list builds or buildsets.  This
will instruct mysql or postgresql to limit execution of the buildset
or build listing queries to 30 seconds -- but only if these queries
originate in zuul-web.  Other users (such as the admin tools) may
still run these queries without an explicit time limit (though the
server may still have one).

Unfortunately (or perhaps fortunately) the RDBMSs can occasionally
satisfy the queries we use in testing in less than 1ms, making a
functional test of this feature impractical (we are unable to set
the timeout to 0ms).

Change-Id: If2f01b33dc679ab7cf952a4fbf095a1f3b6e4faf

1 files changed, 8 insertions, 3 deletions

diff --git a/zuul/web/__init__.py b/zuul/web/__init__.py
index 47226fd7d..8a520a570 100755
--- a/zuul/web/__init__.py
+++ b/zuul/web/__init__.py
@@ -463,6 +463,8 @@ class ZuulWebAPI(object):
 
         self.cache_expiry = 1
         self.static_cache_expiry = zuulweb.static_cache_expiry
+        # SQL build query timeout, in milliseconds:
+        self.query_timeout = 30000
 
     @property
     def log(self):
@@ -1454,7 +1456,8 @@ class ZuulWebAPI(object):
             newrev=newrev, uuid=uuid, job_name=job_name, voting=voting,
             nodeset=nodeset, result=result, final=final, held=held,
             complete=complete, limit=limit, offset=skip, idx_min=_idx_min,
-            idx_max=_idx_max, exclude_result=exclude_result)
+            idx_max=_idx_max, exclude_result=exclude_result,
+            query_timeout=self.query_timeout)
 
         return [self.buildToDict(b, b.buildset) for b in builds]
 
@@ -1510,7 +1513,8 @@ class ZuulWebAPI(object):
 
         buildsets = connection.getBuildsets(
             tenant=tenant_name, project=project, pipeline=pipeline,
-            branch=branch, complete=True, limit=1)
+            branch=branch, complete=True, limit=1,
+            query_timeout=self.query_timeout)
         if not buildsets:
             raise cherrypy.HTTPError(404, 'No buildset found')
 
@@ -1551,7 +1555,8 @@ class ZuulWebAPI(object):
             tenant=tenant_name, project=project, pipeline=pipeline,
             change=change, branch=branch, patchset=patchset, ref=ref,
             newrev=newrev, uuid=uuid, result=result, complete=complete,
-            limit=limit, offset=skip, idx_min=_idx_min, idx_max=_idx_max)
+            limit=limit, offset=skip, idx_min=_idx_min, idx_max=_idx_max,
+            query_timeout=self.query_timeout)
 
         return [self.buildsetToDict(b) for b in buildsets]