2

I am working Spring Batch project which reads data from Oracle and Write it into DB, when I run the batch job, it gives me below error.

I went through many links like: https://github.com/brettwooldridge/HikariCP/issues/1202 and https://github.com/brettwooldridge/HikariCP/issues/1106 and https://jira.sakaiproject.org/si/jira.issueviews:issue-html/SAK-38852/SAK-38852.html, but not sure how to fit it.

jav

a.lang.Exception: Apparent connection leak detected
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100) ~[HikariCP-3.4.2.jar:na]
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:612) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:763) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.batch.item.database.JdbcPagingItemReader.doReadPage(JdbcPagingItemReader.java:224) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.item.database.AbstractPagingItemReader.doRead(AbstractPagingItemReader.java:110) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:93) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProvider.doRead(SimpleChunkProvider.java:99) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProvider.read(SimpleChunkProvider.java:180) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProvider$1.doInIteration(SimpleChunkProvider.java:126) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProvider.provide(SimpleChunkProvider.java:118) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:71) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:407) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:331) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:273) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:258) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:208) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:410) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:136) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:319) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:147) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) ~[spring-core-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:140) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    at com.sun.proxy.$Proxy67.run(Unknown Source) ~[na:na]
    at com.example.PostgresMongoContactsMigrationApplication.run(PostgresMongoContactsMigrationApplication.java:58) ~[classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784) ~[spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:768) ~[spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) ~[spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) ~[spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) ~[spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    at com.example.PostgresMongoContactsMigrationApplication.main(PostgresMongoContactsMigrationApplication.java:46) ~[classes/:na]

Code

@Bean(name = "oracleDataSource")
public DataSource oracleDataSource() {
    HikariConfig hikariConfig = new HikariConfig();
    hikariConfig.setDriverClassName(env.getProperty("oracle.datasource.driver-class-name"));
    hikariConfig.setJdbcUrl(env.getProperty("oracle.datasource.url"));
    hikariConfig.setUsername(env.getProperty("oracle.datasource.username"));
    hikariConfig.setPassword(env.getProperty("oracle.datasource.password"));

    hikariConfig.setMaximumPoolSize(70);
    hikariConfig.setConnectionTestQuery("SELECT 1 FROM DUAL");
    hikariConfig.setPoolName("oracle CP");
    hikariConfig.setMaxLifetime(300000);
    hikariConfig.setLeakDetectionThreshold(60 * 1000);
    hikariConfig.setConnectionTimeout(120 * 1000);

    return new HikariDataSource(hikariConfig);
}

Reader

@Bean
public JdbcCursorItemReader<Employee> employeeReader() throws Exception{
    JdbcCursorItemReader<Employee> reader = new JdbcCursorItemReader<>();
    reader.setSql(EMP_SQL);
    reader.setVerifyCursorPosition(false);
    reader.setDataSource(this.dataSource);
    reader.setFetchSize(5000);
    reader.setRowMapper(new EmployeeRowMapper());
    return reader;
}
  • Note - Once above error appears, connection never gets establishesh with the datasource and I dont see any data is coming.
Jeff Cook
  • 7,956
  • 36
  • 115
  • 186
  • When exactly does this issue happen? Please post the full stack trace, we can't see the root cause of the exception. – Mahmoud Ben Hassine Jun 25 '20 at 09:45
  • Hey, I've provided is th full stack trace, this happens while reading using `JdbcCursorItemReader` or `JdbcPagingItemReader` from Oracle DB. – Jeff Cook Jun 25 '20 at 10:55
  • No, that's not a full stack trace. We don't even see what the exception is nor its root cause (if any). – Mahmoud Ben Hassine Jun 25 '20 at 11:20
  • This is coming after enabiling trace logger level – Jeff Cook Jun 25 '20 at 12:15
  • Does this answer your question? [Apparent connection leak detected with Hikari CP](https://stackoverflow.com/questions/54883940/apparent-connection-leak-detected-with-hikari-cp) – Mahmoud Ben Hassine Jun 25 '20 at 13:04
  • @MahmoudBenHassine - Hey If you look at my configuration I've used same configurations. In fact I already reffered link and put the same configuration while posting the question. Any help now please ? – Jeff Cook Jun 25 '20 at 13:32
  • Yeah, but does your query take more than `LeakDetectionThreshold`? If it's the case, then you are in the same situation as the duplicate question and you need to increase the threshold. How long does your query take to return results? – Mahmoud Ben Hassine Jun 25 '20 at 13:38
  • @MahmoudBenHassine - Actually almost 20 batch jobs are already running and few of them are experincing this issue. Oracle DB is 11g. Query reads almost 20 millions records, JdbcCursorItemReader is taking time. What max values we can se here ? – Jeff Cook Jun 25 '20 at 18:39
  • I don't know what value you can use, it depends on how long your query takes to return, but it should be greater than the query time to avoid this issue as explained in the duplicate question. – Mahmoud Ben Hassine Jun 25 '20 at 20:31
  • This is a warning, did you try increasing leakDetectionThreshold? As previous comment if query is longer you should increase – Ori Marko Jun 29 '20 at 17:22
  • Thank you for accepting, Was issue fixed by removing/reducing minimumIdle? – Ori Marko Aug 19 '21 at 11:55

1 Answers1

1

This is a Hikari feature that warns you about possible leakage,

For long running queries you should consider increasing leakDetectionThreshold relative to your maximum query expected response time

You can figure that out by using HikariCP leakDetectionThreshold parameter. Configure it to the duration of your slowest query + some small constant to avoid false positives.

You can also try to tune hikari pool by reducing minimumIdle

to limit the life of idle connections

For reputable source you can ask your question in HikariCP discussion group

Ori Marko
  • 56,308
  • 23
  • 131
  • 233