search

OHDSI / WebAPI

OHDSI WebAPI contains all OHDSI services that can be called from OHDSI applications
Apache License 2.0
126 stars 156 forks source link

Cohort Delete thows CleanupCohortTasket JDBC errors #2334

Open alondhe opened 5 months ago

alondhe commented 5 months ago

Expected behavior

(Using WebAPI 2.14.0 / Atlas 2.14.1)

When deleting a cohort, the org.ohdsi.webapi.cohortdefinition.CleanupCohortTasklet fires without error, and the cohort records are deleted from the pertinent cohort tables for all CDMs in which we've executed the cohort definition.

Actual behavior

(I've confirmed our JDBC connections and the service account's ability to delete from CDM results schemas.)

A few different things to cover here:

  1. It looks like this tasklet is attempted against all CDMs in WebAPI, even for those that haven't been executed. So we see JDBC errors for those, perhaps some additional logic to determine which to delete from would help here.

  2. The tasklet is unable to execute against Redshift:

ERROR taskExecutor-3 org.ohdsi.webapi.cohortdefinition.CleanupCohortTasklet - [] - Error deleting results for cohort: 992, cause: Could not get JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: [Amazon][JDBC](10100) Connection Refused: [Amazon][JDBC](11640) Required Connection Key(s): PWD, UID; [Amazon][JDBC](11480) Optional Connection Key(s): AccessKeyID, AuthMech, AutoCreate, BlockingRowsMode, ClusterID, DbGroups, DisableIsValidQuery, DriverLogLevel, EndpointUrl, FilterLevel, IAMDuration, Language, loginTimeout, OpenSourceSubProtocolOverride, plugin_name, profile, Region, SecretAccessKey, SessionToken, socketTimeout, ssl, sslcert, sslfactory, sslkey, sslpassword, sslrootcert, SSLTruststore , SSLTrustStorePath, tcpKeepAlive, TCPKeepAliveMinutes, unknownLength

  1. The tasklet is unable to execute against Snowflake. The JDBC connection has a user name, so not sure why this is an error:

ERROR taskExecutor-3 org.ohdsi.webapi.cohortdefinition.CleanupCohortTasklet - [] - Error deleting results for cohort: 992, cause: Could not get JDBC Connection; nested exception is net.snowflake.client.jdbc.SnowflakeSQLLoggedException: JDBC driver internal error: missing user name for opening session.

  1. I've run the deletes from https://github.com/OHDSI/WebAPI/blob/v2.14.0/src/main/resources/resources/cohortdefinition/sql/cleanupResults.sql manually with the same JDBC connection details without issue.

Steps to reproduce behavior

  1. Create and generate a cohort
  2. Delete it
  3. View webapi logs
  4. Also you can see the cohort records still exist in the appropriate CDM results schemas

Tagging @konstjar

alondhe commented 5 months ago

Is there something we should examine with the "hikari" settings here:

https://github.com/OHDSI/WebAPI/blob/master/pom.xml#L183C1-L189C94

I'd seen some mention on Stack Overflow about these JDBC errors and tuning these settings when using Spring Boot.

alondhe commented 5 months ago

On my local Broadsea, a delete of a cohort that was generated against Eunomia on PG works without error. So likely some network issue with our cloud hosted db platforms.

@konstjar and @chrisknoll - could you try this in your environments to confirm? Just create, generate, delete a cohort. Does WebAPI throw these JDBC errors for you?