Make DB timeout message more explicit

[thinkh]

• Release number or git hash: v6.1.0
• Web browser version and OS: Linux, Chrome
• Environment (local or deployed): both

This ticket mainly refers to the error message after a DB timeout, which currently states something about a proxy error.

Steps to reproduce

Change the config in your local development setup to reproduce the error easily:

1. Edit the workspace config.json

   "tdp_publicdb": {
       "dburl": "postgresql://xxxxxxxxxxxx",
       "statement_timeout": "'1s'",
       "statement_timeout_query": "set statement_timeout to {}"
   },

2. Start Ordino
3. Select a gene list
4. Add an aggregated cell line score, as for instance:
   • Organ: bladder
   • Data type: relative copy number
   • Aggregation: average
5. Add score

Observed behavior

The frontend shows the following error:

The server logs OperationalError error:

api_1          | OperationalError: (psycopg2.extensions.QueryCanceledError) canceling statement due to statement timeout
api_1          |  [SQL: 'SELECT d.ensg AS id, avg(relativecopynumber) AS score              FROM cellline.tdp_copynumber d              INNER JOIN public.tdp_geneassignment ga ON d.ensg = ga.ensg              WHERE d.celllinename = ANY(ARRAY(SELECT celllinename FROM cellline.tdp_cellline WHERE species = %(species)s  AND organ = %(organ)s))               AND ga.genesetname = %(panel_ensg)s              GROUP BY d.ensg'] [parameters: {'panel_ensg': u'Cancer Gene Census', 'species': u'human', 'organ': u'bladder'}]
api_1          | {'GATEWAY_INTERFACE': 'CGI/1.1',
api_1          |  'HTTP_ACCEPT': 'application/json',
api_1          |  'HTTP_ACCEPT_ENCODING': 'gzip, deflate, br',
api_1          |  'HTTP_ACCEPT_LANGUAGE': 'en-US,en;q=0.9',
api_1          |  'HTTP_CACHE_CONTROL': 'no-cache',
api_1          |  'HTTP_CONNECTION': 'close',
api_1          |  'HTTP_COOKIE': 'randomCredentials=big_hodgkin@tku1am; _ga=GA1.1.698344166.1573994138; _pk_ses.3.1fff=1; remember_token=big_hodgkin|11a55c4d47a7fd1ef0f72f8a652caf353047fa18dc6fea7b1e8466ff26ba12044de587d23234c551d3b4b8e6db37ae142d4dc328b2fa25f7c44694692ccc15b2; session=.eJwdjsFqg0AQQH-lzDmHthsvQg8tG2ULs0vDGJm9CKnW3VFbMA3ihvx7pefH470bNF9zdwmQ_87XbgdNbCG_wcMZckD6VKiPwcs42BIzlirxZCMnXK3YgGJWXx5Wq_sVaeOEe69xYRoUlmZ12iyuNhmm42ifT8GRefTSCtcfyiY7Ir0JT4fkySiWMGF6j0i9cpozlGrv6lO0EgJTEZyuFk7F5PSr2hrKb66n6gmlGLnGF7jv4Hrp5v9_OMe-CT9tP8RvuP8Bj5xLwQ.EL1Ibg.r3_xAwqlH89xGEmTUc5x-6wSJps; _pk_id.3.1fff=9f83c292cec1dde4.1563276156.87.1574680305.1574680104.',
api_1          |  'HTTP_HOST': 'localhost:8080',
api_1          |  'HTTP_PRAGMA': 'no-cache',
api_1          |  'HTTP_REFERER': 'http://localhost:8080/',
api_1          |  'HTTP_SEC_FETCH_MODE': 'cors',
api_1          |  'HTTP_SEC_FETCH_SITE': 'same-origin',
api_1          |  'HTTP_USER_AGENT': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36',
api_1          |  'PATH_INFO': '/publicdb/gene_cellline_score/score',
api_1          |  'QUERY_STRING': 'table=copynumber&data_subtype=relativecopynumber&agg=avg&species=human&target=Ensembl&filter_panel_ensg=Cancer+Gene+Census&filter_organ=bladder',
api_1          |  'REMOTE_ADDR': '172.24.0.1',
api_1          |  'REMOTE_PORT': '48594',
api_1          |  'REQUEST_METHOD': 'GET',
api_1          |  'SCRIPT_NAME': '/api/tdp/db',
api_1          |  'SERVER_NAME': '7c23e6284185',
api_1          |  'SERVER_PORT': '80',
api_1          |  'SERVER_PROTOCOL': 'HTTP/1.1',
api_1          |  'SERVER_SOFTWARE': 'gevent/1.1 Python/2.7',
api_1          |  'werkzeug.proxy_fix.orig_http_host': 'localhost:8080',
api_1          |  'werkzeug.proxy_fix.orig_remote_addr': '172.24.0.1',
api_1          |  'werkzeug.proxy_fix.orig_wsgi_url_scheme': 'http',
api_1          |  'werkzeug.request': <Request 'http://localhost:8080/api/tdp/db/publicdb/gene_cellline_score/score?table=copynumber&data_subtype=relativecopynumber&agg=avg&species=human&target=Ensembl&filter_panel_ensg=Cancer+Gene+Census&filter_organ=bladder' [GET]>,
api_1          |  'wsgi.errors': <open file '<stderr>', mode 'w' at 0x7effb66671e0>,
api_1          |  'wsgi.input': <gevent.pywsgi.Input object at 0x7eff9bdf82c0>,
api_1          |  'wsgi.multiprocess': False,
api_1          |  'wsgi.multithread': False,
api_1          |  'wsgi.run_once': False,
api_1          |  'wsgi.url_scheme': 'http',
api_1          |  'wsgi.version': (1, 0)} failed with OperationalError

Expected behavior

Please change it to something like:

Error. 
Your data request could not be completed within X minutes. Therefore, it was cancelled. 
This problem might have been caused by requesting too much data at once. 
In case of any questions, please contact ${the administrator}.

(where X is the DB timeout duration and the administrator can be relplaced with a name and link to the persons email address)

Ideally, this message should only be shown if a DB timeout occurs but not if the application does not respond for some other reason.

Tasks

Fix the score first (because we can test it) and then see if we can apply the fix to other queries as well.

• Check get_score_data() in sql.py or get_filtered_data() in db.py as entry point
• Add try/catch block for OperationalError: (psycopg2.extensions.QueryCanceledError) (see psycopg2 documentation) -> turned out to be a sqlalchemy.exc.OperationalError
• Send custom error with HTTP error code 408, e.g., abort(408, error.message)
• Extend errorAlertHandler in notifications.ts to print custom error message as described above
• Check if there are other places (beside scores) that are long-running and could benefit from this error handler

Current error message:

Definition of done

• Simulating the error should (see setup above) should show the custom error message

[keckelt]

Is that the error that you receive when you first use Ordino/the server after a long idle time?

[thinkh]

@keckelt No, it is an error message when the query (e.g., for a score column) gets a time out from the database.