Integration tests failing with SQLAlchemy 1.4

[chaen]

There seem to have been some changes in the API of SQLAlchemy which needs a bit of adaptation in our code.
Namely https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#orm-query-is-internally-unified-with-select-update-delete-2-0-style-execution-available

PR to follow

the error show up in the integration tests

      # Now we monitor again, job one, and find out that job1 has disappeared
      # So we cancel the job and the files
      res = self.db.cancelNonExistingJob(opID, job1GUID)
>     self.assertTrue(res['OK'])
E     AssertionError: False is not true

ServerInstallDIR/DIRAC/tests/Integration/DataManagementSystem/Test_Client_FTS3.py:435: AssertionError
----------------------------- Captured stdout call -----------------------------
cancelNonExistingJob: unexpected exception
Traceback (most recent call last):
  File "/home/dirac/ServerInstallDIR/DIRAC/DataManagementSystem/DB/FTS3DB.py", line 482, in cancelNonExistingJob
    session.execute(updStmt)
  File "/home/dirac/ServerInstallDIR/diracos/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.py", line 1625, in execute
    _parent_execute_state is not None,
  File "/home/dirac/ServerInstallDIR/diracos/usr/lib64/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1821, in orm_pre_session_exec
    update_options,
  File "/home/dirac/ServerInstallDIR/diracos/usr/lib64/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1949, in _do_pre_synchronize_evaluate
    from_=err,
  File "<string>", line 5, in raise_
InvalidRequestError: Could not evaluate current criteria in Python: "Can't evaluate criteria against alternate class <class 'DIRAC.DataManagementSystem.Client.FTS3File.FTS3File'>". Specify 'fetch' or False for the synchronize_session execution option.

[chaen]

I have a fix but I don't quite understand this behaviour, so I will not push it yet. I've asked sqlalchemy/sqlalchemy#6158

[chaen]

Moved to a discussion sqlalchemy/sqlalchemy#6159

[chrisburr]

@chaen Can this be closed?

[chaen]

Actually no, a new one showed up :-/ sqlalchemy/sqlalchemy#6280

[chaen]

So, small summary of the situation: we can't easily use SQLAlchemy 1.4. There are several aspects to that:

• In 1.4, a lot of work has been done to make the bahaviour of the Core and the ORM consistent.
• In 1.4.7, an infinite recursive loop was fixed, but this resulted in breaking some use cases, including ours in the RMS

For the 1st point, the full discussion is available here sqlalchemy/sqlalchemy#6159 and this translated into a fix on our side #5080

For the second point, the full discussion is here sqlalchemy/sqlalchemy#6294 and that led to an issue tracked here sqlalchemy/sqlalchemy#6301
To put it simply in case of the RMS, the back reference link going from Operation to Request and from File to Operation is not eagerly loaded as it used to be. As pointed out in the discussion, there would be ways around that, however, the issue suggests that the behavior could change back. So for the time being, I've decided to freeze the version of SQLAlchemy until all this settles.

That being said, on our side, we are not doing things quite the way they are supposed to be done with SQLAlchemy. Making sure the client does not see the library was a nice wish, but it turns out to be problematic (because relying on internal behavior of the library), and it's anyway very easy to have the library available on the client side (including power9 and arm as soon as we run with DIRACOS2). So bottom line is that we should also refactor the code on our side. That will make our life much easier.

[fstagni]

We're using SQLAlchemy for few DBs in our code, and in a non-consistent way. Is only the RMS affected?

[chaen]

That's where it is the most striking at least. That's the most "complex" use case we have