[Another] Unable to acquire JDBC connection

See original GitHub issue

Describe the bug Hi everyone, I followed the guide on quarkus jdbc, quarkus hibernate orm and quarkus hibernate panache. My environnement :

Adoptopenjdk 8
Quarkus 1.4.2 Final
Dockerfile.jvm for quarkus
Docker mariadb and postgres for development

What’s the problem ?

I launched the application with one datasource (same problem on postgres and mariadb).
I put some configuration for timeout (extremely low because of my bug) : See below my conf
After 1000/2000 requests, I get the error : Unable to acquire JDBC connection (Detail below on Scrrenshot section)
The idle_in_transaction_session_timeout is set to 0 on Postgres
The statement_timeout is set to 0 on Postgres too.
This behaviour is the same on Mariadb.
Sometimes it happens at the 50th request sometimes at the 200th, sometimes more but it happens everytime.

Expected behavior

No more Unable to acquire JDBC connection 😭 .

Actual behavior (Describe the actual behavior clearly and concisely.)

To Reproduce Steps to reproduce the behavior:

Make a JDBC connection on Mariadb or Postgres
Create a jax-rs get path
Create a service class annoted @RequestScoped
Make a findAll on your panache entity in the service class
Return the list in your jax-rs get path
Launch the app
Make many request in a short time (Bot or something else)
In a 1000/2000 requests you can get this error

Configuration

# Add your application.properties here, if applicable.
quarkus.datasource.url = jdbc:postgresql://localhost:3306/eztalk
quarkus.datasource.jdbc.url=jdbc:postgresql://localhost:3306/eztalk
quarkus.datasource.driver=org.postgresql.Driver
quarkus.datasource.jdbc.driver=org.postgresql.Driver
quarkus.datasource.username=USERNAME
quarkus.datasource.password=PASSWORD
quarkus.datasource.jdbc.min-size=4
quarkus.datasource.jdbc.max-size=50
quarkus.datasource.jdbc.max-lifetime=150
quarkus.datasource.jdbc.idle-removal-interval=50
quarkus.datasource.jdbc.background-validation-interval=10
quarkus.datasource.jdbc.leak-detection-interval=30
quarkus.datasource.jdbc.acquisition-timeout=10
quarkus.datasource.db-kind=postgresql

Screenshots I can give you the error

eztalk-service_1        | Caused by: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
eztalk-service_1        | 	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
eztalk-service_1        | 	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
eztalk-service_1        | 	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
eztalk-service_1        | 	at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:107)
eztalk-service_1        | 	at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:134)
eztalk-service_1        | 	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:50)
eztalk-service_1        | 	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
eztalk-service_1        | 	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
eztalk-service_1        | 	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
eztalk-service_1        | 	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2104)
eztalk-service_1        | 	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2034)
eztalk-service_1        | 	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
eztalk-service_1        | 	at org.hibernate.loader.Loader.doQuery(Loader.java:953)
eztalk-service_1        | 	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
eztalk-service_1        | 	at org.hibernate.loader.Loader.doList(Loader.java:2838)
eztalk-service_1        | 	at org.hibernate.loader.Loader.doList(Loader.java:2820)
eztalk-service_1        | 	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2652)
eztalk-service_1        | 	at org.hibernate.loader.Loader.list(Loader.java:2647)
eztalk-service_1        | 	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
eztalk-service_1        | 	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:396)
eztalk-service_1        | 	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
eztalk-service_1        | 	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1404)
eztalk-service_1        | 	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1565)
eztalk-service_1        | 	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533)
eztalk-service_1        | 	... 285 more
eztalk-service_1        | Caused by: java.sql.SQLException: Sorry, acquisition timeout!
eztalk-service_1        | 	at io.agroal.pool.ConnectionPool.handlerFromSharedCache(ConnectionPool.java:244)
eztalk-service_1        | 	at io.agroal.pool.ConnectionPool.getConnection(ConnectionPool.java:173)
eztalk-service_1        | 	at io.agroal.pool.DataSource.getConnection(DataSource.java:66)
eztalk-service_1        | 	at io.quarkus.hibernate.orm.runtime.customized.QuarkusConnectionProvider.getConnection(QuarkusConnectionProvider.java:23)
eztalk-service_1        | 	at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38)
eztalk-service_1        | 	at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:104)
eztalk-service_1        | 	... 305 more

