Software (Backend): Occasionally fail to write to database

[mlej8]

INFO:werkzeug:127.0.0.1 - - [01/Mar/2022 14:13:34] "POST /api/openbci/15748/collect/start HTTP/1.1" 201 -
INFO:dcp:Stopped collecting for character a.
INFO:dcp:Writing collected data for character a to the database.
ERROR:dcp:Exception on /api/openbci/15748/collect/stop [POST]
Traceback (most recent call last):
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\engine\base.py", line 990, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\engine\default.py", line 670, in do_commit
    dbapi_connection.commit()
psycopg2.OperationalError: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\flask\app.py", line 2070, in wsgi_app
    response = self.full_dispatch_request()
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\flask\app.py", line 1515, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\flask_cors\extension.py", line 165, in wrapped_function
    return cors_after_request(app.make_response(f(*args, **kwargs)))
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\flask\app.py", line 1513, in full_dispatch_request
    rv = self.dispatch_request()
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\flask\app.py", line 1499, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "C:\Users\B21\Documents\McGill_NeuroTech\NeuroTechX-McGill-2021\software\speller\data_collection_platform\backend\dcp\api.py", line 126, in openbci_process_collect_stop
    if not write_stream_data(subprocess_dict):
  File "C:\Users\B21\Documents\McGill_NeuroTech\NeuroTechX-McGill-2021\software\speller\data_collection_platform\backend\dcp\api.py", line 185, in write_stream_data
    db.session.commit()
  File "<string>", line 2, in commit
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\orm\session.py", line 1428, in commit
    self._transaction.commit(_to_root=self.future)
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\orm\session.py", line 836, in commit
    trans.commit()
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\engine\base.py", line 2329, in commit
    self._do_commit()
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\engine\base.py", line 2512, in _do_commit
    self._connection_commit_impl()
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\engine\base.py", line 2483, in _connection_commit_impl
    self.connection._commit_impl()
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\engine\base.py", line 992, in _commit_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\engine\base.py", line 1994, in _handle_dbapi_exception
    util.raise_(
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\engine\base.py", line 990, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
  File "c:\users\b21\anaconda3\envs\dcp\lib\site-packages\sqlalchemy\engine\default.py", line 670, in do_commit
    dbapi_connection.commit()
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

(Background on this error at: http://sqlalche.me/e/14/e3q8)
INFO:werkzeug:127.0.0.1 - - [01/Mar/2022 14:14:00] "POST /api/openbci/15748/collect/stop HTTP/1.1" 500 -
INFO:werkzeug:127.0.0.1 - - [01/Mar/2022 14:14:02] "OPTIONS /api/openbci/15748/collect/start HTTP/1.1" 200 -
INFO:dcp:BCI is collecting data for character "o" with phase 5.811946409141116 and frequency 7.85.
INFO:werkzeug:127.0.0.1 - - [01/Mar/2022 14:14:02] "POST /api/openbci/15748/collect/start HTTP/1.1" 201 -

The database fails to write the data sometimes and otuputs an sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly

More specifically the db.session.commit() caused that.

  File "C:\Users\B21\Documents\McGill_NeuroTech\NeuroTechX-McGill-2021\software\speller\data_collection_platform\backend\dcp\api.py", line 126, in openbci_process_collect_stop
    if not write_stream_data(subprocess_dict):
  File "C:\Users\B21\Documents\McGill_NeuroTech\NeuroTechX-McGill-2021\software\speller\data_collection_platform\backend\dcp\api.py", line 185, in write_stream_data
    db.session.commit()

Please investigate ways to solve this problem while keeping in mind that our current database on AWS only allows for one connection.

This might be a good starter point: https://stackoverflow.com/questions/55457069/how-to-fix-operationalerror-psycopg2-operationalerror-server-closed-the-conn

[gupta-parth]

Added some sql engine configs directly to main in config.py to help with this issue