search

dmwm / CRABServer

15 stars 38 forks source link

make CRAB REST work with current cx-oracle #6046

Closed belforte closed 3 years ago

belforte commented 4 years ago

crabserver build is currently hardcoded to use cx-oracle51 [1] while all other CMSWEB applications use 5.2.1 [2] and current version from Oracle is 7.3 [3] This makes longer term life at risk and we need to change things so to be able to evolve with others.

[1] https://github.com/cms-sw/cmsdist/blob/45fbe63a03d209ff1cfc4f7f35b871cd212bcf9f/crabserver.spec#L14 https://github.com/cms-sw/cmsdist/blob/comp_gcc630/py2-cx-oracle51.spec [2] https://github.com/cms-sw/cmsdist/blob/comp_gcc630/py2-cx-oracle.spec [3] https://oracle.github.io/python-cx_Oracle/

I tried to build CRABServer with default cx_oracle in cmsweb, i.e. py2-cx-oracle, but then INSERT fails to put long strings into columns declared as CLOB (while working with 5.1.2) . See below for details.

belforte commented 4 years ago

@sharmaprajesh I still need to fill this part with all details. Will let you know when done.

belforte commented 3 years ago

UPDATE we came back to this since CERN is moving CMS DataBases to new oracle version which requires cx_Oracle 5.2 Daina built a new crabserver with it

On 17/12/2020 11:42, Daina Dirmaite wrote:

I have redeployed crabserver in test2 cluster to use this oracle specification: https://github.com/cms-sw/cmsdist/blob/comp_gcc630/py2-cx-oracle.spec instead of https://github.com/cms-sw/cmsdist/blob/comp_gcc630/py2-cx-oracle51.spec Also, now crabserver runs v3.201216 release. I also configured and started crab-dev-tw01 to use test2 cluster.

