search

VEuPathDB / service-eda

Repo containing EDA web service
Apache License 2.0
0 stars 0 forks source link

QA SQL queries while querying /studies 07/09/2024 #49

Open dmgaldi opened 2 weeks ago

dmgaldi commented 2 weeks ago

Stacktrace:

Caused by: java.lang.RuntimeException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object, borrowMaxWaitDuration=PT1S
        at org.veupathdb.service.eda.subset.model.db.PlatformUtils.fromDataSource(PlatformUtils.java:33) ~[service.jar:3.0.0]
        at org.veupathdb.service.eda.subset.model.db.EntityFactory.attributesTableExists(EntityFactory.java:77) ~[service.jar:3.0.0]
        at org.veupathdb.service.eda.subset.model.db.EntityFactory.lambda$getStudyEntityTree$0(EntityFactory.java:49) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.runner.SQLRunnerExecutors$QueryExecutor.handleResult(SQLRunnerExecutors.java:258) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.runner.SQLRunner.executeSql(SQLRunner.java:419) ~[service.jar:3.0.0]
        ... 33 more
Caused by: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object, borrowMaxWaitDuration=PT1S
        at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:145) ~[service.jar:3.0.0]
        at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:711) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.wrapper.AbstractDataSourceWrapper.getConnection(AbstractDataSourceWrapper.java:31) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.wrapper.DataSourceWrapper.lambda$getConnection$0(DataSourceWrapper.java:43) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.wrapper.DataSourceWrapper.checkExhaustedPool(DataSourceWrapper.java:54) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.wrapper.DataSourceWrapper.getConnection(DataSourceWrapper.java:42) ~[service.jar:3.0.0]
        at org.veupathdb.service.eda.subset.model.db.PlatformUtils.fromDataSource(PlatformUtils.java:27) ~[service.jar:3.0.0]
        at org.veupathdb.service.eda.subset.model.db.EntityFactory.attributesTableExists(EntityFactory.java:77) ~[service.jar:3.0.0]
        at org.veupathdb.service.eda.subset.model.db.EntityFactory.lambda$getStudyEntityTree$0(EntityFactory.java:49) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.runner.SQLRunnerExecutors$QueryExecutor.handleResult(SQLRunnerExecutors.java:258) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.runner.SQLRunner.executeSql(SQLRunner.java:419) ~[service.jar:3.0.0]
        ... 33 more
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object, borrowMaxWaitDuration=PT1S
        at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:316) ~[service.jar:3.0.0]
        at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:233) ~[service.jar:3.0.0]
        at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:139) ~[service.jar:3.0.0]
        at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:711) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.wrapper.AbstractDataSourceWrapper.getConnection(AbstractDataSourceWrapper.java:31) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.wrapper.DataSourceWrapper.lambda$getConnection$0(DataSourceWrapper.java:43) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.wrapper.DataSourceWrapper.checkExhaustedPool(DataSourceWrapper.java:54) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.wrapper.DataSourceWrapper.getConnection(DataSourceWrapper.java:42) ~[service.jar:3.0.0]
        at org.veupathdb.service.eda.subset.model.db.PlatformUtils.fromDataSource(PlatformUtils.java:27) ~[service.jar:3.0.0]
        at org.veupathdb.service.eda.subset.model.db.EntityFactory.attributesTableExists(EntityFactory.java:77) ~[service.jar:3.0.0]
        at org.veupathdb.service.eda.subset.model.db.EntityFactory.lambda$getStudyEntityTree$0(EntityFactory.java:49) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.runner.SQLRunnerExecutors$QueryExecutor.handleResult(SQLRunnerExecutors.java:258) ~[service.jar:3.0.0]
        at org.gusdb.fgputil.db.runner.SQLRunner.executeSql(SQLRunner.java:419) ~[service.jar:3.0.0]
        ... 33 more
dmgaldi commented 2 weeks ago

All of the stacktraces point to the query to check if wide table exists. This query was recently refactored but should be the same as ever. Maybe it's going slow because it's unindexed and there are many tables?

dmgaldi commented 2 weeks ago

Fixing a connection leak introduced in detecting the platform as part of above refactor.

dmgaldi commented 2 weeks ago

https://github.com/VEuPathDB/lib-eda-subsetting/commit/4814d44e931a6f11955782772fa788b01de681c8