Ошибка unable to acquire jdbc connection

I’m running a spring-boot application with hibernate and spring-data. I I have the following method:

@Service
class SomeService(private val repository : SomeRepository){

  fun someEndPoint(request: Request, Id: Long): UUID? {

      return try {

          val enNumber = repository.callEncrypt(request.accountNumber)

          val entity = SomeEntity().apply{
            this.id = Id
            this.enNumber = enNumber
          }

          repository.save(entity)


      } catch (e: Exception) {
          LOGGER.error("someEndPoint",e)
          throw e
      }
  }

}

When I run this method fourth times I got the Unable to acquire JDBC Connection exception. I suspect that when I call the stored procedure «callEncrypt» hibernate is not releasing the connection after the job is done, because if I remove that line the application works perfectly, there’s no way to close the connection manually since hibernate is handling the transaction (and there’s no reason to do it), so I’ve been stuck with this for a while and I don’t know what can be happening.

The weird thing is when I look for active connections in sql server with sp_who procedure, there’s no active connections and the only solution I have now is to restart the application. If I restart the application everything works fine again until I run this function fourth times again, so it’s obvious that hibernate is never releasing the connection.

Here’s the full stack trace:

org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:447)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy151.save(Unknown Source)
    at com.prosper.borrowerservicing.services.PaymentsService.addPayment(PaymentsService.kt:103)
    at com.prosper.borrowerservicing.ws.PaymentsEndpoint.addPayment(PaymentsEndpoint.kt:105)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:841)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1650)
    at org.eclipse.jetty.websocket.server.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:206)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at com.prosper.platform.healthcheck.filter.PlatformStatisticsFilter.doFilter(PlatformStatisticsFilter.java:42)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationProcessingFilter.doFilter(OAuth2AuthenticationProcessingFilter.java:176)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at com.prosper.platform.springboot.filter.PlatformCorrelationIdFilter.doFilter(PlatformCorrelationIdFilter.java:68)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:190)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
    at org.eclipse.jetty.server.Server.handle(Server.java:564)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:317)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:110)
    at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)
    at org.eclipse.jetty.util.thread.Invocable.invokePreferred(Invocable.java:128)
    at org.eclipse.jetty.util.thread.Invocable$InvocableExecutor.invoke(Invocable.java:222)
    at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:294)
    at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:199)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:673)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:591)
    at java.lang.Thread.run(Thread.java:748)
  Caused by: javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1700)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:48)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:189)
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)
    ... 120 common frames omitted
  Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:90)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:112)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:230)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:237)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214)
    at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:52)
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1512)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:45)
    ... 122 common frames omitted
  Caused by: java.sql.SQLTransientConnectionException: circle-one-db-connection-pool - Connection is not available, request timed out after 5003ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:548)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:145)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:99)
    at net.ttddyy.dsproxy.support.ProxyDataSource.getConnection(ProxyDataSource.java:42)
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:386)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:87)
    ... 129 common frames omitted

This is the datasource configuration related to the datasource:

database-name: *******
persistence-name:**********
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
max-connection-pool-size: 5
dialect: org.hibernate.dialect.SQLServer2012Dialect
show-sql: true
connection-pool-name: ************
user-name: *********
password: *******
port: ****
server: **************

This is the code of the Repository:

@Repository
interface SomeRepository : CrudRepository<SomeEntity, Int> {

  fun save(someEntity: SomeEntity): SomeEntity

  @Procedure("spEncrypt")
  fun callEncrypt(bankAccountNumber: String?): Array<Byte>
}

This is the code of the Entity:

