User studies: Install failed with message about redundant IDs

[SheenaTomko]

Tried to upload the PRISM_cohort_Participant.txt file. Import worked, but install failed in the logs with a message about redundant IDs. Jay thinks this is a bug in the validator and that there are no redundant IDs. See slack thread: https://epvb.slack.com/archives/CBEA7PQDQ/p1657045518138049

[dmgaldi]

Jay checked in a fix for this in CBIL. We still need to test that fix by updating the docker file in dataset-installer-isasimple. I'll create a PR for this today.

[dmgaldi]

Initial issue was fixed.

Latest issue has to do with "maximum open cursors exceeded" in oracle:

DBD::Oracle::st execute failed: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded (DBD ERROR: error possibly near <*> indicator at char 35 in '
    INSERT INTO ApidbUserDatasets.<*>EntityAttributes (  row_alg_invocation_id, user_read, entity_attributes_id, entity_type_id, row_user_id, group_read, other_read, row_group_id, modification_date, stable_id, group_write, atts, other_write, row_project_id, user_write )
    VALUES   (  :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, SYSDATE, :p9, :p10, :p11, :p12, :p13, :p14 ) ') [for Statement "
    INSERT INTO ApidbUserDatasets.EntityAttributes (  row_alg_invocation_id, user_read, entity_attributes_id, entity_type_id, row_user_id, group_read, other_read, row_group_id, modification_date, stable_id, group_write, atts, other_write, row_project_id, user_write )
    VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, ?, ?, ?, ?, ?, ? ) " with ParamValues: :p1='10607', :p10=1, :p11='{"TEMP_Age at enrollment (years) [EUPATH_0000120]":["2.9095891"],"TEMP_CD36 genotype [EUPATH_0000737]":["Wild type"],"TEMP_Participant_Id":["1296"],"TEMP_G6PD genotype [EUPATH_0000033]":["Normal (female)"],"TEMP_Alpha-thalassemia genotype [EUPATH_0000034]":["Alpha + thalassemia"],"TEMP_Household_Id":["h_206031501"],"TEMP_Reason for withdrawal [EUPATH_0000208]":["Moved out of area"],"TEMP_Enrollment date [EUPATH_0000151]":["2011-08-27"],"TEMP_Sex [PATO_0000047]":["Female"],"TEMP_Last date observed [EUPATH_0000152]":["2012-04-12"],"TEMP_HbS genotype [EUPATH_0000035]":["Hb aa"],"TEMP_Timing of enrollment [EUPATH_0000219]":["At time of initial household enrollment"]}', :p12=0, :p13='2', :p14=1, :p2=1, :p3='3018', :p4='162', :p5='41', :p6=1, :p7=1, :p8='2', :p9="prism_cohort_participants:391"] at /gusApp/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 147, <$fileHandle> line 501.
DBD::Oracle::st execute failed: ORA-01000: maximum open cursors exceeded (DBD ERROR: OCIStmtExecute) [for Statement "
    UPDATE  Core.AlgorithmInvocation
    SET
        result = ?,
        row_alg_invocation_id = ?,
        end_time = SYSDATE,
        modification_date = SYSDATE
    WHERE   algorithm_invocation_id = ?" with ParamValues: :p1='FAILED', :p2='10607', :p3='10607'] at /gusApp/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 147.
DBD::Oracle::st execute failed: ORA-01000: maximum open cursors exceeded (DBD ERROR: OCIStmtExecute) [for Statement "
    UPDATE  Core.AlgorithmInvocation
    SET
        result = ?,
        row_alg_invocation_id = ?,
        end_time = SYSDATE,
        modification_date = SYSDATE
    WHERE   algorithm_invocation_id = ?" with ParamValues: :p1='FAILED', :p2='10607', :p3='10607'] at /gusApp/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 147.
/usr/local/bin/loadStudy.bash: line 43: 23389 Segmentation fault      ga ApiCommonData::Load::Plugin::InsertEntityGraph --metaDataRoot $PWD --investigationSubset $study --investigationBaseName investigation.xml --isSimpleConfiguration --ontologyMappingFile $PWD/$study/ontologyMapping.xml --extDbRlsSpec "${study}|dontcare" --dateObfuscationFile $PWD/$study/dateObfuscation.txt --schema ApidbUserDatasets --userDatasetId $userDatasetId --commit

[dmgaldi]

The cursor leak issue seems to have been fixed by this commit by Jay:

https://github.com/VEuPathDB/ApiCommonData/commit/436babe39a06257aaef35bb14e9c166f1943de93

A formerly failing dataset was successfully installed! I'm going to try the PRISM dataset again.

[dmgaldi]

Failed when re-running.

Tue Jul 12 16:05:38 2022        Processed 1001 lines
DBD::Oracle::st execute failed: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded (DBD ERROR: error possibly near <*> indicator at char 35 in '
    INSERT INTO ApidbUserDatasets.<*>EntityAttributes (  row_project_id, entity_type_id, group_read, atts, other_read, modification_date, row_user_id, entity_attributes_id, other_write, group_write, user_read, user_write, row_alg_invocation_id, row_group_id, stable_id )
    VALUES   (  :p1, :p2, :p3, :p4, :p5, SYSDATE, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14 ) ') [for Statement "
    INSERT INTO ApidbUserDatasets.EntityAttributes (  row_project_id, entity_type_id, group_read, atts, other_read, modification_date, row_user_id, entity_attributes_id, other_write, group_write, user_read, user_write, row_alg_invocation_id, row_group_id, stable_id )
    VALUES   (  ?, ?, ?, ?, ?, SYSDATE, ?, ?, ?, ?, ?, ?, ?, ?, ? ) " with ParamValues: :p1='62', :p10=1, :p11=1, :p12='11006', :p13='2', :p14="prism_cohort_participants:507", :p2='181', :p3=1, :p4='{"TEMP_HbS genotype [EUPATH_0000035]":["Hb as"],"TEMP_Timing of enrollment [EUPATH_0000219]":["During dynamic recruitment"],"TEMP_G6PD genotype [EUPATH_0000033]":["Normal (male)"],"TEMP_Household_Id":["h_207009501"],"TEMP_Sex [PATO_0000047]":["Male"],"TEMP_Enrollment date [EUPATH_0000151]":["2014-02-04"],"TEMP_Alpha-thalassemia genotype [EUPATH_0000034]":["Alpha + thalassemia"],"TEMP_Last date observed [EUPATH_0000152]":["2016-06-29"],"TEMP_Age at enrollment (years) [EUPATH_0000120]":["7.4821916"],"TEMP_Participant_Id":["1412"],"TEMP_CD36 genotype [EUPATH_0000737]":["Wild type"]}', :p5=1, :p6='41', :p7='12587', :p8=0, :p9=1] at /gusApp/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 147, <$fileHandle> line 1001.
DBD::Oracle::st execute failed: ORA-01000: maximum open cursors exceeded (DBD ERROR: OCIStmtExecute) [for Statement "

[SheenaTomko]

This issue was fixed with input from Jay, Ana, John B and others. Closing the ticket