patch broken topics query to make topic timespan list API endpoint work

[rahulbot]

It seems everyone is onboard to slowly disable Topic Mapper, but we do need to support people retrieving their data. It'd be nice to do this via the UI, but viewing topics via Topic Mapper is broken right now. The first issue seems to be this:

Error - got a HTTP status code of 500 with the message "Internal Server Error", body: {"error":"Error(s): Caught exception in MediaWords::Controller::Api::V2::Topics::Timespans->list \"Traceback (most recent call last):\n File \"/opt/mediacloud/src/common/python/mediawords/db/result/result.py\", line 79, in __execute\n cursor.execute(*query_args)\n File \"/usr/lib/python3/dist-packages/psycopg2/extras.py\", line 142, in execute\n return super(DictCursor, self).execute(query, vars)\npsycopg2.errors.SyntaxError: syntax error at or near \"AND\"\nLINE 18: AND snapshots_id = 6911\n ^\n\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n File \"/opt/mediacloud/src/common/python/mediawords/db/handler.py\", line 173, in query\n return DatabaseResult(cursor=self.__db,\n File \"/opt/mediacloud/src/common/python/mediawords/db/result/result.py\", line 34, in __init__\n self.__execute(cursor=cursor,\n File \"/opt/mediacloud/src/common/python/mediawords/db/result/result.py\", line 95, in __execute\n raise McDatabaseResultException(\nmediawords.db.exceptions.result.McDatabaseResultException: Invalid query: syntax error at or near \"AND\"\nLINE 18: AND snapshots_id = 6911\n ^\n; query: (' SELECT\\n timespans_id,\\n period,\\n start_date,\\n end_date,\\n story_count,\\n story_link_count,\\n medium_count,\\n medium_link_count,\\n model_r2_mean,\\n model_r2_stddev,\\n model_num_media,\\n foci_id,\\n snapshots_id\\n FROM timespans AS t\\n where\\n topics_id = %s AND\\n AND snapshots_id = 6911\\n AND foci_id IS NULL\\n \\n ORDER BY\\n period,\\n start_date,\\n end_date\\n', ((5914,),))\""}

That error suggests the root is a SQL query syntax problem caused by having two ANDs in a row. Reading the code quickly, it looks like this line in Timespans.pm is the problem. Each of the clauses on the lines of code just after has an AND at the front, so the one on this line becomes a duplicate no matter what. I believe removing the AND on line this could fix this initial problem. Do you read the same bug here?

If so, is there an easy way to fix this and redeploy this webapp-api container? Of course there is the potential that we'd fix this and something else would be problematic... but I thought it prudent to ask once I saw this.

Related: if we can't make topics browsable again, we'll go with plan B. I think that is probably writing a script to generate emails to users with links to the existing topic timespan data dump files (which hopefully exist for all successfully completed topics).

[philbudne]

I agree. I can't see how it ever worked as-is.

[philbudne]

Image built and containers redeployed using new image.

[rahulbot]

Still getting the same exact error. If you're logged into our web tools with an admin account this test URL shows the error:
https://topics.mediacloud.org/api/topics/5914/snapshots/6911/timespans/list

{
  "message": "Error - got a HTTP status code of 500 with the message \"Internal Server Error\", body: {\"error\":\"Error(s): Caught exception in MediaWords::Controller::Api::V2::Topics::Timespans->list \\\"Traceback (most recent call last):\\n  File \\\"/opt/mediacloud/src/common/python/mediawords/db/result/result.py\\\", line 79, in __execute\\n    cursor.execute(*query_args)\\n  File \\\"/usr/lib/python3/dist-packages/psycopg2/extras.py\\\", line 142, in execute\\n    return super(DictCursor, self).execute(query, vars)\\npsycopg2.errors.SyntaxError: syntax error at or near \\\"AND\\\"\\nLINE 18:             AND snapshots_id = 6911\\n                     ^\\n\\n\\nDuring handling of the above exception, another exception occurred:\\n\\nTraceback (most recent call last):\\n  File \\\"/opt/mediacloud/src/common/python/mediawords/db/handler.py\\\", line 173, in query\\n    return DatabaseResult(cursor=self.__db,\\n  File \\\"/opt/mediacloud/src/common/python/mediawords/db/result/result.py\\\", line 34, in __init__\\n    self.__execute(cursor=cursor,\\n  File \\\"/opt/mediacloud/src/common/python/mediawords/db/result/result.py\\\", line 95, in __execute\\n    raise McDatabaseResultException(\\nmediawords.db.exceptions.result.McDatabaseResultException: Invalid query: syntax error at or near \\\"AND\\\"\\nLINE 18:             AND snapshots_id = 6911\\n                     ^\\n; query: ('        SELECT\\\\n            timespans_id,\\\\n            period,\\\\n            start_date,\\\\n            end_date,\\\\n            story_count,\\\\n            story_link_count,\\\\n            medium_count,\\\\n            medium_link_count,\\\\n            model_r2_mean,\\\\n            model_r2_stddev,\\\\n            model_num_media,\\\\n            foci_id,\\\\n            snapshots_id\\\\n        FROM timespans AS t\\\\n        where\\\\n            topics_id = %s AND\\\\n            AND snapshots_id = 6911\\\\n            AND foci_id IS NULL\\\\n            \\\\n        ORDER BY\\\\n            period,\\\\n            start_date,\\\\n            end_date\\\\n', ((5914,),))\\\"\"}", 
  "statusCode": 500
}

[philbudne]

I get a 403 ( User lacks read permission for the requested topic ) for your URL.
BUT I think I finally got the new code running (was deploying the same old code), so please try again.

[philbudne]

(and/or give me instructions I can try to follow for the test topic in Emily's account, OR give [email protected] access to the topic!)

[rahulbot]

Works now!