@Entity
@EntityListeners(AuditingEntityListener::class)
@Immutable
@NamedStoredProcedureQueries(
NamedStoredProcedureQuery(name = "SomeEntity.SpEncrypt",
        procedureName = "spEncrypt",
        parameters =[
            StoredProcedureParameter(mode = ParameterMode.IN, name = "accountNumber", type = String::class),
            StoredProcedureParameter(mode = ParameterMode.OUT, name = "enNumber", type = Byte::class)
        ]
))
class SomeEntity (){
 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "Id", insertable = true, updatable = false, nullable 
 =false)
 var servicingPaymentsAddOnetimeId: Long = 0L,
 @Column(name = "enNumber", insertable = true, updatable = false, 
 nullable = 
 false)
 var enNumber: Long = 0L
}

Any help would be very appreciated.

When developing Java database web applications with Hibernate/JPA and MySQL, you will face a connection error if the user has not used the application for a long time. The exception stack trace would look like this:

The last packet successfully received from the server was 297,215,018 milliseconds ago. 
The last packet sent successfully to the server was 35 milliseconds ago.
...
javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: 
Unable to acquire JDBC Connection
...
Caused by: org.hibernate.exception.JDBCConnectionException: 
Unable to acquire JDBC Connection
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
...
Caused by: java.net.SocketException: Software caused connection abort: recv failed
…

If you refresh or try to connect again, the application successfully connected to the database. And the problem keeps happening again if the user left the application for a quite long time and come back. Though this is not a serious problem in development, but it’s not good under the end-user’s perspective and they will see the application is buggy and their work is interrupted. So this problem should be solved completely.

So why is JDBCConnectionException thrown?

By default, Hibernate uses its internal database connection pool library. That means it keeps a database connection open to be reused later. And MySQL database server has a timeout value for each connection (default is 8 hours or 28,800 seconds). So if a connection has been idle longer than this timeout value, it will be dropped by the server.

Therefore, when the Java database application has been idle longer than MySQL server’s connection timeout value, and the end user tries to connect again, Hibernate reuses the idle connection which was already dropped by the server, hence JDBCConnectionExceptionis thrown.

Solutions to fix JDBCConnectionException

If you look closely at the exception stack trace, you will see some suggestions to fix the problem:

The last packet sent successfully to the server was 390,061 milliseconds ago.
is longer than the server configured value of 'wait_timeout'. 
You should consider either expiring and/or testing connection validity before use in your application, 
increasing the server configured values for client timeouts, 
or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Let’s consider each suggestion mentioned above.

Expiring and/or testing connection validity before use in your application:

This requires changing or re-structuring the existing code, which is difficult to implement. Furthermore, opening a database connection is an expensive operation so it’s not optimal to open and close database connection for every user’s request — that’s why database connection pooling comes into play.

Increasing the server configured values for client timeouts:

This is possible by updating the setting file (my.ini) on MySQL server machine — changing the wait_timeoutvalue with longer value. However, the problem can occur again if the connection has been idle longer than the timeout value.

Using the Connector/J connection property ‘autoReconnect=true’

I tried this solution by appending the property autoReconnect=true to the database URL but it doesn’t work. The end user still sees the exception before it is successfully connected again. Also, the user of this feature is not recommended — accordingly to MySQL documentation.

If you look around on the web, you will see someone suggest adding the following properties to Hibernate configuration file:

<property name="connection.autoReconnect">true</property>
<property name="connection.autoReconnectForPools">true</property>
<property name="connection.is-connection-validation-required">true</property>

I already tried this, and it doesn’t work.

A solution that really works to solve JDBCConnectionException problem:

Finally I found a solution that actually works to avoid an error occur if the database connection is idle too long. It is using the c3p0 database connection pooling library.

If you project is Maven-based, add the following dependency to use c3p0:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>5.3.6.Final</version>
</dependency>

If not, let find, download on Maven Central Repository add the following JAR files to your project’s classpath:

hibernate-c3p0-5.3.6.Final.jar
c3p0-0.9.5.2.jar
mchange-commons-java-0.2.11.jar

Then add the following properties to Hibernate/JPA configuration file of your project:

<property name="hibernate.c3p0.min_size" value="5" />
<property name="hibernate.c3p0.max_size" value="20" />
<property name="hibernate.c3p0.timeout" value="300" />

