Pending SQLAlchemy transaction causes Heroku Dyno Crash and needs rollback

[snekiam]

Describe the bug
This issue will be apparent if the API returns "I'm sorry, I don't understand. Please try another question." for ever question.

To Reproduce
Steps to reproduce the behavior:

1. Go to 'nimbus.calpolycsai.com'
2. Ask a question like 'What is Foaad's email?'
3. See error

Expected behavior
The chat bot should return Foaad's email

Additional context
Stack trace from Heroku logs:

2020-03-08T20:07:29.842495+00:00 app[web.1]: [2020-03-08 20:07:29,841] ERROR in app: Exception on /ask [POST]
2020-03-08T20:07:29.842496+00:00 app[web.1]: Traceback (most recent call last):
2020-03-08T20:07:29.842497+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1171, in _execute_context
2020-03-08T20:07:29.842497+00:00 app[web.1]:     conn = self._revalidate_connection()
2020-03-08T20:07:29.842497+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 457, in _revalidate_connection
2020-03-08T20:07:29.842498+00:00 app[web.1]:     "Can't reconnect until invalid "
2020-03-08T20:07:29.842499+00:00 app[web.1]: sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back
2020-03-08T20:07:29.842499+00:00 app[web.1]: 
2020-03-08T20:07:29.842499+00:00 app[web.1]: The above exception was the direct cause of the following exception:
2020-03-08T20:07:29.842500+00:00 app[web.1]: 
2020-03-08T20:07:29.842500+00:00 app[web.1]: Traceback (most recent call last):
2020-03-08T20:07:29.842501+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 2446, in wsgi_app
2020-03-08T20:07:29.842501+00:00 app[web.1]:     response = self.full_dispatch_request()
2020-03-08T20:07:29.842501+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 1951, in full_dispatch_request
2020-03-08T20:07:29.842502+00:00 app[web.1]:     rv = self.handle_user_exception(e)
2020-03-08T20:07:29.842502+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/flask_cors/extension.py", line 161, in wrapped_function
2020-03-08T20:07:29.842503+00:00 app[web.1]:     return cors_after_request(app.make_response(f(*args, **kwargs)))
2020-03-08T20:07:29.842503+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 1820, in handle_user_exception
2020-03-08T20:07:29.842503+00:00 app[web.1]:     reraise(exc_type, exc_value, tb)
2020-03-08T20:07:29.842504+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/flask/_compat.py", line 39, in reraise
2020-03-08T20:07:29.842504+00:00 app[web.1]:     raise value
2020-03-08T20:07:29.842505+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 1949, in full_dispatch_request
2020-03-08T20:07:29.842505+00:00 app[web.1]:     rv = self.dispatch_request()
2020-03-08T20:07:29.842505+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line 1935, in dispatch_request
2020-03-08T20:07:29.842506+00:00 app[web.1]:     return self.view_functions[rule.endpoint](**req.view_args)
2020-03-08T20:07:29.842506+00:00 app[web.1]:   File "/nimbus/flask_api.py", line 67, in handle_question
2020-03-08T20:07:29.842507+00:00 app[web.1]:     "answer": nimbus.answer_question(question)
2020-03-08T20:07:29.842507+00:00 app[web.1]:   File "/nimbus/nimbus.py", line 28, in answer_question
2020-03-08T20:07:29.842507+00:00 app[web.1]:     answer = qa.answer(ans_dict)
2020-03-08T20:07:29.842508+00:00 app[web.1]:   File "/nimbus/QA.py", line 59, in answer
2020-03-08T20:07:29.842508+00:00 app[web.1]:     db_data = self._get_data_from_db(extracted_vars)
2020-03-08T20:07:29.842509+00:00 app[web.1]:   File "/nimbus/QA.py", line 53, in _get_data_from_db
2020-03-08T20:07:29.842509+00:00 app[web.1]:     return self.db_query(extracted_vars)
2020-03-08T20:07:29.842509+00:00 app[web.1]:   File "/nimbus/QA.py", line 164, in _get_property
2020-03-08T20:07:29.842510+00:00 app[web.1]:     value = db.get_property_from_entity(prop=prop, entity=ent, identifier=ent_string)
2020-03-08T20:07:29.842510+00:00 app[web.1]:   File "/nimbus/database_wrapper.py", line 482, in get_property_from_entity
2020-03-08T20:07:29.842511+00:00 app[web.1]:     for row in query_obj.all():
2020-03-08T20:07:29.842511+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py", line 3233, in all
2020-03-08T20:07:29.842511+00:00 app[web.1]:     return list(self)
2020-03-08T20:07:29.842512+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py", line 3389, in __iter__
2020-03-08T20:07:29.842512+00:00 app[web.1]:     return self._execute_and_instances(context)
2020-03-08T20:07:29.842513+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py", line 3414, in _execute_and_instances
2020-03-08T20:07:29.842513+00:00 app[web.1]:     result = conn.execute(querycontext.statement, self._params)
2020-03-08T20:07:29.842513+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 982, in execute
2020-03-08T20:07:29.842522+00:00 app[web.1]:     return meth(self, multiparams, params)
2020-03-08T20:07:29.842522+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
2020-03-08T20:07:29.842523+00:00 app[web.1]:     return connection._execute_clauseelement(self, multiparams, params)
2020-03-08T20:07:29.842523+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
2020-03-08T20:07:29.842523+00:00 app[web.1]:     distilled_params,
2020-03-08T20:07:29.842524+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1176, in _execute_context
2020-03-08T20:07:29.842524+00:00 app[web.1]:     e, util.text_type(statement), parameters, None, None
2020-03-08T20:07:29.842525+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
2020-03-08T20:07:29.842525+00:00 app[web.1]:     util.raise_from_cause(sqlalchemy_exception, exc_info)
2020-03-08T20:07:29.842525+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
2020-03-08T20:07:29.842526+00:00 app[web.1]:     reraise(type(exception), exception, tb=exc_tb, cause=cause)
2020-03-08T20:07:29.842526+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 152, in reraise
2020-03-08T20:07:29.842526+00:00 app[web.1]:     raise value.with_traceback(tb)
2020-03-08T20:07:29.842527+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1171, in _execute_context
2020-03-08T20:07:29.842527+00:00 app[web.1]:     conn = self._revalidate_connection()
2020-03-08T20:07:29.842527+00:00 app[web.1]:   File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 457, in _revalidate_connection
2020-03-08T20:07:29.842527+00:00 app[web.1]:     "Can't reconnect until invalid "
2020-03-08T20:07:29.842528+00:00 app[web.1]: sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
2020-03-08T20:07:29.842528+00:00 app[web.1]: [SQL: SELECT `Courses`.id AS `Courses_id`, `Courses`.dept AS `Courses_dept`, `Courses`.`courseNum` AS `Courses_courseNum`, `Courses`.`termsOffered` AS `Courses_termsOffered`, `Courses`.units AS `Courses_units`, `Courses`.`courseName` AS `Courses_courseName`, `Courses`.raw_concurrent_text AS `Courses_raw_concurrent_text`, `Courses`.raw_recommended_text AS `Courses_raw_recommended_text`, `Courses`.raw_prerequisites_text AS `Courses_raw_prerequisites_text` 
2020-03-08T20:07:29.842529+00:00 app[web.1]: FROM `Courses`]
2020-03-08T20:07:29.842529+00:00 app[web.1]: [parameters: [immutabledict({})]]

I think this may be related to this.

[mfekadu]

So, should we remember to session.rollback() in the code somewhere via a try/except?

• perhaps even by modifying the __del__?

Why isn't there a timeout that SQLAlchemy would do by default?