Environment (please complete the following information):

Output of uname -a or ver: Linux NOPLACELIKEHOME 4.19.107-1-MANJARO #1 SMP Fri Feb 28 21:14:27 UTC 2020 x86_64 GNU/Linux
Output of java -version:

openjdk version "1.8.0_242"
OpenJDK Runtime Environment (build 1.8.0_242-b08)
OpenJDK 64-Bit Server VM (build 25.242-b08, mixed mode)

GraalVM version (if different from Java): Not using
Quarkus version or git rev: 1.4.2 Final
Build tool (ie. output of mvnw --version or gradlew --version):

Apache Maven 3.6.3 (NON-CANONICAL_2019-11-27T20:26:29Z_root)
Maven home: /opt/maven
Java version: 1.8.0_242, vendor: Oracle Corporation, runtime: /usr/lib/jvm/java-8-openjdk/jre
Default locale: fr_FR, platform encoding: UTF-8
OS name: "linux", version: "4.19.107-1-manjaro", arch: "amd64", family: "unix"

Additional context

I tried to put acquisition timeout at 30s or more but it’s the same and I don’t think it can be possible to an user waiting more than 30s because it can be difficult to get a JDBC connection. I tried to follow advices I saw on Google but none did the job.

I’m available by this issue, email, phone, or others to talk more about this.

Thank you in advance for the time you will take to read this,

Issue Analytics

State:
Created 3 years ago
Reactions:4
Comments:30 (6 by maintainers)

Top GitHub Comments

3reactions

DorianMaliszewskicommented, May 8, 2020

Problem found

Ok, so after multiple tests, I find where is the problem :

Hibernate Panache

I don’t know why but when I call Blabla.list(“service = ?1”, id) it works but doesn’t close the statement, and in my metrics the agroal_active_* is increasing.

To resolve that

I just injected the entityManager in my service class and make the request with DTO projection in my request

Detail of my process

May be the statement is not close because :

I get the request in my BlablaResource.java (findAll) with @Path and @Get annotations
I use my BlablaService.java which I injected with @Inject
This service class is @RequestScoped
SO In my findAll method I call the findAllFromServiceClass method
In findAllFromServiceClass method I just do a return Blabla.list(“service = ?1”, id)
I get the list in findAll method and do .parallelStream().map(BlablaDTO::new).collect(Collectors.toList())
In my DTO I try to access relation (@OneToMany, @OneToOne, …)
I think the problem come from this : when I try to access the relation in my DTO constructor the requests are made but the statement are never closed.

So, if someone can help me on that, or confirm that there are a problem with the extension, it would be nice.

I’m available to talk more about this, and may be help to solve this bug.

EDIT

I confirm the problem is when I do the transformation of Blabla to BlablaDTO, it’s the same when I put it in BlablaResource or BlablaService. To solve this I need to put the DTO constructor in the query directly

2reactions

kostacasacommented, Nov 11, 2022

Not sure if this is helpful/useful but all the services we migrated to the reactive (postgres) driver stopped experiencing these issues. Not discounting the possibility that same issues are occurring but being silently swallowed, but if anything it seems to me like the Vert.x driver has a better “dead connection” detection/restarting approach than the Agroal DS.

P.S. @rkorver it’s possible to increase the acquisition timeout property. You can look into that possibly, and decrease the time that idle connections are kept in the pool.

Top Results From Across the Web

Unable to acquire JDBC Connection - Stack Overflow

Unable to acquire JDBC Connection ; open JPA EntityManager for ; is javax.persistence.PersistenceException: org.hibernate.exception.

Unable to acquire JDBC Connection with Hibernate and MySQL

A solution to solve the problem with database connection that is idle too long.

org.hibernate.exception.GenericJDBCException: Unable to ...

1. Navigate to your AutomationEdge/aehome/conf/ and Update the password in the "database.properties" file. · 2. Replace the encrypted password and add password ...

OUTA:Unable To Acquire JDBC Connection - Oracle Support

OUTA:Unable To Acquire JDBC Connection -Connection Is Not Available, Request Timed Out After 30000ms (Doc ID 2685956.1).

Running Out of Database Connections - 3ap Engineering Blog

Unable to acquire JDBC Connection” is staring at you from your production logs. Your connection pool is running out of database connections.