Server 500s after running for a period of time.

[snekiam]

Describe the bug After running for several hours (or several days), the flask server starts returning a 500 for every question it gets asked. (Something like this: The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.)

To Reproduce Steps to reproduce the behavior:

1. Start the flask server
2. Wait several days, running requests to the server intermittently.
3. Observe that the server is now returning status 500 with the above message for all requests.

Expected behavior The server continues to handle requests without a server error.

Screenshots Problem manifestation:

Desktop (where the request was made):

• OS: Windows 10
• Browser Postman
• Version v7.23.0

Server (Where the flask server was running):

• OS: Ubuntu (Running in Docker)

Additional context I think (this)[https://stackoverflow.com/questions/26891971/mysql-connection-not-available-when-use-sqlalchemymysql-and-flask] is the problem we're running into, but I haven't tried this yet.

Here's the logs from that request:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1173, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 775, in _init_compiled
    self.cursor = self.create_cursor()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 1131, in create_cursor
    return self._dbapi_connection.cursor()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 978, in cursor
    return self.connection.cursor(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection.py", line 908, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

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 87, in handle_question
    response = {"answer": nimbus.answer_question(question)}
  File "/nimbus/nimbus.py", line 36, in answer_question
    answer = qa.answer(ans_dict)
  File "/nimbus/QA.py", line 50, in answer
    db_data = self.db_query(extracted_vars, self.db)
  File "/nimbus/QA.py", line 196, in _chain_db_access
    for key, val in fn(extracted_vars, db).items():
  File "/nimbus/QA.py", line 87, in _get_property
    value = db.get_property_from_entity(
  File "/nimbus/database_wrapper.py", line 513, 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 1173, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 775, in _init_compiled
    self.cursor = self.create_cursor()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 1131, in create_cursor
    return self._dbapi_connection.cursor()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 978, in cursor
    return self.connection.cursor(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection.py", line 908, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available.
[SQL: SELECT `Profs`.`firstName` AS `Profs_firstName`, `Profs`.`lastName` AS `Profs_lastName`, `Profs`.`phoneNumber` AS `Profs_phoneNumber`, `Profs
`.`researchInterests` AS `Profs_researchInterests`, `Profs`.email AS `Profs_email`, `Profs`.`OfficeHours` AS `Profs_OfficeHours`, `Profs`.`Platform
` AS `Profs_Platform`, `Profs`.`LatestQuarter` AS `Profs_LatestQuarter`, `Profs`.`Office` AS `Profs_Office`, `Profs`.department AS `Profs_departmen
t`, `Profs`.title AS `Profs_title` 
FROM `Profs`]
[parameters: [immutabledict({})]]
(Background on this error at: http://sqlalche.me/e/e3q8)

[snekiam]

Note: This is from an unmerged fork running on google cloud on python v3.8, but the vast, vast majority of the code is the same (I think all the sqlalchemy stuff is critically). I would bet that this is what is happening on heroku also, and that its not just a memory issue that we're running into.

[snekiam]

It would appear our timeout is 8 hours (28800 seconds):

So problems will probably start to occur after around 8 hours, but I haven't tested this to know for sure.

[Jason-Ku]

You could try making the timeout value very low if you wanted to test this out!