Let me explain these properties:

• hibernate.c3p0.min_size: the minimum number of connections maintained in the pool at any given time.
• hibernate.c3p0.max_size: the maximum number of connections maintained in the pool at any given time.
• hibernate.c3p0.timeout: the number of seconds an idle connection is kept in the pool. If a connection is idle longer than this timeout value, then it will be replaced by a new one.

So that means you have to set the value of hibernate.c3p0.timeout less than the wait_timeout value on MySQL server. And the value 300 seconds in the above example is pretty good. This definitely solves the problem because the pool maintains no connections that are idle longer than the MySQL server’s timeout value.

And c3p0 can be used in production to replace Hibernate’s default internal pool.

Other Hibernate Tutorials:

• How to use c3p0 database connection pooling library with Hibernate/JPA
• How to configure Proxool with Hibernate/JPA
• Java Hibernate JPA Annotations Tutorial for Beginners
• Hibernate Hello World Tutorial for Beginners with Eclipse and MySQL
• Hibernate One-to-One Association on Primary Key Annotations Example
• Hibernate One-to-Many Using Join Table XML Mapping Example
• Hibernate Many-to-Many Association with Extra Columns in Join Table Example
• Hibernate Enum Type Mapping Example
• Hibernate Binary Data and BLOB Mapping Example
• Hibernate Basics — 3 ways to delete an entity from the datastore
• Hibernate Query Language (HQL) Example
• Java Hibernate Reverse Engineering Tutorial with Eclipse and MySQL

Environment

HikariCP version:        2.7.3
JDK version:             1.8.0_121
Database:                MySQL
Driver version:          x.x.x
Hibernate version:       5.1.10.Final
Tomcat version:          8.5.23

After a few hours running, my application stop working. I’m getting a few leak detections, but with no success figuring out how to fix it.

The error I get when stop working is:

ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - HikariPool-1 - Connection is not available, request timed out after 30004ms.
javax.persistence.PersistenceException: 

org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

Also, I’m getting a lot of logs when I perform any SELECT/UPDATE/INSERT (when showing a list, for example, there’s 120x):

1985837 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.pool.ProxyConnection - HikariPool-1 - Executed rollback on connection com.mysql.jdbc.JDBC4Connection@438b4b42 due to dirty commit state on close().

Active pool’s only increases (total=27, active=22, idle=5, waiting=0).

HikariCP Startup log:

883 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
887 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
888 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................false
889 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
890 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
891 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
892 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
893 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
894 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
897 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
900 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{cacheServerConfiguration=true, useServerPrepStmts=true, cachePrepStmts=true, rewriteBatchedStatements=true, cacheResultSetMetadata=true, useLocalSessionState=true, maintainTimeStats=false, prepStmtCacheSize=350, prepStmtCacheSqlLimit=2048, password=<masked>}
902 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName................."com.mysql.jdbc.Driver"
903 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
904 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
905 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................740000
905 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailFast..........true
906 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
907 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
908 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - jdbc4ConnectionTest.............false
909 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:mysql://localhost/mydb?autoReconnect=true&useSSL=false
909 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........60000
910 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1740000
910 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................200
911 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
912 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
912 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................5
913 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
914 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
918 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
920 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
920 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
921 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutorService........internal
922 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
922 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
922 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
924 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - username........................"myuser"
926 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
931 [http-nio-8080-exec-5] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
1483 [http-nio-8080-exec-5] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@40750294
1486 [http-nio-8080-exec-5] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
1520 [http-nio-8080-exec-5] INFO  org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect

Hibernate persistence.xml configuration:

<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect"/>
<property name="hibernate.connection.autocommit" value="false" />

<property name="hibernate.show_sql" value="false" />
<property name="hibernate.format_sql" value="false"/>
<property name="hibernate.connection.provider_class" value="org.hibernate.hikaricp.internal.HikariCPConnectionProvider" />

