/ask/ sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

[mfekadu]

Stacktrace

expand to see

``` [2020-05-28 16:53:21,075] ERROR in app: Exception on /ask [POST] Traceback (most recent call last): File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1171, in _execute_context conn = self._revalidate_connection() File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 456, in _revalidate_connection raise exc.InvalidRequestError( sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 2446, in wsgi_app response = self.full_dispatch_request() File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1951, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/local/lib/python3.8/dist-packages/flask_cors/extension.py", line 161, in wrapped_function return cors_after_request(app.make_response(f(*args, **kwargs))) File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1820, in handle_user_exception reraise(exc_type, exc_value, tb) File "/usr/local/lib/python3.8/dist-packages/flask/_compat.py", line 39, in reraise raise value File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1949, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1935, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/nimbus/flask_api.py", line 122, in handle_question response = {"answer": nimbus.answer_question(question)} File "/nimbus/nimbus.py", line 42, in answer_question answer = qa.answer(ans_dict) File "/nimbus/QA.py", line 52, in answer db_data = self.db_query(extracted_vars, self.db) File "/nimbus/QA.py", line 231, in _chain_db_access for key, val in fn(extracted_vars, db).items(): File "/nimbus/QA.py", line 95, in _get_property value = db.get_property_from_entity( File "/nimbus/database_wrapper.py", line 583, in get_property_from_entity props = self._get_property_from_entity(prop, entity, identifier, tag_column_map) File "/nimbus/database_wrapper.py", line 558, in _get_property_from_entity for row in query_obj.all(): File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 3233, in all return list(self) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 3389, in __iter__ return self._execute_and_instances(context) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/query.py", line 3414, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 982, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1095, in _execute_clauseelement ret = self._execute_context( File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1175, in _execute_context self._handle_dbapi_exception( File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception util.raise_from_cause(sqlalchemy_exception, exc_info) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 152, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1171, in _execute_context conn = self._revalidate_connection() File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 456, in _revalidate_connection raise exc.InvalidRequestError( sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: SELECT `Profs`.first_name AS `Profs_first_name`, `Profs`.last_name AS `Profs_last_name`, `Profs`.phone_number AS `Profs_phone_number`, `Profs`.research_interests AS `Profs_research_interests`, `Profs`.email AS `Profs_email`, `Profs`.office_hours AS `Profs_office_hours`, `Profs`.platform AS `Profs_platform`, `Profs`.latest_quarter AS `Profs_latest_quarter`, `Profs`.office AS `Profs_office`, `Profs`.department AS `Profs_department`, `Profs`.title AS `Profs_title` FROM `Profs`] [parameters: [immutabledict({})]] ```

[mfekadu]

I ran

docker restart $CONTAINER_ID

and that resolved this issue.

@snekiam @richagadgil we discussed over zoom about how to handle transactions and rollbacks in SQLAlchemy... what were those resources again?

[snekiam]

I actually have code that should do this - I can push it after work. But it's pretty basic - it uses a flask ErrorHandler to capture sqlalchemy OperationalError exceptions.

[mfekadu]

Awesome! Thanks @snekiam ! Looking forward to the PR

[mfekadu]

Additional Details (same code dev branch as of commit 05071aea287690f0913b565bdd5b75fa2c68f75e)

GCE server connection to DB often tries to rollback

Render.com connection to DB often does not

[mfekadu]

@cameron-toy you brought up a good point about Professor_Teaches_Section

it may be interesting to later test questions that access Professor_Teaches_Section to see if the rollback issue is consistent on GCE. That would help us discover some knowledge about MySQL Views.