REST API requests raising errors for SQLAlchemy backend

[elsapassaro]

When using the feature "Connect to your AiiDA REST API" on Materials Cloud Explore, the interface launches a series of requests to the REST API. For profiles with SQLAlchemy backend, the first time a user is connecting some of those requests fail, raising the errors below. This is not happening for profiles with Django backend, and might be related to #3743.
Mentioning @sphuber @CasperWA

Traceback (most recent call last):
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/flask_restful/__init__.py", line 458, in wrapper
    resp = resource(*args, **kwargs)
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/flask/views.py", line 88, in view
    self = view.view_class(*class_args, **class_kwargs)
  File "/home/passaro/aiida/aiida_core/aiida/restapi/resources.py", line 103, in __init__
    self.trans = self._translator_class(**kwargs)
  File "/home/passaro/aiida/aiida_core/aiida/restapi/translator/base.py", line 70, in __init__
    self.qbobj = QueryBuilder()
  File "/home/passaro/aiida/aiida_core/aiida/orm/querybuilder.py", line 317, in __init__
    backend = backend or get_manager().get_backend()
  File "/home/passaro/aiida/aiida_core/aiida/manage/manager.py", line 129, in get_backend
    self._load_backend()
  File "/home/passaro/aiida/aiida_core/aiida/manage/manager.py", line 80, in _load_backend
    manager.load_backend_environment(profile, validate_schema=schema_check)
  File "/home/passaro/aiida/aiida_core/aiida/backends/manager.py", line 118, in load_backend_environment
    self.validate_schema(profile)
  File "/home/passaro/aiida/aiida_core/aiida/backends/manager.py", line 227, in validate_schema
    self.validate_schema_version(profile)
  File "/home/passaro/aiida/aiida_core/aiida/backends/manager.py", line 303, in validate_schema_version
    schema_version_database = self.get_schema_version_database()
  File "/home/passaro/aiida/aiida_core/aiida/backends/sqlalchemy/manager.py", line 131, in get_schema_version_database
    return config.attributes['rev']  # pylint: disable=unsubscriptable-object
KeyError: 'rev'
127.0.0.1 - - [01/Apr/2020 11:14:11] "GET /api/v4/users HTTP/1.1" 500 -

Traceback (most recent call last):
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/flask_restful/__init__.py", line 458, in wrapper
    resp = resource(*args, **kwargs)
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/flask/views.py", line 88, in view
    self = view.view_class(*class_args, **class_kwargs)
  File "/home/passaro/aiida/aiida_core/aiida/restapi/resources.py", line 216, in __init__
    super().__init__(**kwargs)
  File "/home/passaro/aiida/aiida_core/aiida/restapi/resources.py", line 103, in __init__
    self.trans = self._translator_class(**kwargs)
  File "/home/passaro/aiida/aiida_core/aiida/restapi/translator/nodes/node.py", line 52, in __init__
    super().__init__(**kwargs)
  File "/home/passaro/aiida/aiida_core/aiida/restapi/translator/base.py", line 70, in __init__
    self.qbobj = QueryBuilder()
  File "/home/passaro/aiida/aiida_core/aiida/orm/querybuilder.py", line 317, in __init__
    backend = backend or get_manager().get_backend()
  File "/home/passaro/aiida/aiida_core/aiida/manage/manager.py", line 129, in get_backend
    self._load_backend()
  File "/home/passaro/aiida/aiida_core/aiida/manage/manager.py", line 80, in _load_backend
    manager.load_backend_environment(profile, validate_schema=schema_check)
  File "/home/passaro/aiida/aiida_core/aiida/backends/manager.py", line 118, in load_backend_environment
    self.validate_schema(profile)
  File "/home/passaro/aiida/aiida_core/aiida/backends/manager.py", line 227, in validate_schema
    self.validate_schema_version(profile)
  File "/home/passaro/aiida/aiida_core/aiida/backends/manager.py", line 303, in validate_schema_version
    schema_version_database = self.get_schema_version_database()
  File "/home/passaro/aiida/aiida_core/aiida/backends/sqlalchemy/manager.py", line 131, in get_schema_version_database
    return config.attributes['rev']  # pylint: disable=unsubscriptable-object
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/alembic/runtime/environment.py", line 110, in __exit__
    self._remove_proxy()
  File "/home/passaro/Envs/aiida_develop_py36/lib/python3.6/site-packages/alembic/util/langhelpers.py", line 49, in _remove_proxy
    del globals_[attr_name]
KeyError: 'script'
127.0.0.1 - - [01/Apr/2020 11:14:11] "GET /api/v4/processes/projectable_properties HTTP/1.1" 500 -

[CasperWA]

This doesn't seem (on first look) to be related to thread-handling, but I'll try and take a peek in the code and with the issue. Could you possibly try and come up with a code example that reproduces the error, maybe something that could be used as a test as well?

[elsapassaro]

The Explore interface launches the same kind of requests that we check in the tests, so I'm not sure how to write a test for this.
To reproduce it, you can start the REST API with the verdi restapi command, using a profile with SQLAlchemy backend, and then connect to https://www.materialscloud.org/explore/ownrestapi?base_url=http://127.0.0.1:5000/api/v4. This happens only the first time, i.e., if you refresh the page it will work.

And I've just added the following in the file aiida/restapi/api.py at line 75 (in the else clause) in order to print out the traceback

import traceback
traceback.print_exc()

[CasperWA]

Okay. I think I'll try and fix #3743 in the near future then, and see if this issue is solved as a consequence as well - since it seems to be difficult to pinpoint the issue here exactly.

[CasperWA]

There is a PR open for #3743 (#3974), after it is merged, this issue should be looked into again.

[sphuber]

Can @asle85 not check out your PR branch and run the test? As I understand it, it should be reproducible in a deterministic manner.