Invalid transaction is causing TopArticles API to fail

[glciampaglia]

The TopArticles endpoint is failing intermittently. It fails once every two or three queries. As a result Hoaxy is showing an error on the front page, instead of the lists of top popular claims and fact-checks.

• The log of Hoaxy shows that when there is an error we get this exception (see below). It looks like something related to an invalid transaction.
• The error pops up around 9 AM 06/01. Looking further in the logs, it looks like that between 6 AM and 8:30 AM there was a space issue ("no space left on device" errors). Wondering if the error is related to that.
• Looking at top20_articles_monthly it looks like it hasn't been updated since 05/08

StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: u'SELECT max(top20_article_monthly.upper_day) AS max_1 \nFROM top20_article_monthly'] [parameters: [{}]]
2018-06-01 14:04:45,055 - hoaxy(api) - ERROR: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: u'SELECT max(top20_article_monthly.upper_day) AS max_1 \nFROM top20_article_monthly'] [parameters: [{}]]
Traceback (most recent call last):
  File "/home/data/apps/hoaxy-backend/hoaxy/backend/api.py", line 582, in query_top_articles
    df = db_query_top_articles(engine, **q_kwargs)
  File "/home/data/apps/hoaxy-backend/hoaxy/ir/search.py", line 895, in db_query_top_articles
    upper_day = get_max(session, Top20ArticleMonthly.upper_day)
  File "/home/data/apps/hoaxy-backend/hoaxy/database/functions.py", line 105, in get_max
    return q.scalar()
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2785, in scalar
    ret = self.one()
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2756, in one
    ret = self.one_or_none()
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2726, in one_or_none
    ret = list(self)
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2797, in __iter__
    return self._execute_and_instances(context)
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2820, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1121, in _execute_context
    None, None)
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1114, in _execute_context
    conn = self._revalidate_connection()
  File "/u/truthy/miniconda3/envs/hoaxy-backend/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 424, in _revalidate_connection
    "Can't reconnect until invalid "
StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: u'SELECT max(top20_article_monthly.upper_day) AS max_1 \nFROM top20_article_monthly'] [parameters: [{}]]

[shaochengcheng]

Two Bugs fixed. 1. query the top articles 2. generate top articles.

[filmenczer]

I m reopening this because we have reports (and we have observed ourselves) an intermittent problem where the website hangs due to an error from the top articles API.

@benabus was going to catch this in the front-end, to ensure that the website at least works even when the top articles API fails.

@shaochengcheng could you please look into the API issue?

[benabus]

I did fix this in regards to the top articles. This particular issue must be some somewhere else. I'll look into it.

[benabus]

See https://github.com/IUNetSci/hoaxy-backend/issues/24