search

ohdsi-studies / PioneerWatchfulWaiting

This study is part of the joint PIONEER - EHDEN - OHDSI studyathon in March 2021, and aims to advance understanding of clinical management and outcomes of watchful waiting in prostate cancer.
Apache License 2.0
7 stars 18 forks source link

Error when running cohortDiagnostics #27

Closed YorgosA closed 3 years ago

YorgosA commented 3 years ago

I run the first part of the package (cohortDiagnostics) and after running for almost 42 hours it finished with an error and didn't produce the expected output. I'm pasting the last rows of the cohortDiagnosticslog.txt file below:

Finding orphan concepts took 4.45 mins Running concept set diagnostics took 4.43 hours Creating time distributions Error when running CohortDiagnostics::runCohortDiagnostics on CohortGroup: target Error in FALSE: Error executing SQL: java.sql.SQLException: [Amazon] (500310) Invalid operation: syntax error at or near "," Position: 93; An error report has been created at /home/gargyriou/Packages/pioneer_031/PioneerWatchfulWaiting-0.3.1/errorReportSql.txt

Run Cohort Diagnostics started at 2021-04-09 13:29:30 Created folder at /home/gargyriou/output/pioneer_v2//OpenClaims/diagnostics/outcome Number of cohorts 10 Found existing record keeping file in incremental folder - CreatedDiagnostics.csv Saving database metadata Saving database metadata took 0.0268 secs Error when running CohortDiagnostics::runCohortDiagnostics on CohortGroup: outcome Error in FALSE: Error executing SQL: java.sql.SQLException: [Amazon] (500310) Invalid operation: current transaction is aborted, commands ignored until end of transaction block; An error report has been created at /home/gargyriou/Packages/pioneer_031/PioneerWatchfulWaiting-0.3.1/errorReportSql.txt

Run Cohort Diagnostics started at 2021-04-09 13:29:35 Created folder at /home/gargyriou/output/pioneer_v2//OpenClaims/diagnostics/strata Number of cohorts 38 Found existing record keeping file in incremental folder - CreatedDiagnostics.csv Saving database metadata Saving database metadata took 0.0282 secs Error when running CohortDiagnostics::runCohortDiagnostics on CohortGroup: strata Error in FALSE: Error executing SQL: java.sql.SQLException: [Amazon] (500310) Invalid operation: current transaction is aborted, commands ignored until end of transaction block; An error report has been created at /home/gargyriou/Packages/pioneer_031/PioneerWatchfulWaiting-0.3.1/errorReportSql.txt

I think the issue will be in the first error message. The two other errors are consequence of the first one.

keesvanbochove commented 3 years ago

Hello @YorgosA could you also include (last part of) cohortDiagnosticsLog.txt? This should show which cohort definition is the culprit.

YorgosA commented 3 years ago