and when testing, we found that it works. Both with DB instance on int2r (where CERN DBA's just migrated us) and old dvdb11.

I am quite puzzled at what I was finding back last spring, but there's no arguing with facts.

Will keep this open a bit more as we make more tests. Hopefully we will simply build a new crabserver, put it in testbed and it will work. Then we close.

ddaina commented 3 years ago

some information on this issue: HN thread where users hit this problem: https://hypernews.cern.ch/HyperNews/CMS/get/computing-tools/5640.html and CRAB meeting notes from 2020 May 8:

CMSWEB update broke CRABServer in some case. Fix deployed by Imran in preprod and working. Stefano will ask to deploy to prod (Monday) and write a GH issue to follow up with Oracle experts on a better solution. this is the (ORA-01461: can bind a LONG value only for insert into a LONG column) which appears for columns which are already declared as CLOB. And is not there with cx_oracle5.1 but only appears with newer cx_oracle5.2 Prajesh will follow up with Kate (DBA for CMS in CERN IT)

I checked crabserver logs for ORA-01461: can bind a LONG value... error and found quite a few of them:

Error messages:

 crabserver-20201217-crabserver-696b9fdfc6-ktbs2.log:[17/Dec/2020:23:55:35]    DatabaseError: ORA-01461: can bind a LONG value only for insert into a LONG column
 crabserver-20201217-crabserver-696b9fdfc6-ktbs2.log:[17/Dec/2020:23:55:35]  SERVER DATABASE ERROR 500/403 Execution error cx_Oracle.DatabaseError 2060850a3ff1be4139941106965bcd00 [instance: dev] (ORA-01461: can bind a LONG value only for insert into a LONG column); last statement: INSERT INTO <**>filemetadata (                tm_taskname, panda_job_id, job_id, fmd_outdataset, fmd_acq_era, fmd_sw_ver, fmd_in_events, fmd_global_tag,               fmd_publish_name, fmd_location, fmd_tmp_location, fmd_runlumi, fmd_adler32, fmd_cksum, fmd_md5, fmd_lfn, fmd_size,               fmd_type, fmd_parent, fmd_creation_time, fmd_filestate, fmd_direct_stageout, fmd_tmplfn)

Counts:

[root@crabserver-696b9fdfc6-ktbs2 crabserver]# grep -c 'SERVER DATABASE ERROR 500/403' crabserver*
crabserver-20201217-crabserver-696b9fdfc6-ktbs2.log:438
crabserver-20201218-crabserver-696b9fdfc6-ktbs2.log:0
ddaina commented 3 years ago

apparently all tasks that failed with this error were only my tasks:

[root@crabserver-696b9fdfc6-ktbs2 crabserver]# grep -c 'SERVER DATABASE ERROR 500/403.*ddirmait' crabserver*
crabserver-20201217-crabserver-696b9fdfc6-ktbs2.log:438
crabserver-20201218-crabserver-696b9fdfc6-ktbs2.log:0

[root@crabserver-696b9fdfc6-ktbs2 crabserver]# grep -c 'SERVER DATABASE ERROR 500/403.*belforte' crabserver*
crabserver-20201217-crabserver-696b9fdfc6-ktbs2.log:0
crabserver-20201218-crabserver-696b9fdfc6-ktbs2.log:0
belforte commented 3 years ago

THanks a lot @ddaina for having dug this out ! So maybe problem is still there, all in all. I did not remember that it only appears in some cases. Indeed I presume that we tried that in testbed first. As to "why only your jobs"... I suspect that I can make mine fail as well by slightly changing configuration. Let me look. Anyhow.. if the problem is still there, I don't think we'll solve it w/o Kate's help in January.

belforte commented 3 years ago

more hints from this https://hypernews.cern.ch/HyperNews/CMS/get/computing-tools/5640/1/1/1/1/1/1/1.html it appeared to be a problem with longish lumi lists


as feared David's submission failed to insert the new task into
CRAB's internal DataBase:

[06/May/2020:03:34:37]  SERVER DATABASE ERROR 500/403 Execution error cx_Oracle.DatabaseError edc14bddee9faddfdbefbf0a77389aa5 [instance: prod] (ORA-01461: can bind a LONG value only for insert into a LONG column)

Same for Attilio's submission:
[06/May/2020:07:54:19]  SERVER DATABASE ERROR 500/403 Execution error cx_Oracle.DatabaseError 097c40a5a0a5002adeccd323835387c5 [instance: prod] (ORA-01461: can bind a LONG value only for insert into a LONG column); last statement: INSERT INTO <**>tasks

I still have to check the other error reports, but suspect it will be the same.

THis is most likely due to the long list of lumis and runs to be processed,
although the Oracle message does not say which column in the table it is
complaining about.

e.g. for the last error in ddirmait jobs in test2 now server fails to insert an entry in FILEMETADATA table with this :

runlumi': "{'1': {'670757': '50', '670756': '50', '670755': '50', '670776': '50', '670753': '50', '670752': '50', '670750': '50', '670454': '50', '670455': '50', '670456': '50', '670457': '50', '670450': '50', '670451': '50', '670452': '50', '670758': '50', '672034': '50', '672035': '50', '670809': '50', '670808': '50', '670601': '50', '672307': '50', '672032': '50', '672033': '50', '671974': '50', '671221': '50', '671730': '50', '671223': '50', '670608': '50', '670459': '50', '670595': '50', '672028': '50', '670591': '50', '670184': '50', '672267': '50', '670599': '50', '670598': '50', '670190': '50', '672388': '50', '672389': '50', '671871': '50', '670194': '50', '670197': '50', '670353': '50', '670352': '50', '670351': '50', '670219': '50', '672386': '50', '672387': '50', '672384': '50', '672385': '50', '670722': '50', '670617': '50', '670448': '50', '670189': '50', '670727': '50', '670724': '50', '671039': '50', '671401': '50', '671402': '50', '670336': '50', '670447': '50', '670446': '50', '671450': '50', '670332': '50', '672022': '50', '672021': '50', '670633': '50', '672027': '50', '670635': '50', '672025': '50', '672180': '50', '670639': '50', '671344': '50', '672220': '50', '672531': '50', '672186': '50', '670605': '50', '672521': '50', '670772': '50', '670604': '50', '672250': '50', '670356': '50', '670201': '50', '672494': '50', '672468': '50', '670205': '50', '670204': '50', '670209': '50', '672390': '50', '672392': '50', '672553': '50', '672552': '50', '672551': '50', '670600': '50', '670731': '50', '670730': '50', '671710': '50', '672192': '50', '672191': '50', '672190': '50', '670739': '50', '671557': '50', '670602': '50', '671918': '50', '672199': '50', '672584': '50', '670382': '50', '670383': '50', '670384': '50', '672242': '50', '670386': '50', '670387': '50', '670388': '50', '670389': '50', '670761': '50', '670432': '50', '671739': '50', '670621': '50', '672640': '50', '672645': '50', '670363': '50', '672580': '50', '671704': '50', '672058': '50', '671911': '50', '670585': '50', '671997': '50', '672481': '50', '672480': '50', '672039': '50', '672523': '50', '670613': '50', '670981': '50', '671886': '50', '670231': '50', '671851': '50', '672525': '50', '671703': '50', '671700': '50', '671706': '50', '670545': '50', '672212': '50', '671258': '50', '670365': '50', '671318': '50', '670157': '50', '670151': '50', '671859': '50', '670399': '50', '670398': '50', '670397': '50', '670653': '50', '670427': '50', '672239': '50', '670421': '50', '670423': '50', '670616': '50', '671909': '50', '669465': '50', '670749': '50', '670851': '50', '671907': '50', '670124': '50', '671930': '50', '672634': '50', '670223': '50', '672530': '50', '671820': '50', '672550': '50', '670227': '50', '670226': '50', '670225': '50', '671793': '50', '671714': '50', '670587': '50', '671963': '50', '670152': '50', '670714': '50', '670418': '50', '670419': '50', '672588': '50', '670410': '50', '671676': '50', '670413': '50', '670411': '50', '672583': '50', '672625': '50', '671142': '50', '671712': '50', '672576': '50', '670213': '50', '670638': '50', '672172': '50', '670210': '50', '672177': '50', '671839': '50', '672383': '50', '672543': '50', '672541': '50', '670311': '50', '670195': '50', '671833': '50', '670619': '50', '672585': '50', '671834': '50', '671837': '50', '670426': '50', '671372': '50', '670193': '50', '670403': '50', '671165': '50', '670400': '50', '672197': '50', '670764': '50', '670409': '50', '670408': '50', '671927': '50', '671925': '50', '671924': '50', '672218': '50', '672457': '50', '671285': '50', '670198': '50', '670766': '50', '672215': '50', '670175': '50', '672217': '50', '670762': '50', '670847': '50', '670891': '50', '671807': '50', '672516': '50', '672515': '50', '671803': '50', '670765': '50', '670425': '50', '671114': '50', '671928': '50', '672060': '50', '672214': '50', '670360': '50', '672613': '50', '670366': '50', '670367': '50', '670364': '50', '671904': '50', '671735': '50', '670774': '50', '670084': '50', '670385': '50', '671408': '50', '672001': '50', '670578': '50', '672202': '50', '671954': '50', '671196': '50', '670439': '50', '670576': '50', '670574': '50', '671758': '50', '672209': '50', '670130': '50', '671755': '50', '671756': '50', '671955': '50', '670775': '50', '671376': '50', '672201': '50', '670583': '50', '670771': '50', '670777': '50', '672204': '50', '671812': '50', '672200': '50', '671280': '50', '671897': '50', '671375': '50', '672519': '50', '670770': '50', '670371': '50', '670370': '50', '670373': '50', '670372': '50', '670171': '50', '670374': '50', '670377': '50', '670379': '50', '670378': '50', '672605': '50', '672604': '50', '670224': '50', '670440': '50', '670465': '50', '671786': '50', '670467': '50', '670466': '50', '670468': '50', '671313': '50', '670181': '50', '671941': '50', '671940': '50', '670614': '50', '670615': '50', '672478': '50', '672479': '50', '670610': '50', '671748': '50', '670462': '50', '670742': '50', '670743': '50', '670744': '50', '670745': '50', '671741': '50', '670747': '50', '672009': '50', '672008': '50', '670584': '50', '670220': '50', '671216': '50', '671290': '50', '670626': '50', '672000': '50', '672003': '50', '672533': '50', '671299': '50', '670632': '50', '670588': '50', '671988': '50', '671866': '50', '670180': '50', '671865': '50', '670186': '50', '671697': '50', '670344': '50', '670634': '50', '670188': '50', '670347': '50', '670636': '50', '670342': '50', '672219': '50'}}"
belforte commented 3 years ago

In the examples in May, the user has a long lumi list in input, so crab submit was immediately failing. While here the jobs completed and I think all that will happen is that the lack of filemetadata will make publication fail.

My submissions from yesterday has 1 lumi per job. I guess that's why there's no error.

It goes w/o saying that all DB colums which hold lumi list (or any kind of string lists) are declared a CLOB not LONG. Well.. at least that was my conclusion, but as usual I may have missed something. Maybe it is another column which complain

QL> describe filemetadata;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TM_TASKNAME                   NOT NULL VARCHAR2(255)
 PANDA_JOB_ID                       NUMBER(11)
 JOB_ID                         VARCHAR2(20)
 FMD_OUTDATASET                NOT NULL VARCHAR2(500)
 FMD_ACQ_ERA                   NOT NULL VARCHAR2(255)
 FMD_SW_VER                NOT NULL VARCHAR2(255)
 FMD_IN_EVENTS                      NUMBER(11)
 FMD_GLOBAL_TAG                     VARCHAR2(255)
 FMD_PUBLISH_NAME              NOT NULL VARCHAR2(255)
 FMD_LOCATION                  NOT NULL VARCHAR2(255)
 FMD_TMP_LOCATION              NOT NULL VARCHAR2(255)
 FMD_RUNLUMI                        CLOB
 FMD_ADLER32                        VARCHAR2(10)
 FMD_CKSUM                      NUMBER(11)
 FMD_MD5                        VARCHAR2(50)
 FMD_LFN                   NOT NULL VARCHAR2(500)
 FMD_SIZE                  NOT NULL NUMBER(11)
 FMD_TYPE                  NOT NULL VARCHAR2(50)
 FMD_PARENT                     CLOB
 FMD_CREATION_TIME             NOT NULL TIMESTAMP(6)
 FMD_FILESTATE                      VARCHAR2(20)
 FMD_DIRECT_STAGEOUT                    VARCHAR2(1)
 FMD_TMPLFN                     VARCHAR2(500)
belforte commented 3 years ago

for documentation, here's the full log entry with the details of the SQL command:

[18/Dec/2020:11:08:17] SERVER DATABASE ERROR 500/403 Execution error cx_Oracle.DatabaseError 09543d118e5d4a12c5a209bc1b6b7523 [instance: dev] (ORA-01461: can bind a LONG value only for insert into a LONG column); last statement: INSERT INTO <**>filemetadata ( tm_taskname, panda_job_id, job_id, fmd_outdataset, fmd_acq_era, fmd_sw_ver, fmd_in_events, fmd_global_tag, fmd_publish_name, fmd_location, fmd_tmp_location, fmd_runlumi, fmd_adler32, fmd_cksum, fmd_md5, fmd_lfn, fmd_size, fmd_type, fmd_parent, fmd_creation_time, fmd_filestate, fmd_direct_stageout, fmd_tmplfn) VALUES (:taskname, :pandajobid, :jobid, :outdatasetname, :acquisitionera, :appver, :events, :globalTag, :publishdataname, :outlocation, :outtmplocation, :runlumi, :checksumadler32, :checksumcksum, :checksummd5, :outlfn, :outsize, :outtype, :inparentlfns, SYS_EXTRACT_UTC(SYSTIMESTAMP), :filestate, :directstageout, :outtmplfn); binds: ([{'outlfn': '/store/user/ddirmait/GenericTTbar/CRAB3_tutorial_May2015_MC_analysis/201218_095336/0000/output_16.root', 'appver': 'CMSSW_10_2_6', 'publishdataname': 'CRAB3_tutorial_May2015_MC_analysis-37773c17ce2994cf16892d5f04945e41', 'taskname': '201218_095336:ddirmait_crab_20201218_105322', 'outtmplocation': 'T3_US_FNALLPC', 'directstageout': 'T', 'outsize': '20093003', 'outlocation': 'T2_CH_CERN', 'acquisitionera': 'null', 'events': '17000', 'globalTag': 'None', 'checksumcksum': '78925842', 'runlumi': "{'1': {'670757': '50', '670756': '50', '670755': '50', '670776': '50', '670753': '50', '670752': '50', '670750': '50', '670454': '50', '670455': '50', '670456': '50', '670457': '50', '670450': '50', '670451': '50', '670452': '50', '670758': '50', '672034': '50', '672035': '50', '670809': '50', '670808': '50', '670601': '50', '672307': '50', '672032': '50', '672033': '50', '671974': '50', '671221': '50', '671730': '50', '671223': '50', '670608': '50', '670459': '50', '670595': '50', '672028': '50', '670591': '50', '670184': '50', '672267': '50', '670599': '50', '670598': '50', '670190': '50', '672388': '50', '672389': '50', '671871': '50', '670194': '50', '670197': '50', '670353': '50', '670352': '50', '670351': '50', '670219': '50', '672386': '50', '672387': '50', '672384': '50', '672385': '50', '670722': '50', '670617': '50', '670448': '50', '670189': '50', '670727': '50', '670724': '50', '671039': '50', '671401': '50', '671402': '50', '670336': '50', '670447': '50', '670446': '50', '671450': '50', '670332': '50', '672022': '50', '672021': '50', '670633': '50', '672027': '50', '670635': '50', '672025': '50', '672180': '50', '670639': '50', '671344': '50', '672220': '50', '672531': '50', '672186': '50', '670605': '50', '672521': '50', '670772': '50', '670604': '50', '672250': '50', '670356': '50', '670201': '50', '672494': '50', '672468': '50', '670205': '50', '670204': '50', '670209': '50', '672390': '50', '672392': '50', '672553': '50', '672552': '50', '672551': '50', '670600': '50', '670731': '50', '670730': '50', '671710': '50', '672192': '50', '672191': '50', '672190': '50', '670739': '50', '671557': '50', '670602': '50', '671918': '50', '672199': '50', '672584': '50', '670382': '50', '670383': '50', '670384': '50', '672242': '50', '670386': '50', '670387': '50', '670388': '50', '670389': '50', '670761': '50', '670432': '50', '671739': '50', '670621': '50', '672640': '50', '672645': '50', '670363': '50', '672580': '50', '671704': '50', '672058': '50', '671911': '50', '670585': '50', '671997': '50', '672481': '50', '672480': '50', '672039': '50', '672523': '50', '670613': '50', '670981': '50', '671886': '50', '670231': '50', '671851': '50', '672525': '50', '671703': '50', '671700': '50', '671706': '50', '670545': '50', '672212': '50', '671258': '50', '670365': '50', '671318': '50', '670157': '50', '670151': '50', '671859': '50', '670399': '50', '670398': '50', '670397': '50', '670653': '50', '670427': '50', '672239': '50', '670421': '50', '670423': '50', '670616': '50', '671909': '50', '669465': '50', '670749': '50', '670851': '50', '671907': '50', '670124': '50', '671930': '50', '672634': '50', '670223': '50', '672530': '50', '671820': '50', '672550': '50', '670227': '50', '670226': '50', '670225': '50', '671793': '50', '671714': '50', '670587': '50', '671963': '50', '670152': '50', '670714': '50', '670418': '50', '670419': '50', '672588': '50', '670410': '50', '671676': '50', '670413': '50', '670411': '50', '672583': '50', '672625': '50', '671142': '50', '671712': '50', '672576': '50', '670213': '50', '670638': '50', '672172': '50', '670210': '50', '672177': '50', '671839': '50', '672383': '50', '672543': '50', '672541': '50', '670311': '50', '670195': '50', '671833': '50', '670619': '50', '672585': '50', '671834': '50', '671837': '50', '670426': '50', '671372': '50', '670193': '50', '670403': '50', '671165': '50', '670400': '50', '672197': '50', '670764': '50', '670409': '50', '670408': '50', '671927': '50', '671925': '50', '671924': '50', '672218': '50', '672457': '50', '671285': '50', '670198': '50', '670766': '50', '672215': '50', '670175': '50', '672217': '50', '670762': '50', '670847': '50', '670891': '50', '671807': '50', '672516': '50', '672515': '50', '671803': '50', '670765': '50', '670425': '50', '671114': '50', '671928': '50', '672060': '50', '672214': '50', '670360': '50', '672613': '50', '670366': '50', '670367': '50', '670364': '50', '671904': '50', '671735': '50', '670774': '50', '670084': '50', '670385': '50', '671408': '50', '672001': '50', '670578': '50', '672202': '50', '671954': '50', '671196': '50', '670439': '50', '670576': '50', '670574': '50', '671758': '50', '672209': '50', '670130': '50', '671755': '50', '671756': '50', '671955': '50', '670775': '50', '671376': '50', '672201': '50', '670583': '50', '670771': '50', '670777': '50', '672204': '50', '671812': '50', '672200': '50', '671280': '50', '671897': '50', '671375': '50', '672519': '50', '670770': '50', '670371': '50', '670370': '50', '670373': '50', '670372': '50', '670171': '50', '670374': '50', '670377': '50', '670379': '50', '670378': '50', '672605': '50', '672604': '50', '670224': '50', '670440': '50', '670465': '50', '671786': '50', '670467': '50', '670466': '50', '670468': '50', '671313': '50', '670181': '50', '671941': '50', '671940': '50', '670614': '50', '670615': '50', '672478': '50', '672479': '50', '670610': '50', '671748': '50', '670462': '50', '670742': '50', '670743': '50', '670744': '50', '670745': '50', '671741': '50', '670747': '50', '672009': '50', '672008': '50', '670584': '50', '670220': '50', '671216': '50', '671290': '50', '670626': '50', '672000': '50', '672003': '50', '672533': '50', '671299': '50', '670632': '50', '670588': '50', '671988': '50', '671866': '50', '670180': '50', '671865': '50', '670186': '50', '671697': '50', '670344': '50', '670634': '50', '670188': '50', '670347': '50', '670636': '50', '670342': '50', '672219': '50'}}", 'outtype': 'EDM', 'checksummd5': 'asda', 'inparentlfns': "['/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/A4D9CB91-1FAE-E111-8BBC-003048F17A72.root', '/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/A8A81A79-48AE-E111-88A2-0019B9F72D71.root', '/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/A8AB8BFE-44AE-E111-B5FD-001D09F25479.root', '/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/AC1DC2BA-4AAE-E111-8DA5-5404A63886EB.root', '/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/B0D66DAE-07AE-E111-9D19-5404A63886D4.root', '/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/B0EA9106-0EAE-E111-9E5D-003048D2BF1C.root', '/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/B23FC457-47AE-E111-8A5F-001D09F24D67.root', '/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/B4C1D253-04AE-E111-AC5F-BCAEC5329716.root', '/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/B82190B9-09AE-E111-B614-002481E0D7D8.root', '/store/mc/HC/GenericTTbar/GEN-SIM-RECO/CMSSW_5_3_1_START53_V5-v1/0011/BA1FAB4B-11AE-E111-957B-003048D3C932.root']", 'filestate': 'None', 'pandajobid': '0', 'jobid': '16', 'outtmplfn': '/store/user/ddirmait/GenericTTbar/CRAB3_tutorial_May2015_MC_analysis/201218_095336/0000/output_16.root', 'checksumadler32': 'd8b36bad', 'outdatasetname': '/GenericTTbar/ddirmait-CRAB3_tutorial_May2015_MC_analysis-37773c17ce2994cf16892d5f04945e41/USER'}],), {}; offset: 12

belforte commented 3 years ago

hopefully Kate will be able to replay it, replicate the problem, and advice

belforte commented 3 years ago

The original problem can be reproduced very easily on cmsweb-test2 by adding this line to crabConfig.py

config.Data.lumiMask = 'https://cms-service-dqm.web.cern.ch/cms-service-dqm/CAF/certification/Collisions18/13TeV/ReReco/Cert_314472-325175_13TeV_17SeptEarlyReReco2018ABC_PromptEraD_Collisions18_JSON.txt'

as the user who reported the problem pointed out, that lumiMask list is 14k chars

belforte@lxplus756/ora> curl -s https://cms-service-dqm.web.cern.ch/cms-service-dqm/CAF/certification/Collisions18/13TeV/ReReco/Cert_314472-325175_13TeV_17SeptEarlyReReco2018ABC_PromptEraD_Collisions18_JSON.txt|wc
      1    1998   13968
belforte@lxplus756/ora>

and submission attempt immediately fails with:

[18/Dec/2020:16:40:49] SERVER DATABASE ERROR 500/403 Execution error cx_Oracle.DatabaseError e31dc60d690190da08887460322ab87e [instance: dev] (ORA-01461: can bind a LONG value only for insert into a LONG column); last statement: INSERT INTO <**>tasks ( tm_taskname, tm_activity, panda_jobset_id, tm_task_status, tm_task_command, tm_start_time, tm_task_failure, tm_job_sw, tm_job_arch, tm_input_dataset, tm_primary_dataset, tm_nonvalid_input_dataset, tm_use_parent, tm_secondary_input_dataset, tm_site_whitelist, tm_site_blacklist, tm_split_algo, tm_split_args, tm_totalunits, tm_user_sandbox, tm_debug_files, tm_cache_url, tm_username, tm_user_dn, tm_user_vo, tm_user_role, tm_user_group, tm_publish_name, tm_publish_groupname, tm_asyncdest, tm_dbs_url, tm_publish_dbs_url, tm_publication, tm_outfiles, tm_tfile_outfiles, tm_edm_outfiles, tm_job_type, tm_generator, tm_arguments, panda_resubmitted_jobs, tm_save_logs, tm_user_infiles, tm_maxjobruntime, tm_numcores, tm_maxmemory, tm_priority, tm_scriptexe, tm_scriptargs, tm_extrajdl, tm_asourl, tm_asodb, tm_events_per_lumi, tm_collector, tm_schedd, tm_dry_run, tm_user_files, tm_transfer_outputs, tm_output_lfn, tm_ignore_locality, tm_fail_limit, tm_one_event_mode, tm_submitter_ip_addr, tm_ignore_global_blacklist) VALUES (:task_name, :task_activity, :jobset_id, upper(:task_status), upper(:task_command), SYS_EXTRACT_UTC(SYSTIMESTAMP), :task_failure, :job_sw, :job_arch, :input_dataset, :primary_dataset, :nonvalid_data, :use_parent, :secondary_dataset, :site_whitelist, :site_blacklist, :split_algo, :split_args, :total_units, :user_sandbox, :debug_files, :cache_url, :username, :user_dn, :user_vo, :user_role, :user_group, :publish_name, :publish_groupname, :asyncdest, :dbs_url, :publish_dbs_url, :publication, :outfiles, :tfile_outfiles, :edm_outfiles, :job_type, :generator, :arguments, :resubmitted_jobs, :save_logs, :user_infiles, :maxjobruntime, :numcores, :maxmemory, :priority, :scriptexe, :scriptargs, :extrajdl, :asourl, :asodb, :events_per_lumi, :collector, :schedd_name, :dry_run, :user_files, :transfer_outputs, :output_lfn, :ignore_locality, :fail_limit, :one_event_mode, :submitter_ip_addr, :ignore_global_blacklist); binds: ([{'maxjobruntime': 60, 'submitter_ip_addr': '188.185.124.220', 'extrajdl': '[]', 'events_per_lumi': None, 'user_dn': u'/DC=org/DC=terena/DC=tcs/C=IT/O=Istituto Nazionale di Fisica Nucleare/CN=Stefano Belforte belforte@infn.it', 'user_infiles': '[]', 'user_files': '[]', 'job_type': 'Analysis', 'ignore_locality': 'F', 'scriptexe': None, 'job_sw': 'CMSSW_10_6_12', 'debug_files': '3373441ab1260df68c69bd1dd84b5892a252ec428e8ac5693037b271f142decc.tar.gz', 'asyncdest': 'T2_CH_CERN', 'task_command': 'SUBMIT', 'collector': u'cmsgwms-collector-global.cern.ch:9620,cmsgwms-collector-global.fnal.gov', 'task_failure': '', 'publish_name': 'Stefano-Test-2026-00000000000000000000000000000000', 'numcores': 1, 'publication': 'T', 'generator': 'pythia', 'maxmemory': 2000, 'publish_dbs_url': 'https://cmsweb.cern.ch/dbs/prod/phys03/DBSWriter', 'priority': 10, 'dbs_url': 'https://cmsweb.cern.ch/dbs/prod/global/DBSReader', 'arguments': '{}', 'resubmitted_jobs': '[]', 'schedd_name': 'crab3@vocms0199.cern.ch', 'primary_dataset': 'GenericTTbar', 'transfer_outputs': 'T', 'username': 'belforte', 'fail_limit': None, 'site_blacklist': '[]', 'secondary_dataset': None, 'ignore_global_blacklist': 'F', 'scriptargs': '[]', 'asodb': u'filetransfers', 'tfile_outfiles': '[]', 'one_event_mode': 'F', 'user_role': None, 'input_dataset': '/GenericTTbar/HC-CMSSW_9_2_6_91X_mcRun1_realistic_v2-v2/AODSIM', 'total_units': 10, 'split_args': "{'splitOnRun': False, 'halt_job_on_file_boundaries': False, 'lumis': ['1,88,91,92', '1,172', .......

where I truncated the lumis list

But same configuration can be submitted w/o problems to preprod which used the older cx_Oracle:

rabserver-20201218-crabserver-76dc4c69f6-jgbvv.log:[18/Dec/2020:16:49:05] RESTSQL:DFJKQMAeclVj executemany: ([{'maxjobruntime': 60, 'submitter_ip_addr': '188.185.124.220', 'extrajdl': '[]', 'events_per_lumi': None, 'user_dn': u'/DC=org/DC=terena/DC=tcs/C=IT/O=Istituto Nazionale di Fisica Nucleare/CN=Stefano Belforte belforte@infn.it', 'user_infiles': '[]', 'user_files': '[]', 'job_type': 'Analysis', 'ignore_locality': 'F', 'scriptexe': None, 'job_sw': 'CMSSW_10_6_12', 'debug_files': 'b65ef183aea01baf9ff682918e2df5b50cbdc50d16210c2a4c4b0fbe8b0cb4ed.tar.gz', 'asyncdest': 'T2_CH_CERN', 'task_command': 'SUBMIT', 'collector': u'cmsgwms-collector-global.cern.ch:9620,cmsgwms-collector-global.fnal.gov', 'task_failure': '', 'publish_name': 'Stefano-Test-2026-00000000000000000000000000000000', 'numcores': 1, 'publication': 'T', 'generator': 'pythia', 'maxmemory': 2000, 'publish_dbs_url': 'https://cmsweb.cern.ch/dbs/prod/phys03/DBSWriter', 'priority': 10, 'dbs_url': 'https://cmsweb.cern.ch/dbs/prod/global/DBSReader', 'arguments': '{}', 'resubmitted_jobs': '[]', 'schedd_name': 'crab3@vocms0199.cern.ch', 'primary_dataset': 'GenericTTbar', 'transfer_outputs': 'T', 'username': 'belforte', 'fail_limit': None, 'site_blacklist': '[]', 'secondary_dataset': None, 'ignore_global_blacklist': 'F', 'scriptargs': '[]', 'asodb': u'filetransfers', 'tfile_outfiles': '[]', 'one_event_mode': 'F', 'user_role': None, 'input_dataset': '/GenericTTbar/HC-CMSSW_9_2_6_91X_mcRun1_realistic_v2-v2/AODSIM', 'total_units': 10, 'split_args': "{'splitOnRun': False, 'halt_job_on_file_boundaries': False, 'lumis': ['1,88,91,92', '1,172', '32,261', '4,58', '1,244', '1,633', '23,118,122,1354', '37,654', '44,732,736,770,780,831', '40,619', '1,35,37,47,49,67,69,80,82,90', '10,61,67,750', '28,920,924,942,954,1748', '42,843', '1,653,672,709', '1,24', '13,100', '1,345', '1,1122', '55,171', '22,97', '1,26', '1,279', '46,87', '1,4', '1,92', '1,184', '1,40,47,390,395,565,567,594', '1,1033', '1,58', '1,110', '24,1127,1180,1186', '10,654', '38,113', '1,545', '1,61', '1,700', '35,359,374,385,400,1123', '33,50,56,626', '34,92', '37,309', '39,332', '29,33,40,156,158,161', '1,198,201,305', '1,72', '1,716,718,922', '41,621', '1,344', '33,1154', '39,240,262,914', '1,71', '42,405', '1,321,323,567', '1,935', '1,23,194,206', '1,4', '37,407', '1,210', '1,48', '1,64', '1,777,779,1562', '1,770', '38,81', '1,1091,1093,1100,1207,2077', '33,1197', '1,10', '1,498', '1,403', '25,466', '1,264', '1,1008', '1,283', '38,626', '1,1224', '1,325', '36,121', '22,124,126,131,133,135,137,137,139,142,144,145,147,147,149,159,161,174,176,178,180,189,191,197,199,208,210,223', '1,208,210,212,214,225,227,242,244,269,271,319,332,392,394,395,397,402,404,404,406,410,412,412,414,418,420,428,430,450', '1,39,41,49', '19,19,21,40', '1,29', '1,70', '1,708,714,1213', '36,71', '1,1454', '17,444', '1,476', '1,70,76,333', '44,205,207,921,923,1364', '20,703,742,1945', '17,526', '49,241', '1,338', '1,981', '1,197', '46,346,388,397', '1,479', '1,388', '33,45', '1,181', '1,192', '1,311', '1,91,100,144', '1,182', '1,15', '1,751', '1,64', '11,1609', '51,1920,1922,2199', '34,38,40,644', '1,164,171,401', '1,156', '40,188', '33,503', '44,254', '1,14', '1,623', '41,66', '43,177,213,222,257,852', '1,1003', '47,63,65,1424', '36,175', '1,375', '43,508', '34,824', '1,330', '1,283,347,760', '44,182', '1,326,333,411,413,1827', '66,107', '1,163', '1,418', '58,128', '1,58', '39,46', '1,1116,1119,1900', '1,1397', '1,68,71,309', '33,89,105,115', '1,23', '1,448,467,514,519,545', '1,844', '41,1487', '43,334', '40,2045', '29,829', '1,1390', '45,139', '1,621', '1,1304', '35,1256', '1,858', '83,402', '38,682', '1,33', '54,123', '16,287', '1,320', '1,161', '1,615', '52,92', '48,2240', '40,690', '1,37', '1,148', '35,559,562,734', '1,287,290,683', '138,346', '1,78', '38,103', '1,317', '36,1459', '1,282', '1,259', '41,3168', '17,206', '31,1509', '51,310', '1,167', '1,225', '1,87', '36,294', '46,90', '47,482,490,490', '1,312', '44,1966', '41,388', '1,167', '49,51', '1,53', '1,492', '1,4', '1,40,47,262', '1,225', '1,40,43,53', '1,7', '1,983', '1,59', '1,56', '1,32', '1,416', '43,298', '1,50', '38,205', '46,882', '1,264', '1,955', '52,192', '1,34,36,341', '1,330', '1,302', '1,99', '17,292', '1,410', '1,113', '1,204', '43,663', '1,30', '1,737', '1,105', '1,42', '1,49', '1,21', '1,64', '1,200', '1,920', '35,901', '1,599', '49,531', '39,242', '51,382', '46,1274', '1,7', '1,716', '41,221', '1,523', '1,360', '1,1051', '93,357', '1,471', '1,205', '41,369', '1,125', '1,565', '1,159', '1,272', '1,230', '1,40', '49,321', '1,26', '2,25', '1,1926', '1,178', '40,214', '1,831', '1,407', '1,93', '44,142', '26,214', '1,380', '39,188', '1,61', '1,162', '1,831', '1,85', '1,342', '1,213', '1,35,190,201', '58,1179', '1,302,304,326,328,340,368,759', '39,225,232,639', '1,715', '1,313', '45,214', '1,47', '1,395', '1,819', '1,493', '33,70', '1,741', '1,798', '35,1424,1426,1476,1478,1553,1558,1576,1578,1588,1591,1743', '1,8', '1,10', '1,141,143,143,145,510,512,552,554,691,693,923', '38,74,77,214,216,232,234,247,249,321,323,365,367,455', '5,78', '49,962', '1,934', '1,40', '41,124', '1,59', '1,30', '1,727', '1,4', '48,357', '1,62', '1,307,309,316,318,384,390,394,396,604,606,616,619,646,649,690,693,754', '1,24,34,41,44,67', '20,2600,2605,2651', '1,10', '1,768', '1,408', '1,127,134,148', '1,1475', '1,365', '31,1283', '1,804', '30,189', '1,47', '1,125', '1,642', '1,710', '43,451,453,1888', '1,149', '50,518,526,2084', '1,57', '1,2,16,54,57,115,117,263', '2,257,259,291', '1,127,129,181,185,189,192,245,248,252,254,373,375,381,386,386,389,392,395,424,426,432,434,448,450,452,454,459,467,586,589,680,682,686,689,903,905,973,975,1448', '1,146', '33,361,363,470,472,473,475,487,489,729', '1,47,49,75,77,121,128,130,146,148,151,155,161,165,168,189', '1,171,175,205,207,238,240,258,260,420,422,520,526,586,588,593,598,602,604,607,613,716,719,721,727,788,794,818,822,824,828,830,834,836,840,841,845,855', '11,14,25,35,39,52,54,79', '1,12,14,52,54,119', '1,12,14,14', '1,12,15,19,30,45', '1,81,83,169,174,176,192,207', '8,150', '1,332,336,338,342,346,351,357,359,360,362,371,374,383,392,412,414,420,422,493,496,499,502,503,505,508,517,518', '6,37,53,56,58,66,69,69,77,180,186,209,212,265,269,274,276,290,293,312,316,410,412,427', '32,352', '1,23', '1,536', '1,690', '1,214', '25,781', '1,389,403,510', '1,407', '1,333', '39,47,50,52,55,68,71,73,77,89,93,95,99,111,114,116,120,132,136,138,141,154,157,159,163,175,178,181,185,197,200,202,207,218,222,356', '1,41,44,132', '54,948', '43,472', '1,208,210,1654', '4,156,164,808', '1,6', '43,232,235,326', '18,47', '1,354', '37,746,748,968,972,1253', '1,866', '45,996,1106,1486', '1,471', '14,22', '1,17', '42,185,201,1805', '32,70', '38,58', '51,724', '39,200,216,393,409,428', '48,871', '48,159', '1,516,530,874', '43,820,823,1783', '39,266', '1,280,282,301,303,331,337,1143', '1,526', '42,1586', '48,101', '1,163', '1,170,267,1205', '1,416', '37,1055', '40,1505', '36,137', '1,779', '45,577', '46,582', '46,501', '59,1166', '60,408', '1,1386', '37,45', '43,294', '1,69,72,72', '1,10', '1,280', '1,601', '41,484,492,1167', '1,249', '1,46', '1,136', '38,172,176,218,223,266', '1,238', '1,64', '1,227', '1,355', '1,77', '42,177,184,498', '1,514,555,734,738,793', '1,33', '1,144', '1,187', '25,561', '1,91,97,1126', '1,248,253,466', '38,151', '1,257', '1,808', '18,346', '1,60', '1,83,88,677,682,813,819,822,826,987', '27,815,818,823,826,826,828,830,833,861,864,964', '38,81,84,171', '1,934', '45,948', '1,68', '46,510', '1,357', '49,1567', '1,77', '31,85', '1,73', '1,202', '1,188', '1,498', '44,819', '1,554', '54,710,712,753', '20,834,837,1385', '1,240', '1,163', '1,149', '1,34', '1,142', '33,236', '23,1681', '39,1440,1442,2176,2178,2342', '1,200,203,204', '1,332', '1,625', '1,193', '63,1139', '1,150', '1,481', '1,328', '1,165', '77,664', '1,1217', '40,230,302,369', '1,96', '1,1347', '1,151,154,517', '62,111,113,175,180,1800', '30,170', '1,425,428,598,606,632,634,1529,1532,2195', '39,917,919,954,956,968,1005,1042,1044,2340', '29,150', '1,368', '1,14', '1,371', '1,105,108,171,173,595', '45,1594', '42,383', '40,96', '1,8', '1,394', '1,254', '1,462,464,485', '1,21', '1,533', '48,266', '1,21', '1,23', '1,692,694,1205', '1,267,269,485'], 'runs': ['315257', '315259', '315264', '315265', '315267', '315270', '315322', '315339', '315357', '315361', '315363', '315366', '315420', '315488', '315489', '315490', '315506', '315510', '315512', '315543', '315555', '315556', '315557', '315640', '315641', '315642', '315644', '315645', '315646', '315647', '315648', '315689', '315690', '315702', '315703', '315704', '315705', '315713', '315721', '315741', '315764', '315770', '315784', '315785', '315786', '315790', '315800', '315801', '315840', '315973', '315974', '316058', '316059', '316060', '316061', '316062', '316082', '316110', '316111', '316113', '316114', '316153', '316186', '316187', '316199', '316200', '316201', '316202', '316216', '316217', '316218', '316219', '316239', '316240', '316241', '316271', '316361', '316362', '316363', '316377', '316378', '316379', '316380', '316455', '316457', '316469', '316470', '316472', '316505', '316569', '316590', '316613', '316615', '316666', '316667', '316700', '316701', '316702', '316715', '316716', '316717', '316718', '316719', '316720', '316721', '316722', '316723', '316758', '316766', '316876', '316877', '316879', '316928', '316985', '316993', '316994', '316995', '317080', '317087', '317089', '317182', '317212', '317213', '317279', '317291', '317292', '317297', '317319', '317320', '317338', '317339', '317340', '317382', '317383', '317391', '317392', '317435', '317438', '317475', '317478', '317484', '317488', '317527', '317591', '317626', '317640', '317641', '317648', '317649', '317650', '317661', '317663', '317683', '317696', '318733', '318828', '318872', '318874', '318876', '318877', '319077', '319337', '319347', '319348', '319349', '319449', '319450', '319456', '319459', '319486', '319503', '319524', '319526', '319528', '319579', '319625', '319639', '319656', '319657', '319658', '319659', '319678', '319687', '319697', '319698', '319756', '319840', '319841', '319847', '319848', '319849', '319851', '319853', '319854', '319908', '319909', '319910', '319912', '319913', '319914', '319915', '319941', '319942', '319950', '319991', '319992', '319993', '320002', '320006', '320010', '320011', '320012', '320023', '320024', '320025', '320026', '320038', '320039', '320040', '320059', '320060', '320061', '320062', '320063', '320064', '320065', '320673', '320674', '320688', '320712', '320757', '320804', '320807', '320809', '320821', '320822', '320823', '320824', '320838', '320840', '320841', '320853', '320854', '320855', '320856', '320857', '320858', '320859', '320887', '320888', '320916', '320917', '320920', '320933', '320934', '320936', '320941', '320980', '320995', '320996', '321004', '321005', '321006', '321007', '321009', '321010', '321011', '321012', '321051', '321055', '321067', '321068', '321069', '321119', '321121', '321122', '321124', '321126', '321134', '321138', '321140', '321149', '321165', '321166', '321167', '321177', '321178', '321218', '321219', '321221', '321230', '321231', '321232', '321233', '321262', '321283', '321294', '321295', '321296', '321305', '321311', '321312', '321313', '321393', '321396', '321397', '321414', '321415', '321431', '321432', '321433', '321434', '321436', '321457', '321461', '321475', '321710', '321712', '321730', '321732', '321735', '321755', '321758', '321760', '321773', '321774', '321775', '321776', '321777', '321778', '321780', '321781', '321813', '321815', '321817', '321818', '321820', '321831', '321832', '321833', '321834', '321879', '321880', '321887', '321908', '321909', '321917', '321919', '321933', '321960', '321961', '321973', '321975', '321988', '321990', '322013', '322014', '322022', '322040', '322057', '322068', '322079', '322106', '322113', '322118', '322179', '322201', '322204', '322222', '322252', '322317', '322319', '322322', '322324', '322332', '322348', '322355', '322356', '322381', '322407', '322430', '322431', '322480', '322492', '322510', '322599', '322602', '322603', '322605', '322617', '322625', '322633', '323414', '323423', '323470', '323471', '323472', '323473', '323474', '323475', '323487', '323488', '323492', '323493', '323495', '323524', '323525', '323526', '323693', '323696', '323702', '323725', '323726', '323727', '323755', '323775', '323778', '323790', '323794', '323841', '323857', '323940', '323954', '323976', '323978', '323980', '323983', '323997', '324021', '324022', '324077', '324201', '324202', '324205', '324206', '324207', '324209', '324237', '324245', '324293', '324315', '324318', '324420', '324729', '324747', '324764', '324765', '324769', '324772', '324785', '324791', '324835', '324840', '324841', '324846', '324878', '324897', '324970', '324980', '324997', '324998', '324999', '325000', '325001', '325022', '325057', '325097', '325098', '325099', '325100', '325101', '325110', '325117', '325159', '325168', '325169', '325170', '325172'], 'lumis_per_job': 1}", 'user_group': None, 'split_algo': 'LumiBased', 'edm_outfiles': "['kk.root']", 'publish_groupname': 'F', 'cache_url': 'https://cmsweb-k8s-prod.cern.ch/crabcache', 'jobset_id': None, 'nonvalid_data': 'F', 'dry_run': 'F', 'site_whitelist': "['T2_BR_UERJ', 'T2_TR_METU', 'T2_AT_Vienna', 'T2_EE_Estonia', 'T2_UA_KIPT', 'T2_PK_NCP', 'T2_US_Purdue', 'T2_TW_NCHC', 'T2_FI_HIP', 'T2_UK_SGrid_RALPP', 'T2_FR_GRIF_LLR', 'T2_BE_UCL', 'T2_ES_IFCA', 'T2_BE_IIHE', 'T2_UK_SGrid_Bristol', 'T2_FR_IPHC', 'T2_US_Caltech', 'T2_DE_DESY', 'T2_RU_SINP', 'T2_IT_Legnaro', 'T2_CN_Beijing', 'T2_RU_ITEP', 'T2_UK_London_Brunel', 'T2_RU_JINR', 'T2_IT_Pisa', 'T2_US_Vanderbilt', 'T2_GR_Ioannina', 'T2_IN_TIFR', 'T2_CH_CERN_HLT', 'T2_US_Florida', 'T2_CH_CSCS', 'T2_FR_GRIF_IRFU', 'T2_UK_London_IC', 'T2_IT_Bari', 'T2_US_Nebraska', 'T2_IT_Rome', 'T2_FR_CCIN2P3', 'T2_US_UCSD', 'T2_ES_CIEMAT', 'T2_RU_IHEP', 'T2_US_Wisconsin', 'T2_HU_Budapest', 'T2_US_MIT', 'T2_DE_RWTH', 'T2_KR_KISTI', 'T2_RU_INR', 'T2_CH_CERN', 'T2_PT_NCG_Lisbon', 'T2_PL_Swierk', 'T2_BR_SPRACE']", 'task_activity': None, 'user_sandbox': '9ed5cfad865d1b9f6c4b4ef47a18213dec84cf531e9dce8cf78de4a95b8fa1f2.tar.gz', 'output_lfn': '/store/user/belforte/', 'asourl': u'https://cmsweb-testbed.cern.ch/crabserver/preprod', 'user_vo': 'cms', 'task_name': '201218_154905:belforte_crab_20201218_164858', 'outfiles': '[]', 'task_status': 'NEW', 'job_arch': 'slc7_amd64_gcc700', 'use_parent': 0, 'save_logs': 'F'}],) {} belforte@vocms0750/crab-logs>

belforte commented 3 years ago

that list of lumis goes in the TASKS table in the TM_SPLIT_ARGS column which is defined as CLOB

SQL> describe tasks;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TM_TASKNAME                   NOT NULL VARCHAR2(255)
 TM_ACTIVITY                        VARCHAR2(255)
 PANDA_JOBSET_ID                    NUMBER(11)
 TM_TASK_STATUS                NOT NULL VARCHAR2(255)
 TM_TASK_COMMAND                    VARCHAR2(20)
 TM_START_TIME                      TIMESTAMP(6)
 TM_START_INJECTION                 TIMESTAMP(6)
 TM_END_INJECTION                   TIMESTAMP(6)
 TM_TASK_FAILURE                    CLOB
 TM_JOB_SW                 NOT NULL VARCHAR2(255)
 TM_JOB_ARCH                        VARCHAR2(255)
 TM_INPUT_DATASET                   VARCHAR2(500)
 TM_NONVALID_INPUT_DATASET              VARCHAR2(1)
 TM_USE_PARENT                      NUMBER(1)
 TM_SITE_WHITELIST                  VARCHAR2(4000)
 TM_SITE_BLACKLIST                  VARCHAR2(4000)
 TM_SPLIT_ALGO                 NOT NULL VARCHAR2(255)
 TM_SPLIT_ARGS                 NOT NULL CLOB
...

and according to Oracle A CLOB without a specified length is defaulted to two giga characters (2,147,483,647).

Why does then cx_Oracle 5.2 (but not 5.1) complain about (ORA-01461: can bind a LONG value only for insert into a LONG column) ??

If I replace the real lumimask from the user with a short 100char fragment of it, submission to cmsweb-test2 works finely:

belforte@lxplus756/ora> grep lumi crabConfig.py
config.Data.lumiMask = 'https://cms-service-dqm.web.cern.ch/cms-service-dqm/CAF/certification/Collisions18/13TeV/ReReco/Cert_314472-325175_13TeV_17SeptEarlyReReco2018ABC_PromptEraD_Collisions18_JSON.txt'
config.Data.lumiMask = '100char.txt'
belforte@lxplus756/ora> cat 100char.txt 
{"315257": [[1, 88], [91, 92]], "315259": [[1, 172]], "315264": [[32, 261]], "315265": [[4, 58]]}
belforte@lxplus756/ora> wc 100char.txt 
 1 14 98 100char.txt
belforte@lxplus756/ora>

[18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm ENTER cmsweb_analysis_dev@devdb11 CRABInterface.RESTBaseAPI.RESTBaseAPI (PUT dev workflow) inuse=0 idle=1 [18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm (previously alDtEsNHqPoY) connected, client: 11.2.0.4.0, server: 11.2.0.4.0, stmtcache: 50 [18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm ping [18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm check [select sysdate from dual] [18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm connection established [18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm prepare [INSERT INTO tasks ( tm_taskname, tm_activity, panda_jobset_id, tm_task_status, tm_task_command, tm_start_time, tm_task_failure, tm_job_sw, tm_job_arch, tm_input_dataset, tm_primary_dataset, tm_nonvalid_input_dataset, tm_use_parent, tm_secondary_input_dataset, tm_site_whitelist, tm_site_blacklist, tm_split_algo, tm_split_args, tm_totalunits, tm_user_sandbox, tm_debug_files, tm_cache_url, tm_username, tm_user_dn, tm_user_vo, tm_user_role, tm_user_group, tm_publish_name, tm_publish_groupname, tm_asyncdest, tm_dbs_url, tm_publish_dbs_url, tm_publication, tm_outfiles, tm_tfile_outfiles, tm_edm_outfiles, tm_job_type, tm_generator, tm_arguments, panda_resubmitted_jobs, tm_save_logs, tm_user_infiles, tm_maxjobruntime, tm_numcores, tm_maxmemory, tm_priority, tm_scriptexe, tm_scriptargs, tm_extrajdl, tm_asourl, tm_asodb, tm_events_per_lumi, tm_collector, tm_schedd, tm_dry_run, tm_user_files, tm_transfer_outputs, tm_output_lfn, tm_ignore_locality, tm_fail_limit, tm_one_event_mode, tm_submitter_ip_addr, tm_ignore_global_blacklist) VALUES (:task_name, :task_activity, :jobset_id, upper(:task_status), upper(:task_command), SYS_EXTRACT_UTC(SYSTIMESTAMP), :task_failure, :job_sw, :job_arch, :input_dataset, :primary_dataset, :nonvalid_data, :use_parent, :secondary_dataset, :site_whitelist, :site_blacklist, :split_algo, :split_args, :total_units, :user_sandbox, :debug_files, :cache_url, :username, :user_dn, :user_vo, :user_role, :user_group, :publish_name, :publish_groupname, :asyncdest, :dbs_url, :publish_dbs_url, :publication, :outfiles, :tfile_outfiles, :edm_outfiles, :job_type, :generator, :arguments, :resubmitted_jobs, :save_logs, :user_infiles, :maxjobruntime, :numcores, :maxmemory, :priority, :scriptexe, :scriptargs, :extrajdl, :asourl, :asodb, :events_per_lumi, :collector, :schedd_name, :dry_run, :user_files, :transfer_outputs, :output_lfn, :ignore_locality, :fail_limit, :one_event_mode, :submitter_ip_addr, :ignore_global_blacklist)] [18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm executemany: ([{'maxjobruntime': 60, 'submitter_ip_addr': '188.185.124.220', 'extrajdl': '[]', 'events_per_lumi': None, 'user_dn': u'/DC=org/DC=terena/DC=tcs/C=IT/O=Istituto Nazionale di Fisica Nucleare/CN=Stefano Belforte belforte@infn.it', 'user_infiles': '[]', 'user_files': '[]', 'job_type': 'Analysis', 'ignore_locality': 'F', 'scriptexe': None, 'job_sw': 'CMSSW_10_6_12', 'debug_files': '45d01a4a0fe41da2e22c814a75d14f6dff587d6d5acf4100fb60a6c6db2dd81d.tar.gz', 'asyncdest': 'T2_CH_CERN', 'task_command': 'SUBMIT', 'collector': u'cmsgwms-collector-global.cern.ch:9620,cmsgwms-collector-global.fnal.gov', 'task_failure': '', 'publish_name': 'Stefano-Test-2026-00000000000000000000000000000000', 'numcores': 1, 'publication': 'T', 'generator': 'pythia', 'maxmemory': 2000, 'publish_dbs_url': 'https://cmsweb.cern.ch/dbs/prod/phys03/DBSWriter', 'priority': 10, 'dbs_url': 'https://cmsweb.cern.ch/dbs/prod/global/DBSReader', 'arguments': '{}', 'resubmitted_jobs': '[]', 'schedd_name': 'crab3@vocms0199.cern.ch', 'primary_dataset': 'GenericTTbar', 'transfer_outputs': 'T', 'username': 'belforte', 'fail_limit': None, 'site_blacklist': '[]', 'secondary_dataset': None, 'ignore_global_blacklist': 'F', 'scriptargs': '[]', 'asodb': u'filetransfers', 'tfile_outfiles': '[]', 'one_event_mode': 'F', 'user_role': None, 'input_dataset': '/GenericTTbar/HC-CMSSW_9_2_6_91X_mcRun1_realistic_v2-v2/AODSIM', 'total_units': 10, 'split_args': "{'splitOnRun': False, 'halt_job_on_file_boundaries': False, 'lumis': ['1,88,91,92', '1,172', '32,261', '4,58'], 'runs': ['315257', '315259', '315264', '315265'], 'lumis_per_job': 1}", 'user_group': None, 'split_algo': 'LumiBased', 'edm_outfiles': "['kk.root']", 'publish_groupname': 'F', 'cache_url': 'https://cmsweb-test2.cern.ch:443/crabcache', 'jobset_id': None, 'nonvalid_data': 'F', 'dry_run': 'F', 'site_whitelist': "['T2_BR_UERJ', 'T2_TR_METU', 'T2_AT_Vienna', 'T2_EE_Estonia', 'T2_UA_KIPT', 'T2_PK_NCP', 'T2_US_Purdue', 'T2_TW_NCHC', 'T2_FI_HIP', 'T2_UK_SGrid_RALPP', 'T2_FR_GRIF_LLR', 'T2_BE_UCL', 'T2_ES_IFCA', 'T2_BE_IIHE', 'T2_UK_SGrid_Bristol', 'T2_FR_IPHC', 'T2_US_Caltech', 'T2_DE_DESY', 'T2_RU_SINP', 'T2_IT_Legnaro', 'T2_CN_Beijing', 'T2_RU_ITEP', 'T2_UK_London_Brunel', 'T2_RU_JINR', 'T2_IT_Pisa', 'T2_US_Vanderbilt', 'T2_GR_Ioannina', 'T2_IN_TIFR', 'T2_CH_CERN_HLT', 'T2_US_Florida', 'T2_CH_CSCS', 'T2_FR_GRIF_IRFU', 'T2_UK_London_IC', 'T2_IT_Bari', 'T2_US_Nebraska', 'T2_IT_Rome', 'T2_FR_CCIN2P3', 'T2_US_UCSD', 'T2_ES_CIEMAT', 'T2_RU_IHEP', 'T2_US_Wisconsin', 'T2_HU_Budapest', 'T2_US_MIT', 'T2_DE_RWTH', 'T2_KR_KISTI', 'T2_RU_INR', 'T2_CH_CERN', 'T2_PT_NCG_Lisbon', 'T2_PL_Swierk', 'T2_BR_SPRACE']", 'task_activity': None, 'user_sandbox': '9ed5cfad865d1b9f6c4b4ef47a18213dec84cf531e9dce8cf78de4a95b8fa1f2.tar.gz', 'output_lfn': '/store/user/belforte/', 'asourl': u'https://cmsweb-test.cern.ch/crabserver/preprod', 'user_vo': 'cms', 'task_name': '201218_161335:belforte_crab_20201218_171329', 'outfiles': '[]', 'task_status': 'NEW', 'job_arch': 'slc7_amd64_gcc700', 'use_parent': 0, 'save_logs': 'F'}],) {} [18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm commit [18/Dec/2020:17:13:35] crabserver-696b9fdfc6-ktbs2 188.185.124.220 "PUT /crabserver/dev/workflow HTTP/1.1" 200 OK [data: 8975 in 80 out 1028373 us ] [auth: OK "/DC=org/DC=terena/DC=tcs/C=IT/O=Istituto Nazionale di Fisica Nucleare/CN=Stefano Belforte belforte@infn.it" "" ] [ref: "" "CRABClient/development" ] [18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm release with rollback [18/Dec/2020:17:13:35] RESTSQL:beGKkaAHwVWm RELEASED cmsweb_analysis_dev@devdb11 timeout=300 inuse=0 idle=1

sharmaprajesh commented 3 years ago

I think, problem and solution found. I am using my setup to understand the problem.

python version: [prsharma@testmac2 python]$ python --version Python 2.7.5

cx_Oracle version:

import cx_Oracle cx_Oracle.version '5.2.1'

I created a table in cmsweb_analysis_preprod@int2r: SQL> create table prajeshtest(data CLOB); Table created.

I created following python code (FIX_ME is the database password): `import cx_Oracle

con = cx_Oracle.connect('cmsweb_analysis_preprod', 'FIX_ME', 'int2r') cur = con.cursor()

data=["{'1': {'670757': '50', '670756': '50', '670755': '50', '670776': '50', '670753': '50', '670752': '50', '670750': '50', '670454': '50', '670455': '50', '670456': '50', '670457': '50', '670450': '50', '670451': '50', '670452': '50', '670758': '50', '672034': '50', '672035': '50', '670809': '50', '670808': '50', '670601': '50', '672307': '50', '672032': '50', '672033': '50', '671974': '50', '671221': '50', '671730': '50', '671223': '50', '670608': '50', '670459': '50', '670595': '50', '672028': '50', '670591': '50', '670184': '50', '672267': '50', '670599': '50', '670598': '50', '670190': '50', '672388': '50', '672389': '50', '671871': '50', '670194': '50', '670197': '50', '670353': '50', '670352': '50', '670351': '50', '670219': '50', '672386': '50', '672387': '50', '672384': '50', '672385': '50', '670722': '50', '670617': '50', '670448': '50', '670189': '50', '670727': '50', '670724': '50', '671039': '50', '671401': '50', '671402': '50', '670336': '50', '670447': '50', '670446': '50', '671450': '50', '670332': '50', '672022': '50', '672021': '50', '670633': '50', '672027': '50', '670635': '50', '672025': '50', '672180': '50', '670639': '50', '671344': '50', '672220': '50', '672531': '50', '672186': '50', '670605': '50', '672521': '50', '670772': '50', '670604': '50', '672250': '50', '670356': '50', '670201': '50', '672494': '50', '672468': '50', '670205': '50', '670204': '50', '670209': '50', '672390': '50', '672392': '50', '672553': '50', '672552': '50', '672551': '50', '670600': '50', '670731': '50', '670730': '50', '671710': '50', '672192': '50', '672191': '50', '672190': '50', '670739': '50', '671557': '50', '670602': '50', '671918': '50', '672199': '50', '672584': '50', '670382': '50', '670383': '50', '670384': '50', '672242': '50', '670386': '50', '670387': '50', '670388': '50', '670389': '50', '670761': '50', '670432': '50', '671739': '50', '670621': '50', '672640': '50', '672645': '50', '670363': '50', '672580': '50', '671704': '50', '672058': '50', '671911': '50', '670585': '50', '671997': '50', '672481': '50', '672480': '50', '672039': '50', '672523': '50', '670613': '50', '670981': '50', '671886': '50', '670231': '50', '671851': '50', '672525': '50', '671703': '50', '671700': '50', '671706': '50', '670545': '50', '672212': '50', '671258': '50', '670365': '50', '671318': '50', '670157': '50', '670151': '50', '671859': '50', '670399': '50', '670398': '50', '670397': '50', '670653': '50', '670427': '50', '672239': '50', '670421': '50', '670423': '50', '670616': '50', '671909': '50', '669465': '50', '670749': '50', '670851': '50', '671907': '50', '670124': '50', '671930': '50', '672634': '50', '670223': '50', '672530': '50', '671820': '50', '672550': '50', '670227': '50', '670226': '50', '670225': '50', '671793': '50', '671714': '50', '670587': '50', '671963': '50', '670152': '50', '670714': '50', '670418': '50', '670419': '50', '672588': '50', '670410': '50', '671676': '50', '670413': '50', '670411': '50', '672583': '50', '672625': '50', '671142': '50', '671712': '50', '672576': '50', '670213': '50', '670638': '50', '672172': '50', '670210': '50', '672177': '50', '671839': '50', '672383': '50', '672543': '50', '672541': '50', '670311': '50', '670195': '50', '671833': '50', '670619': '50', '672585': '50', '671834': '50', '671837': '50', '670426': '50', '671372': '50', '670193': '50', '670403': '50', '671165': '50', '670400': '50', '672197': '50', '670764': '50', '670409': '50', '670408': '50', '671927': '50', '671925': '50', '671924': '50', '672218': '50', '672457': '50', '671285': '50', '670198': '50', '670766': '50', '672215': '50', '670175': '50', '672217': '50', '670762': '50', '670847': '50', '670891': '50', '671807': '50', '672516': '50', '672515': '50', '671803': '50', '670765': '50', '670425': '50', '671114': '50', '671928': '50', '672060': '50', '672214': '50', '670360': '50', '672613': '50', '670366': '50', '670367': '50', '670364': '50', '671904': '50', '671735': '50', '670774': '50', '670084': '50', '670385': '50', '671408': '50', '672001': '50', '670578': '50', '672202': '50', '671954': '50', '671196': '50', '670439': '50', '670576': '50', '670574': '50', '671758': '50', '672209': '50', '670130': '50', '671755': '50', '671756': '50', '671955': '50', '670775': '50', '671376': '50', '672201': '50', '670583': '50', '670771': '50', '670777': '50', '672204': '50', '671812': '50', '672200': '50', '671280': '50', '671897': '50', '671375': '50', '672519': '50', '670770': '50', '670371': '50', '670370': '50', '670373': '50', '670372': '50', '670171': '50', '670374': '50', '670377': '50', '670379': '50', '670378': '50', '672605': '50', '672604': '50', '670224': '50', '670440': '50', '670465': '50', '671786': '50', '670467': '50', '670466': '50', '670468': '50', '671313': '50', '670181': '50', '671941': '50', '671940': '50', '670614': '50', '670615': '50', '672478': '50', '672479': '50', '670610': '50', '671748': '50', '670462': '50', '670742': '50', '670743': '50', '670744': '50', '670745': '50', '671741': '50', '670747': '50', '672009': '50', '672008': '50', '670584': '50', '670220': '50', '671216': '50', '671290': '50', '670626': '50', '672000': '50', '672003': '50', '672533': '50', '671299': '50', '670632': '50', '670588': '50', '671988': '50', '671866': '50', '670180': '50', '671865': '50', '670186': '50', '671697': '50', '670344': '50', '670634': '50', '670188': '50', '670347': '50', '670636': '50', '670342': '50', '672219': '50'}}"]

cur.execute("insert into prajeshtest values (:data)", data)

con.commit() con.close() ` after that I execute above code:

[prsharma@testmac2 python]$ python oracle.py Traceback (most recent call last): File "oracle.py", line 9, in cur.execute("insert into prajeshtest values (:data)", data) cx_Oracle.DatabaseError: ORA-01461: can bind a LONG value only for insert into a LONG column

[prsharma@testmac2 python]$

This is the same error as above. Now add following line just before cur.execute statement:

cur.setinputsizes(cx_Oracle.CLOB)

execute again:

[prsharma@testmac2 python]$ python oracle.py [prsharma@testmac2 python]$ NO errors/warning/message. Like Unix :-)

output from table:

SQL> select * from prajeshtest;

DATA

{'1': {'670757': '50', '670756': '50', '670755': '50', '670776': '50', '670753':

@belforte , Is it OK?

belforte commented 3 years ago

Very nice Prajesh. Can you also confirm that such line was not needed with 5.1?Looks great BTW. 

belforte commented 3 years ago

well I can't tell from you last last if the full long list has been saved and retrieved from DB. Note, do not need to past it all here in case. Anyhow the point appears that we need to declare explicitly to Oracle that the input value is a CLOB. HOpefully only for this column. And of course need to find the way to do it in our python where we do not call directly cx_Oracle... but those things can be sorted out, I am sure others went through same issue and can give examples.

sharmaprajesh commented 3 years ago

My code is working with/without following line in 5.1.3 version: cur.setinputsizes(cx_Oracle.CLOB)

belforte commented 3 years ago

well I can't tell from you last line if the full long list has been saved and retrieved from DB. Note, do not need to past it all here in case. Anyhow the point appears that we need to declare explicitly to Oracle that the input value is a CLOB. Hopefully only for this column. And of course need to find the way to do it in our python where we do not call directly cx_Oracle... but those things can be sorted out, I am sure others went through same issue and can give examples.

belforte commented 3 years ago

Reference: CRAB Server code uses SQL from https://github.com/dmwm/CRABServer/blob/master/src/python/Databases/TaskDB/Oracle/Task/Task.py in particular

https://github.com/dmwm/CRABServer/blob/c1e15e9318751a4597703e4c4a238c8a6bdd4efa/src/python/Databases/TaskDB/Oracle/Task/Task.py#L41-L57

belforte commented 3 years ago

I have asked @amaltaro about CLOB usage in other WMCore applications, since WMCore is built and works with cx_oracle.5.2, He pointed to https://github.com/dmwm/WMCore/blob/master/src/python/WMComponent/DBS3Buffer where a CLOB type column is currently used. We should find out what is different in that case and in ours and see if we can make this work for CRABServer as well. A few references: table creation https://github.com/dmwm/WMCore/blob/master/src/python/WMComponent/DBS3Buffer/Oracle/Create.py#L63 insert into table https://github.com/dmwm/WMCore/blob/master/src/python/WMComponent/DBS3Buffer/Oracle/NewAlgo.py#L12 how the insert is called https://github.com/dmwm/WMCore/blob/master/src/python/WMComponent/DBS3Buffer/DBSBufferFile.py#L156 DAOFactory instantiation https://github.com/dmwm/WMCore/blob/master/src/python/WMComponent/DBS3Buffer/DBSBufferFile.py#L51 a standaslone script using DAO Libraries https://github.com/amaltaro/ProductionTools/blob/master/executeDAO.py

First task on our side is to reconstruct the exact path via which our SQL is called. I.e. how https://github.com/dmwm/CRABServer/blob/master/src/python/CRABInterface/RESTUserWorkflow.py talks to Oracle. Relevant lines: https://github.com/dmwm/CRABServer/blob/c1e15e9318751a4597703e4c4a238c8a6bdd4efa/src/python/CRABInterface/RESTUserWorkflow.py#L620 https://github.com/dmwm/CRABServer/blob/c1e15e9318751a4597703e4c4a238c8a6bdd4efa/src/python/CRABInterface/DataWorkflow.py#L106

belforte commented 3 years ago

I believe that a good start will be a simple script which reproduces the problem in a test CRABServer (e.g. in cmsweb-test2) where we can play with the code, add logging etc.

belforte commented 3 years ago

@amaltaro with regard to our conversation in Slack, when I say that CRAB Server uses WMCore REST to talk to Oracle, I refer to this class: https://github.com/dmwm/WMCore/blob/cf734ace796a3d7e1e6a3fabaede0213a1729942/src/python/WMCore/REST/Server.py#L1666 in particular the db INSERT which gives us problems is done via https://github.com/dmwm/WMCore/blob/cf734ace796a3d7e1e6a3fabaede0213a1729942/src/python/WMCore/REST/Server.py#L2165 where we pass as argument an SQL INSERT statement, and a series of name=[values] args: https://github.com/dmwm/CRABServer/blob/c1e15e9318751a4597703e4c4a238c8a6bdd4efa/src/python/CRABInterface/DataWorkflow.py#L188-L191

Altough CRABServer contains a reference to DAOFactory in here https://github.com/dmwm/CRABServer/blob/master/src/python/Databases/Connection.py it looks to me that this is not used.

Is CRABServer the only user of DatabaseRESTApi ? https://github.com/dmwm/WMCore/blob/cf734ace796a3d7e1e6a3fabaede0213a1729942/src/python/WMCore/REST/Server.py#L1666

if yes... how much should we worry ?

belforte commented 3 years ago

in order to facilitate testing, here's a CRABServer container image build with cx-oracle.5.2 and latest CRAB tag

cmssw/crabserver:v3.210110.cx52

https://hub.docker.com/layers/133450501/cmssw/crabserver/v3.210110.cx52/images/sha256-9a581c1e3e7e04461bcc91012b578363ede8cbb0ccb117999f08887b28a7d624?context=explore

I have deployed that on cmsweb-test2

belforte commented 3 years ago

we can run crabserver in debug mode as per https://twiki.cern.ch/twiki/bin/view/CMSPublic/Crab3OperatorDebugging#To_run_the_crabserver_in_debug_m

sharmaprajesh commented 3 years ago

I was testing insertion on CLOB column with above value with different versions of cx_Oracle packages[1] available in python 2.7. I am using same code which I already shared above.

I found that my code is inserting CLOB value without any error in 6.0b1 version without following line of code: cur.setinputsizes(cx_Oracle.CLOB)

@belforte , Can we use this version 6.0b1 in our CRAB code ? If you want I can check next available versions.

[1] cx-Oracle== (from versions: 5.1.3, 5.2, 5.2.1, 5.3, 6.0b1, 6.0b2, 6.0rc1, 6.0rc2, 6.0, 6.0.1, 6.0.2, 6.0.3, 6.1, 6.2, 6.2.post2, 6.2.1, 6.3, 6.3.1, 6.4, 6.4.1, 7.0.0, 7.1.0, 7.1.1, 7.1.2, 7.1.3, 7.2.0, 7.2.1, 7.2.2, 7.2.3, 7.3.0, 8.0.0, 8.1.0)

belforte commented 3 years ago

thanks Prajesh, that's eniticing !!

here are current spec file for building py2-cx-oracle https://github.com/cms-sw/cmsdist/blob/comp_gcc630/py2-cx-oracle51.spec https://github.com/cms-sw/cmsdist/blob/comp_gcc630/py2-cx-oracle.spec

I am nor familiar with that rpm building machinery, maybe @amaltaro or @smuzaffar can comment on whether we could try to build with cx-0racle6l 6.0 or later ? Note that it is important that it woks "from a a certain version onward", not just for 6.0b1 by sort of accident.

I was testing insertion on CLOB column with above value with different versions of cx_Oracle packages[1] available in python 2.7. I am using same code which I already shared above.

I found that my code is inserting CLOB value without any error in 6.0b1 version without following line of code: cur.setinputsizes(cx_Oracle.CLOB)

@belforte , Can we use this version 6.0b1 in our CRAB code ? If you want I can check next available versions.

[1] cx-Oracle== (from versions: 5.1.3, 5.2, 5.2.1, 5.3, 6.0b1, 6.0b2, 6.0rc1, 6.0rc2, 6.0, 6.0.1, 6.0.2, 6.0.3, 6.1, 6.2, 6.2.post2, 6.2.1, 6.3, 6.3.1, 6.4, 6.4.1, 7.0.0, 7.1.0, 7.1.1, 7.1.2, 7.1.3, 7.2.0, 7.2.1, 7.2.2, 7.2.3, 7.3.0, 8.0.0, 8.1.0)

smuzaffar commented 3 years ago

there is no restrictions from rpm build system. You can move to any latest version. By the way, for cmssw , we are using cx-Oracle 7.3.0. You just need to make sure that if you update py2-cx-oracle.spec then make sure all services which require it also work otherwise you can add py2-cx-oracle73.spec too

belforte commented 3 years ago

thanks @smuzaffar we'll give it a try !

sharmaprajesh commented 3 years ago

I checked my code(having CLOB) is also working with 7.3.0 version without any error.

belforte commented 3 years ago

thanks @sharmaprajesh . Let's try to build a CRABServer with that.

belforte commented 3 years ago

@smuzaffar I got an odd error trying to build py2-cx-oracle73 . Can you point me to the file you are using ? I could not find cx-oracle in cmsdist cmssw branch, surely my fault !

smuzaffar commented 3 years ago

cmssw branch auto generates spec files for packages which we download and build with pip. where can I see your py2-cx-oracle73.spec.

belforte commented 3 years ago

I suspect it was due to a work dir., corruption, so deleted it and tried again. It is taking a bit...

belforte commented 3 years ago

anyhow here it is as a trivial modification of existing one:

belforte@vocms055/belforte> cat cmsdist/py2-cx-oracle73.spec 
### RPM external py2-cx-oracle73 7.3.0
## INITENV +PATH PYTHONPATH %i/${PYTHON_LIB_SITE_PACKAGES}
%define downloadn cx_Oracle

Source: https://pypi.python.org/packages/source/c/cx_Oracle/cx_Oracle-%realversion.tar.gz
Patch: py2-cx-oracle-pingbreak

Requires: python oracle oracle-env

%prep
%setup -n %downloadn-%realversion
%patch -p1

cat >> setup.cfg <<- EOF
[build_ext]
include_dirs = $ORACLE_ROOT/include
library_dirs = $ORACLE_ROOT/lib
EOF

%build
python setup.py build

%install
mkdir -p %i/$PYTHON_LIB_SITE_PACKAGES
PYTHONPATH=%i/$PYTHON_LIB_SITE_PACKAGES:$PYTHONPATH \
python setup.py install --prefix=%i
find %i -name '*.egg-info' -exec rm {} \;
belforte@vocms055/belforte>
belforte commented 3 years ago

Nope, same error:

* The action "build-external+py2-cx-oracle73+7.3.0" was not completed successfully because Failed to build py2-cx-oracle73. Log file in /build/belforte/w/BUILD/slc7_amd64_gcc630/external/py2-cx-oracle73/7.3.0/log. Final lines of the log file:
+ patch --no-backup-if-mismatch -p1 --fuzz=0
can't find file to patch at input line 5
Perhaps you used the wrong -p or --strip option?
The text leading up to this was:
--------------------------
|diff --git a/Connection.c b/Connection.c
|index 279c397..f1117d6 100644
|--- a/Connection.c
|+++ b/Connection.c
--------------------------
File to patch: 
Skip this patch? [y] 
Skipping patch.
2 out of 2 hunks ignored
error: Bad exit status from /build/belforte/w/tmp/rpm-tmp.ci54BH (%prep)
smuzaffar commented 3 years ago

do you need py2-cx-oracle-pingbreak patch for this new version? if not then just remove the folloiwng line from your spec.

Patch: py2-cx-oracle-pingbreak
belforte commented 3 years ago

honestly I have no idea what pingbreak is ! easy to try !

belforte commented 3 years ago

gets

* The action "build-external+py2-cx-oracle73+7.3.0" was not completed successfully because Failed to build py2-cx-oracle73. Log file in /build/belforte/w/BUILD/slc7_amd64_gcc630/external/py2-cx-oracle73/7.3.0/log. Final lines of the log file:
warning: Macro %rpmbuild_libdir defined but not used within scope
error: %patch without corresponding "Patch:" tag

remove also %patch -p1 ?

smuzaffar commented 3 years ago

yes

belforte commented 3 years ago

much better: Build successful py2-cx-oracle73.

belforte commented 3 years ago

preliminary indication is that this fixed the problem. A CRABServer container with cx-oracle-7.3.0 is now running on cmsweb-test2.cern.ch K8s cluster and passed an initial "CLOB test". We will test more and then put in preprod.

Many thanks @mmascher and @smuzaffar

belforte commented 3 years ago

There is a deployment issue since this cx-oracle/73 requires a python egg cache. We need to make sure that it points to a place where it can be written. likely a different one in VMs and K8s. I'll make an ad-hoc issue. this is the msg on K8s

ExtractionError: Can't extract file(s) to egg cache

The following error occurred while trying to extract file(s)
to the Python egg cache:

  [Errno 13] Permission denied: '/root/.cache'

The Python egg cache directory is currently set to:

  /root/.cache/Python-Eggs

Perhaps your account does not have write access to this directory?
You can change the cache directory by setting the PYTHON_EGG_CACHE
environment variable to point to an accessible directory.
amaltaro commented 3 years ago

For the record, it's exactly the same issue with the same spec, which I'm trying to resolve in this PR: https://github.com/cms-sw/cmsdist/pull/6440

I'll try to get it fixed today... But if Shahzad has already encountered this issue and know how to fix it, I'd be even happier to hear :-D

smuzaffar commented 3 years ago

no I never encounter this issue but setting PYTHON_EGG_CACHE might help

amaltaro commented 3 years ago

I've tried the following %install section in the spec file:

%install
# to make sure the cache is not created under the user home directory (afs!)
export PYTHON_EGG_CACHE=%i/$PYTHON_LIB_SITE_PACKAGES
mkdir -p %i/$PYTHON_LIB_SITE_PACKAGES
PYTHONPATH=%i/$PYTHON_LIB_SITE_PACKAGES:$PYTHONPATH \
python setup.py install --prefix=%i
find %i -name '*.egg-info' -exec rm {} \;

but it did not fix the issue. Is it misplaced or ill defined?

smuzaffar commented 3 years ago

PYTHON_EGG_CACHE is runtime variable, adding it here is not going to work. It should be set a writable directory where at runtime where python can extract/unzipped the egg packages.

smuzaffar commented 3 years ago

one thing I do not understad is that why in cmssw we never got such error, we do not set PYTHON_EGG_CACHE

belforte commented 3 years ago

If it defaults to user home dir it may be that most of the times it works. Sent from. Stefano's phoneOn Jan 19, 2021 09:24, Malik Shahzad Muzaffar notifications@github.com wrote: one thing I do not understad is that why in cmssw we never got such error, we do not set PYTHON_EGG_CACHE

—You are receiving this because you were mentioned.Reply to this email directly, view it on GitHub, or unsubscribe.

amaltaro commented 3 years ago

Exactly, I only spotted it because I hit this error back in November, when using an AFS account:

  [Errno 122] Disk quota exceeded: '/afs/cern.ch/user/c/cmst1/.cache/Python-Eggs/cx_Oracle-5.2.1-py2.7-linux-x86_64.egg-tmp/tmp7QVL6I.$extract'

I guess the correct place to set it then would be in the init.sh/init.csh, right? What I read on google is that one could change it to /tmp, or even /tmp/python-eggs. We need to make sure though that the cx_Oracle.so module is properly available under site-packages, and not from the temp area.

amaltaro commented 3 years ago

An easier option to test first might be: """ The easiest way to do this is to set it in your ~/.bash_profile (assuming you're using bash), like this: export PYTHON_EGG_CACHE=/some/other/dir" """

it does involve changing .bash_profile on every single user that will deploy these services though... which might mean it's not really an option.

smuzaffar commented 3 years ago

is there any simple way to reproduce the issue? simply installing crabserver, sourcing its env and then running python -c 'import cx_Oracle' does not produce any error for me (even if PYTHON_EGG_CACHE is set to /root)

belforte commented 3 years ago

@smuzaffar does running the container qualify as a simple way ?

ssh cmsdocker01
belforte@cmsdocker01/~> docker run --rm -h `hostname -f` -v /tmp:/tmp -i -t  sbelforte/crabserver:v3.210110.cx73 /bin/bash
/data/srv/current/config/crabserver/manage start 'I did read documentation'
ls -al ~_crabserver/.cache/

note that in this case the cache was created w/o problems in /home/_crabserver

I am not sure what exactly was/is the problem in K8s, seems like the container starts as root then it switches to _crabserver to run the crabserver application but $HOME stays what it was and so the cache points to /root/.cache which _crabserver user can't write.