New version of cx-oracle (7.3.0) breaks oracle connection with old sqlalchemy

[amaltaro]

As briefly discussed in this HN:
https://hypernews.cern.ch/HyperNews/CMS/get/webInterfaces/1763.html

we decided to update python cx-oracle library from 5.2.1 to 7.3.0, which is the latest cx-oracle version still supporting python2.7.

It turns out this upgrade broke DBS in cmsweb-testbed, with apparently an incompatibility between the SQLAlchemy and cx-Oracle version. This issue hasn't been seen (yet) in WMAgent, and I think the reason for that is that WMAgent is using one of the latest SQLAlchemy version 1.3.3, while DBS is still using 0.9.6.

Here is the traceback from the DBSGlobaReader logs, when querying the datatiers RESTful API:

INFO:cherrypy.access:REQUEST [20/Jan/2021 20:13:01] XXX 16981 GET /datatiers [/DC=ch/XXX] [{}]
ERROR:dbs.web.DBSReaderModel:Wed Jan 20 19:13:01 2021 dbsException-invalid-input2: 'twophase' is an invalid keyword argument for this function
Traceback (most recent call last):
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/cms/dbs3/3.16.0-comp/lib/python2.7/site-packages/dbs/web/DBSReaderModel.py", line 568, in listDataTiers
    conn = self.dbi.connection()
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/cms/dbs3/3.16.0-comp/lib/python2.7/site-packages/WMCore/Database/DBCore.py", line 120, in connection
    return self.engine.connect()
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1717, in connect
    return self._connection_cls(self, **kwargs)
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 59, in __init__
    self.__connection = connection or engine.raw_connection()
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1786, in raw_connection
    return self.pool.unique_connection()
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/pool.py", line 273, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/pool.py", line 630, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/pool.py", line 433, in checkout
    rec = pool._do_get()
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/pool.py", line 949, in _do_get
    return self._create_connection()
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/pool.py", line 278, in _create_connection
    return _ConnectionRecord(self)
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/pool.py", line 404, in __init__
    self.connection = self.__connect()
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/pool.py", line 530, in __connect
    connection = self.__pool._creator()
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 89, in connect
    return dialect.connect(*cargs, **cparams)
  File "/data/srv/HG2102a/sw/slc7_amd64_gcc630/external/py2-sqlalchemy096/0.9.6/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 376, in connect
    return self.dbapi.connect(*cargs, **cparams)
TypeError: 'twophase' is an invalid keyword argument for this function
INFO:cherrypy.access:[20/Jan/2021:20:13:01] dbs-global-r-f5fd4f764-sjqz8 XXX "GET /dbs/int/global/DBSReader/datatiers HTTP/1.1" 500 Internal Server Error [data: 7333 in 74 out 2113 us ] [auth: OK "/DC=ch/XXX" "" ] [ref: "" "DBSClient/3.16.0/" ]

[amaltaro]

Googling for it, brought me to this issue under the SQLAlchemy project:
sqlalchemy/sqlalchemy#3997

where they suggest to update to SQLAlchemy >= 1.1.11, which removes the hard-coded twophase parameter.

@yuyiguo Yuyi, if you agree, I can try building a specific SQLAlchemy for DBS (based on 1.1.13, which has some unicode performance bugfix) and ask Imran for a new deployment tomorrow.

[amaltaro]

I have created this PR: cms-sw/cmsdist#6578

which might fail to build, since I didn't test it myself.

[yuyiguo]

Thanks Alan

[amaltaro]

@yuyiguo Hi Yuyi, I'm just wondering if you managed to test the performance of DBS in cmsweb-testbed?

It has the latest COMP cx-Oracle and the changes provided in this PR:
cms-sw/cmsdist#6578

once we confirm that performance is Okay, we can continue with other changes and developments. Thanks

[yuyiguo]

No, I haven't. I have something at hands. Later today or tomorrow I will do it.

[yuyiguo]

@amaltaro
I just did tests against cmswe-testbed. The results were compatible with the tests done on cmsweb-k8s-testbed in June.

[amaltaro]

Wonderful news, Yuyi! Thanks for testing it. From my side, feel free to close this issue then.

[yuyiguo]

Tested both k8s and vm BE. All test results were compatible with previous tests.