2021-04-09 13:29:27 [Main thread] INFO CohortDiagnostics runConceptSetDiagnostics Running concept set diagnostics took 4.43 hours 2021-04-09 13:29:27 [Main thread] INFO CohortDiagnostics runCohortDiagnostics Creating time distributions 2021-04-09 13:29:30 [Main thread] ERROR PioneerWatchfulWaiting 3 Error when running CohortDiagnostics::runCohortDiagnostics on CohortGroup: target 2021-04-09 13:29:30 [Main thread] ERROR PioneerWatchfulWaiting 3 Error in FALSE: Error executing SQL: java.sql.SQLException: [Amazon] (500310) Invalid operation: syntax error at or near "," Position: 93; An error report has been created at /home/gargyriou/Packages/pioneer_031/PioneerWatchfulWaiting-0.3.1/errorReportSql.txt 2021-04-09 13:29:30 [Main thread] INFO CohortDiagnostics runCohortDiagnostics Run Cohort Diagnostics started at 2021-04-09 13:29:30 2021-04-09 13:29:30 [Main thread] INFO CohortDiagnostics createIfNotExist Created folder at /home/gargyriou/output/pioneer_v2//OpenClaims/diagnostics/outcome 2021-04-09 13:29:30 [Main thread] DEBUG CohortDiagnostics getCohortsJsonAndSqlFromPackage Executing on cohorts specified in package - PioneerWatchfulWaiting 2021-04-09 13:29:34 [Main thread] INFO CohortDiagnostics getCohortsJsonAndSql Number of cohorts 10 2021-04-09 13:29:34 [Main thread] DEBUG CohortDiagnostics writeToCsv creating /home/gargyriou/output/pioneer_v2//OpenClaims/diagnostics/outcome/cohort.csv 2021-04-09 13:29:34 [Main thread] DEBUG CohortDiagnostics runCohortDiagnostics Working in incremental mode. 2021-04-09 13:29:34 [Main thread] INFO CohortDiagnostics runCohortDiagnostics Found existing record keeping file in incremental folder - CreatedDiagnostics.csv 2021-04-09 13:29:34 [Main thread] INFO CohortDiagnostics runCohortDiagnostics Saving database metadata 2021-04-09 13:29:34 [Main thread] DEBUG CohortDiagnostics writeToCsv creating /home/gargyriou/output/pioneer_v2//OpenClaims/diagnostics/outcome/database.csv 2021-04-09 13:29:34 [Main thread] TRACE CohortDiagnostics runCohortDiagnostics Creating concept ID table for tracking concepts used in diagnostics 2021-04-09 13:29:35 [Main thread] ERROR PioneerWatchfulWaiting 3 Error when running CohortDiagnostics::runCohortDiagnostics on CohortGroup: outcome 2021-04-09 13:29:35 [Main thread] ERROR PioneerWatchfulWaiting 3 Error in FALSE: Error executing SQL: java.sql.SQLException: [Amazon] (500310) Invalid operation: current transaction is aborted, commands ignored until end of transaction block; An error report has been created at /home/gargyriou/Packages/pioneer_031/PioneerWatchfulWaiting-0.3.1/errorReportSql.txt 2021-04-09 13:29:35 [Main thread] INFO CohortDiagnostics runCohortDiagnostics Run Cohort Diagnostics started at 2021-04-09 13:29:35 2021-04-09 13:29:35 [Main thread] INFO CohortDiagnostics createIfNotExist Created folder at /home/gargyriou/output/pioneer_v2//OpenClaims/diagnostics/strata 2021-04-09 13:29:35 [Main thread] DEBUG CohortDiagnostics getCohortsJsonAndSqlFromPackage Executing on cohorts specified in package - PioneerWatchfulWaiting 2021-04-09 13:29:40 [Main thread] INFO CohortDiagnostics getCohortsJsonAndSql Number of cohorts 38 2021-04-09 13:29:41 [Main thread] DEBUG CohortDiagnostics writeToCsv creating /home/gargyriou/output/pioneer_v2//OpenClaims/diagnostics/strata/cohort.csv 2021-04-09 13:29:41 [Main thread] DEBUG CohortDiagnostics runCohortDiagnostics Working in incremental mode. 2021-04-09 13:29:41 [Main thread] INFO CohortDiagnostics runCohortDiagnostics Found existing record keeping file in incremental folder - CreatedDiagnostics.csv 2021-04-09 13:29:41 [Main thread] INFO CohortDiagnostics runCohortDiagnostics Saving database metadata 2021-04-09 13:29:41 [Main thread] DEBUG CohortDiagnostics writeToCsv creating /home/gargyriou/output/pioneer_v2//OpenClaims/diagnostics/strata/database.csv 2021-04-09 13:29:41 [Main thread] TRACE CohortDiagnostics runCohortDiagnostics Creating concept ID table for tracking concepts used in diagnostics 2021-04-09 13:29:41 [Main thread] ERROR PioneerWatchfulWaiting 3 Error when running CohortDiagnostics::runCohortDiagnostics on CohortGroup: strata 2021-04-09 13:29:41 [Main thread] ERROR PioneerWatchfulWaiting 3 Error in FALSE: Error executing SQL: java.sql.SQLException: [Amazon] (500310) Invalid operation: current transaction is aborted, commands ignored until end of transaction block; An error report has been created at /home/gargyriou/Packages/pioneer_031/PioneerWatchfulWaiting-0.3.1/errorReportSql.txt 2021-04-09 13:29:46 [Main thread] INFO PioneerWatchfulWaiting runCohortDiagnostics PIONEER cohort diagnostics are bundled for sharing at: /home/gargyriou/output/pioneer_v2//OpenClaims/diagnostics/Results_diagnostics_OpenClaims_20210409T132946.zip

keesvanbochove commented 3 years ago

Hello @YorgosA looking at this log and the timing I think you actually ran v0.3.1 which had some known errors. Could you try installing and running version 0.3.2 instead?

YorgosA commented 3 years ago

Sure. I'll let you know once it is done.

YorgosA commented 3 years ago

The error comes up when the package is trying to create time distributions.

From the console:

_Finding orphan concepts took 12.6 mins Running concept set diagnostics took 5.28 hours Creating time distributions Error when running CohortDiagnostics::runCohortDiagnostics on CohortGroup: strata Error in FALSE: Error executing SQL: java.sql.SQLException: [Amazon] (500310) Invalid operation: syntax error at or near "," Position: 93; An error report has been created at /home/gargyriou/Packages/pioneer032/PioneerWatchfulWaiting-0.3.2/errorReportSql.txt

From the errorReportSql.txt file:

_SQL: SELECT COUNT(*) FROM study_reference.PIONEER_OpenClaims_stg WHERE cohort_definitionid = 304,313,319,327,328,329,330,331,332,333,337,338

The code above needs in and brackets for multiple values, not equal sign. I cannot find the SQL file in the package to fix it. Can you help?

keesvanbochove commented 3 years ago

@YorgosA which versions of the CohortDiagnostics and FeatureExtraction packages do you have installed, and do these match the ones in renv.lock? This could should be invoked from R/TimeDistributions.R. Could you try ensuring that FeatureExtraction v3.1.0 is installed? Looking at https://github.com/OHDSI/FeatureExtraction/compare/v3.0.1...v3.1.0 this may have been fixed in that version (R/GetCovariates.R). If that fixes it, I will update renv.lock to point to v3.1.0 instead.

YorgosA commented 3 years ago

I had FeatureExtraction v3.0.1 as renv suggested. Switching to FeatureExtraction v3.1.0 fixed the problem however so you can update renv.lock.