Repository: dmwm/wmcore
Issue: JobCreator crashing on CERN agents - oracle issue

And oracle issues are hitting us again and bringing components down. Right now I see vocms0281 JobCreator down, here is the traceback [1].

It seems those come from the SQLAlchemy update in the latest WMAgent release. It now wraps the exception from the DBAPI, thus the error message is now somehow different and the exception handler is not able to find those anymore:
https://github.com/dmwm/WMCore/blob/master/src/python/WMCore/Database/DBExceptionHandler.py#L9


[1]
```
2019-06-13 06:54:02,923:139664752584448:INFO:JobCreatorPoller:Found end in iteration over subscription 6180
2019-06-13 06:54:03,019:139664752584448:ERROR:base:Error closing cursor
Traceback (most recent call last):
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1333, in _safe_close_cursor
    cursor.close()
InterfaceError: not connected
2019-06-13 06:54:03,062:139664752584448:ERROR:JobCreatorPoller:Failed to execute JobCreator. Error: (cx_Oracle.InterfaceError) not connected
(Background on this error at: http://sqlalche.me/e/rvf5)
Traceback (most recent call last):
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch2/lib/python2.7/site-packages/WMComponent/JobCreator/JobCreatorPoller.py", line 414, in algorithm
    self.pollSubscriptions()
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch2/lib/python2.7/site-packages/WMComponent/JobCreator/JobCreatorPoller.py", line 549, in pollSubscriptions
    wmbsJobGroups = next(jobSplittingFunction)
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch2/lib/python2.7/site-packages/WMComponent/JobCreator/JobCreatorPoller.py", line 87, in runSplitter
    groups = jobFactory(**splitParams)
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch2/lib/python2.7/site-packages/WMCore/JobSplitting/JobFactory.py", line 94, in __call__
    self.algorithm(*args, **kwargs)
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch2/lib/python2.7/site-packages/WMCore/JobSplitting/EventAwareLumiBased.py", line 103, in algorithm
    lDict = self.getFilesSortedByLocation(avgEventsPerJob)
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch2/lib/python2.7/site-packages/WMCore/JobSplitting/JobFactory.py", line 460, in getFilesSortedByLocation
    lDict = self.sortByLocation()
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch2/lib/python2.7/site-packages/WMCore/JobSplitting/JobFactory.py", line 237, in sortByLocation
    fileset = self.loadFiles(size=self.limit)
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch2/lib/python2.7/site-packages/WMCore/JobSplitting/JobFactory.py", line 339, in loadFiles
    newResults = resultProxy.fetchmany(size=length)
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1247, in fetchmany
    e, None, None, self.cursor, self.context
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1241, in fetchmany
    l = self.process_rows(self._fetchmany_impl(size))
  File "/data/srv/wmagent/v1.2.2.patch2/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1157, in _fetchmany_impl
    return self.cursor.fetchmany(size)
InterfaceError: (cx_Oracle.InterfaceError) not connected
(Background on this error at: http://sqlalche.me/e/rvf5)
```

Changed files:
src/python/WMCore/Database/DBExceptionHandler.py

diff --git a/src/python/WMCore/Database/DBExceptionHandler.py b/src/python/WMCore/Database/DBExceptionHandler.py
index d08235c9b..b719d349e 100644
--- a/src/python/WMCore/Database/DBExceptionHandler.py
+++ b/src/python/WMCore/Database/DBExceptionHandler.py
@@ -11,9 +11,11 @@ import threading
 # ORA-12545: Connect failed because target host or object does not exist
 # ORA-00060: deadlock detected while waiting for resource
 # ORA-01033: ORACLE initialization or shutdown in progress
+# (cx_Oracle.InterfaceError) not connected  # same as ORA-03114, in the new SQLAlchemy
 # and those two MySQL exceptions
 DB_CONNECTION_ERROR_STR = ["ORA-03113", "ORA-03114", "ORA-03135", "ORA-12545", "ORA-00060", "ORA-01033",
-                           "MySQL server has gone away", "Lock wait timeout exceeded"]
+                           "MySQL server has gone away", "Lock wait timeout exceeded",
+                           "(cx_Oracle.InterfaceError) not connected"]
 
 
 def db_exception_handler(f):