acitve connection never return to the connection pool

[Karsten Spakowski]

Hello,

i have trouble to get connections back from status active to idle.

I use JPA to fire a stored procedure against a SYBASE ASE database.

Each time the procedure is fired a new connection from the pool is taken an switched to active.

The procedure is successfully executed on the database server but the connection does not return to the pool, it stays active for ever.

As a result after a while the pool cannot provide a connection since the max connection for that pool  are created and all are active.

It only happens for stored procedures, if I fire JPQL select queries the connections return to the pool.

Another point if i kill active connection on the database server the pool does not recognize it, I thought the Hikari would fork a new connection ? 

  

Attached the configuration and the version i used.

Configuration

spring.datasource.driver-class-name=com.sybase.jdbc4.jdbc.SybDriver
spring.datasource.url=jdbc:sybase:Tds:localhost:20000/mydb


spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SybaseDialect



spring.datasource.type=com.zaxxer.hikari.HikariDataSource


spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=100
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.idleTimeout=10000
spring.datasource.hikari.poolName=SpringBootJPAHikariCP
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.username=user
spring.datasource.hikari.password=******
spring.datasource.hikari.connectionTimeout=10000
spring.datasource.hikari.connection-test-query=select 1 from myTable



spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider


logging.level.com.zaxxer.hikari=TRACE

Versions:

• HikarCP 3.20
  
• SpringBoot 1.5.12
• jconn4, (i also tried jtds)
• Adaptive Server Enterprise/12.5.4/EBF 14124 ESD#4/P/x86_64/Enterprise Linux/ase1254/2034/64-bit/OPT/Thu Nov 30 05:39:47 2006
  

Thanks a lot for your help.

Regards Karsten

[Brett Wooldridge]

I am not familiar with SyBase stored procedures, but something needs to close the connection in order to return to the pool. Maybe ask on stackoverflow.

Regarding killing connections, HikariCP will not be aware of it until a Connection is validated on borrow — at which point it will be retired.

[Karsten Spakowski]

Hi Brett 

thanks you so much for your answer. 

Until now I could not figure out why the stored procedure does not close the connection.

Once agin thx.

Karsten

[黄振东]

Hello， Have you ever figured out what happened in sybase store procedure ?  Based on the  HikariCP principle , once a connection has got from pool , it's in "IN_USE" state. And when finished use,  you should close the connection.  Then the connection proxy will return the connection back to connection pool. Mostly change the state to "NOT_IN_USE".  So wish you feedback, and good luck to you !

在 2018年6月27日星期三 UTC+8下午4:27:12，Karsten Spakowski写道：

在 2018年6月27日星期三 UTC+8下午4:27:12，Karsten Spakowski写道：

在 2018年6月27日星期三 UTC+8下午4:27:12，Karsten Spakowski写道：