<property name="hibernate.hikari.dataSource.prepStmtCacheSize" value="350" />
<property name="hibernate.hikari.dataSource.prepStmtCacheSqlLimit" value="2048" />
<property name="hibernate.hikari.dataSource.cachePrepStmts" value="true" />
<property name="hibernate.hikari.dataSource.useServerPrepStmts" value="true" />
<property name="hibernate.hikari.dataSource.useLocalSessionState" value="true" />
<property name="hibernate.hikari.dataSource.useLocalTransactionState" value="true" />
<property name="hibernate.hikari.dataSource.rewriteBatchedStatements" value="true" />
<property name="hibernate.hikari.dataSource.cacheResultSetMetadata" value="true" />
<property name="hibernate.hikari.dataSource.cacheResultSetMetadata" value="true" />
<property name="hibernate.hikari.dataSource.cacheServerConfiguration" value="true" />
<property name="hibernate.hikari.dataSource.elideSetAutoCommits" value="true" />
<property name="hibernate.hikari.dataSource.maintainTimeStats" value="false" />

<!--
	MySQL:  
	interactive_timeout = 7200
	wait_timeout = 1800
	max_connections = 200
	
	idleTimeout, maxLifetime should be 1min less than wait_timeout = 1740s or 1740000ms
 --> 

<property name="hibernate.hikari.minimumIdle" value="5" />
<property name="hibernate.hikari.maximumPoolSize" value="200" /> 
<property name="hibernate.hikari.idleTimeout" value="740000" /> 
<property name="hibernate.hikari.maxLifetime" value="1740000" /> 
<property name="hibernate.hikari.leakDetectionThreshold" value="60000" /> 

<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost/mydb?autoReconnect=true&amp;useSSL=false" />
<property name="javax.persistence.jdbc.user" value="myuser" />
<property name="javax.persistence.jdbc.password" value="mypassword" />

HikariCP housekeeper log:

1621219 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Before cleanup stats (total=27, active=22, idle=5, waiting=0)
1621220 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - After cleanup  stats (total=37, active=33, idle=4, waiting=0)

MySQL thrads

Threads_connected:	22
Threads_created:	29
Threads_running:	2

Hikari Leak detection log:

295269 [HikariPool-1 housekeeper] WARN  com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for com.mysql.jdbc.JDBC4Connection@64f645d5 on thread http-nio-8080-exec-9, stack trace follows
java.lang.Exception: Apparent connection leak detected
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:85)
    at org.hibernate.hikaricp.internal.HikariCPConnectionProvider.getConnection(HikariCPConnectionProvider.java:77)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:386)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:84)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:109)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:47)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.prepareQueryStatement(AbstractLoadPlanBasedLoader.java:241)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:185)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:121)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
    at org.hibernate.loader.collection.plan.AbstractLoadPlanBasedCollectionInitializer.initialize(AbstractLoadPlanBasedCollectionInitializer.java:88)
    at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:688)
    at org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:75)
    at org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:2004)
    at org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:567)
    at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:249)
    at org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:563)
    at org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:132)
    at org.hibernate.collection.internal.AbstractPersistentCollection$1.doWork(AbstractPersistentCollection.java:161)
    at org.hibernate.collection.internal.AbstractPersistentCollection$1.doWork(AbstractPersistentCollection.java:146)
    at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:249)
    at org.hibernate.collection.internal.AbstractPersistentCollection.readSize(AbstractPersistentCollection.java:145)
    at org.hibernate.collection.internal.PersistentBag.size(PersistentBag.java:261)
    at javax.faces.model.ListDataModel.isRowAvailable(ListDataModel.java:110)
    at javax.faces.model.ListDataModel.setRowIndex(ListDataModel.java:185)
    at javax.faces.model.ListDataModel.setWrappedData(ListDataModel.java:220)
    at javax.faces.model.ListDataModel.<init>(ListDataModel.java:79)
    at org.primefaces.component.api.UIData.getDataModel(UIData.java:583)
    at javax.faces.component.UIData.isRowAvailable(UIData.java:343)
    at org.primefaces.component.api.UIData.setRowModel(UIData.java:418)
    at org.primefaces.component.api.UIData.setRowIndex(UIData.java:397)
    at org.primefaces.component.api.UIData.visitTree(UIData.java:629)
    at javax.faces.component.UIComponent.visitTree(UIComponent.java:1623)
    at javax.faces.component.UIComponent.visitTree(UIComponent.java:1623)
    at javax.faces.component.UIComponent.visitTree(UIComponent.java:1623)
    at javax.faces.component.UIComponent.visitTree(UIComponent.java:1623)
    at javax.faces.component.UIForm.visitTree(UIForm.java:371)
    at javax.faces.component.UIComponent.visitTree(UIComponent.java:1623)
    at javax.faces.component.UIComponent.visitTree(UIComponent.java:1623)
    at com.sun.faces.context.PartialViewContextImpl.processComponents(PartialViewContextImpl.java:376)
    at com.sun.faces.context.PartialViewContextImpl.processPartial(PartialViewContextImpl.java:297)
    at javax.faces.context.PartialViewContextWrapper.processPartial(PartialViewContextWrapper.java:183)
    at javax.faces.component.UIViewRoot.encodeChildren(UIViewRoot.java:981)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1779)
    at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:391)
    at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:125)
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:121)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:594)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)

Some code that I use:

Business object layer

@Transactional(readOnly = true)
public List<Person> getPersons() {
    if (persons == null) {
        try {
            persons = new PersonBO().listAll(Person.class);
        } catch (Exception e) {
            getLog().error("Erro", e);
        }
    }
    return persons;
}

DAO Layer

@Transactional(readOnly = true)
public List<E> listAll(Class<E> classEntity) throws Exception {
    return list(createQuery("SELECT o FROM " + classEntity.getSimpleName() + " o"));
}

@Transactional(readOnly = true)
public List<E> list(Query query) throws Exception {
    List<E> entities = null;
    if (query != null) {
        try {
            entities = query.getResultList();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }
    return entities;
}

@Transactional(readOnly = false)
private Query createQuery(String jpql) {
    Query query = null;
    if (jpql != null && !jpql.isEmpty()) {
        query = entityManager.createQuery(jpql);
    }
    return query;
}

на здоровье )
1. сбрось скрины на котором было бы видно что запущен сам сервер базы данных и активен, и покажи какие там таблицы есть, у меня cosmoport и test из того что имеет отношение.

2. Сбрось скриншот И доложи как там насчет проверки конекшена в самой Идее (Как я писал через добавление дата сорс.

3. И формулируй инфо полно!

Кстати в Data sourses работает только с драйвером для MySql 5.1.

кто находится в Дата сорс и кто работает только с драйверами для 5,1?

У тебя на скрине просто с mysql.

что именно на скрине просто с mysql?

не сокращай и не выбрасывай слова с фраз ) это очень важно, все подобные проблемы как у тебя, это все исключительно внимательность, важно ничего не пропускать и не перепрыгывать.

4. На счет 404 не понятно.

7. Запустить приложение. В браузере откроется стартовая страница, на
которой ты увидишь готовый интерфейс приложения, но он не
работает пока на сервере нет соответствующего функционала. По
мере реализации проекта, интерфейс будет правильно отображать
данные и отправлять запросы на редактирование, удаление и
создание кораблей.
8. Дописать нужный функционал (см. Задание на

То есть после выполнения пунктов мануала по настройке окружения и ПЕРЕД реализацией контроллера у тебя в браузере должна была быть страничка с интерфейсом.
Верно?
Если пытаться делать ГЕТ запросы это понятно, но мы говорим о том, что ты выполнил все пункты до 6 включительно, и на 7-ом пункте у тебя открылась нормально страничка с интерфейсом.