Unable to acquire jdbc connection ошибка - Решение и исправление самых разных ошибок на TopOshibok.ru

Details: Written by; Last Updated on 17 July 2019 | Print Email

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.

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Add comment

Источник

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.

Источник

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;
}

Источник

Issue

My Spring Boot works fine when I generate report using Japser report.

The problem I face is that the application throws a hibernate exception :

Unable to acquire JDBC Connection

I get this error after I generate report many times .

1 running delayed actions on {type: MASTER, group: null, band: 0}
2018-09-20 14:27:55.536 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.fill.JRBaseFiller           : Fill 1: ended
2018-09-20 14:27:55.536 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.fill.JRFillDataset          : Fill 1: closing query executer
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block DEVANAGARI
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block BENGALI
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block TELUGU
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block TAMIL
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block GUJARATI
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block KANNADA
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block MALAYALAM
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block ORIYA
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block GURMUKHI
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block SINHALA
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block TIBETAN
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block KHMER
2018-09-20 14:28:25.549  WARN 46148 --- [ XNIO-2 task-27] 
o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null
2018-09-20 14:28:25.550 ERROR 46148 --- [ XNIO-2 task-27] 
o.h.engine.jdbc.spi.SqlExceptionHelper   : HikariPool-1 - Connection is not 
available, request timed out after 30000ms.
2018-09-20 14:28:25.556 ERROR 46148 --- [ XNIO-2 task-27] 
c.n.xx.aop.logging.LoggingAspect    : Exception in 
com.xx.xx.web.rest.GrueResource.generateRapportGrue() with cause = 
'org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC 
Connection' and exception = 'Could not open JPA EntityManager for 
transaction; nested exception is 
org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC 
Connection'

org.springframework.transaction.CannotCreateTransactionException: Could not 
open JPA EntityManager for transaction; nested exception is 
org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC 
Connection

Solution

You run out of connections.

Try to set the Hikari Connection Pool to a bigger number:

spring.datasource.hikari.maximum-pool-size=10

Answered By — Simon Martinelli
Answer Checked By — Willingham (JavaFixing Volunteer)

Источник

Details: Written by; Last Updated on 17 July 2019 | Print Email

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
…

So why is JDBCConnectionException 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:

Increasing the server configured values for client timeouts:

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

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.

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

About the Author:

Add comment

Содержание

[Solved] JDBCConnectionException: Unable to acquire JDBC Connection with Hibernate and MySQL
So why is JDBCConnectionException thrown?
Solutions to fix JDBCConnectionException
A solution that really works to solve JDBCConnectionException problem:
Other Hibernate Tutorials:
About the Author:
Невозможно приобрести соединение JDBC при тестировании интеграции при использовании сети моста Docker
Не удалось получить соединение JDBC в тесте интеграции при использовании сети моста Docker
JDBCConnectionException «Невозможно получить соединение JDBC» при загрузке Spring

[Solved] JDBCConnectionException: Unable to acquire JDBC Connection with Hibernate and MySQL

So why is JDBCConnectionException thrown?

Solutions to fix JDBCConnectionException

Let’s consider each suggestion mentioned above.

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_timeout value 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’

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

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

A solution that really works to solve JDBCConnectionException problem:

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

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

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

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.

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

Невозможно приобрести соединение JDBC при тестировании интеграции при использовании сети моста Docker

Когда я запускаю maven test локально передается. Но получил эту ошибку, когда я запустил ее на сервере CI.

При запуске локального теста все они передаются, используется настройка по умолчанию maven, предоставленная IntelliJ IDEA.
Поскольку ошибка связана с подключением к базе данных, поэтому я проверил Jenkins Audit to Database Plugin. Успешное соединение!

Параметр подключения в моем application.properties также соответствует этому параметру

MySQL в URL-адресе – это имя контейнера док-станции MySQL. Если изменить его с помощью localhost или private IP в docker container inspect mysql докеров, docker container inspect mysql то сообщение об ошибке будет таким же, а Stacktrace немного отличается на последних двух строках.

для локального хоста

для частного IP-адреса

По-разному, я считаю, что хост в URL-адресе, localhost используется для локального теста. В то время как сервер Jenkins использовал сеть моста Докер.

Когда я запускаю тест JUnit в IntelliJ, он иногда не работает в локальной среде. Журнал ошибок выглядит так:

Я искал проблему, он сказал, что по умолчанию база данных использует верхний регистр базы данных. После запуска maven test эта проблема будет идти, если снова запустить тест JUnit в среде IDE. Но это не должно быть связано с основной причиной.

Найдите в сообщении об ошибке, найдите аналогичный вопрос, но с другим вложенным исключением:

Все они относятся к nested exception is javax.persistence.PersistenceException
Но nested exception is org.hibernate.exception.JDBCConnectionException: это моя ситуация. Чтение подключения Java к базе данных MySQL
однако, поскольку этот плагин подключается к OK, означает, что соединение с контейнером Jenkins в контейнере MySQL прекрасное.

Суммировать:
1. Местное тестирование с maven прошло
2. Плагин Jenkins подключается к успеху MySQL.
3. Тест интеграции не работает при запуске от Jenkins
4. локальная тестовая среда WIN10 64bit; Дженкинс работает в контейнере докеров на сервере Ubuntu 16.04 64 бит, а контейнер MySQL 5.7 подключается к одной и той же сети мостов.

вы должны привязать порт mysql контейнера докеров к порту в VM.

Это хорошо объяснено в нижнем потоке.

Благодаря @rohit-thomas. Мы сузим вопрос к тому, что связано с хостом URL-адреса.
Простым ответом является изменение хоста URL-адреса JDBC в весеннем загрузочном application.properties на IP-адрес хоста докера. От
spring.datasource.url=jdbc:mysql://mysql:3306/database?
в
spring.datasource.url=jdbc:mysql://172.17.0.1:3306/database?

Мой вывод:
Контейнер Jenkins, созданный из образа, способен связываться с контейнером MySQL с его именем контейнера или частным адресом в сети докеревого моста. Однако, поскольку приложение, созданное Jenkins, не может этого сделать. Поскольку контейнерный порт MySQL привязан к хост-машине, приложение может связываться с контейнером MySQL через порт хоста.

Если вывод неверен, комментарии приветствуются.

Некоторые вещи, которые вы можете проверить, могут помочь вам решить эту проблему.

В application.properties попытайтесь использовать IP-адрес хоста-докерера.

Примечание. Вам нужно будет сопоставить свой IP-порт и порт, когда вы выполняете запуск докеров или порты в файле докеров. И используйте одну и ту же сеть докеров среди ваших контейнеров.

Проверьте, может ли серверное приложение достигнуть вашего mysql или наоборот. Зайдите внутрь контейнера докеров и попробуйте выполнить ping.

Источник

Не удалось получить соединение JDBC в тесте интеграции при использовании сети моста Docker

когда я запускаю maven test локально передается. Но получил эту ошибку, когда я запускаю ее на сервере CI.

при запуске локального теста все они прошли, используется настройка теста maven по умолчанию, предоставляемая IntelliJ IDEA.
Поскольку ошибка жалуется на подключение к базе данных, поэтому я проверил Jenkins Audit на плагин базы данных. Соединение Успешно!

параметр подключения в my application.properties также соответствует этому

MySQL в URL-адресе является именем контейнера MySQL docker. Если изменить его с localhost или частный IP в docker container inspect mysql сообщение об ошибке то же самое, в то время как Stacktrace немного отличается на последних двух строках.

для частного IP

другой, я думаю, является хостом в URL, localhost используется для локального теста. В то время как сервер Jenkins использовал сеть Docker bridge.

в состояние контейнера:

когда я запускаю тест JUnit в IntelliJ, он иногда терпит неудачу в локальной среде. Журнал ошибок выглядит так:

Я искал проблему, он сказал, что база данных h2 использует верхний регистр по умолчанию. После бега maven test , эта проблема будет идти, если снова запустить тест JUnit в IDE. Но это не должно быть связано с первопричиной.

поиск в сообщении об ошибке найдите аналогичный вопрос, но с другим вложенным исключением:

не удалось открыть JPA EntityManager для транзакции; вложенным исключением является javax.стойкость.PersistenceException

SpingREST: не удалось открыть JPA EntityManager для транзакции; вложенным исключением является org.hiberna

не удалось открыть JPA EntityManager для транзакции; org.зимовать.исключение.GenericJDBCException: не удалось открыть соединение

не удалось открыть JPA EntityManager для транзакции весной

все они-о nested exception is javax.persistence.PersistenceException
Но!—12—> моя ситуация. Читать подключение Java к базе данных MySQL
однако, поскольку этот плагин подключается нормально, означает, что соединение из контейнера Jenkins с контейнером MySQL в порядке.

подведем итог:
1. локальный тест с Maven прошел
2. Плагин Jenkins подключается к MySQL успех
3. Интеграционный тест не выполняется при запуске из Jenkins
4. локальная тестовая среда WIN10 64bit; Дженкинс работает в контейнере docker на Ubuntu 16.04 64bit server, с MySQL 5.7 контейнер подключается к той же сети моста.

Источник

JDBCConnectionException «Невозможно получить соединение JDBC» при загрузке Spring

Моя Spring Boot отлично работает, когда я создаю отчет с использованием отчета Japser.

Проблема, с которой я сталкиваюсь, заключается в том, что приложение выдает исключение спящего режима:

Unable to acquire JDBC Connection

Я получаю эту ошибку после многократного создания отчета.

У вас заканчиваются связи.

Попробуйте установить для пула подключений Hikari большее число:

Это зависит от того, как долго выполняется ваш запрос, чтобы получить данные для отчетов. Так что вам следует это оптимизировать. В конце вы можете иметь столько подключений к базе данных, сколько позволяет ваша база данных.

О подключении к базе данных, при генерации любого отчета создается новый экземпляр DataSource или он тот же?

Hikari — это пул подключений. Поэтому он пытается повторно использовать соединения.

Итак, ваше решение требует одновременного приема 10 различных подключений или означает принятие 10 экземпляров одного и того же объекта DataSource?

Вы смешиваете вещи. Источник данных отражает только конфигурацию соединения с базой данных. А соединение — это физическое соединение с базой данных. Таким образом, пул будет содержать не более 10 подключений.

Спасибо за ваше объяснение. Мне это помогло. Но значение по умолчанию для максимального размера пула — 10. Мне просто нужно поставить большее число.

Насколько вы его увеличили? вы следовали какой-либо метрике для увеличения размера пула?

Согласно Официальная статья Хикари формула размера пула — connections = ((core_count * 2) + effective_spindle_count)

Я сталкивался с одними и теми же проблемами каждый второй день, когда работал с отчетами из яшмы, и, наконец, исправил их, правильно поняв, потому что, когда мы работаем с отчетами на основе запросов, мы несем ответственность за закрытие подключения к нашему собственному источнику данных, чтобы он вернулся в пул и доступен для следующего использования. Вы должны позаботиться о нескольких вещах 1- получить соединение от источника данных

2-Вы должны закрыть соединение с источником данных в любом случае: лучше закрыть его в наконец заблокировать, чтобы в случае исключения соединение не оставалось открытым.

Источник

Platform Notice: Data Center Only — This article only applies to Atlassian products on the data center platform.

Summary

An error message appears when trying to access Confluence:

 Database is being updated by an instance which is not part of the current cluster.

Environment

Diagnosis

When accessing Confluence, some nodes will throw errors in the logs, making the application inaccessible. When looking at the atlassian-confluence.log, will find entries like below:

atlassian-confluence.log

2021-05-25 01:50:42,150 WARN [http-nio-8090-exec-9 url: /status] [confluence.util.profiling.DefaultActivityMonitor] close Exceeded the threshold of 60000 ms: ActivitySnapshot{startTime=1621921699643, threadId=403, threadName='http-nio-8090-exec-9 url: /status', userId='<unknown>', type='web-request', summary='/status'}
 -- url: /status | traceId: 3990689cfb713b1a | userName: anonymous
2021-05-25 01:50:42,151 WARN [http-nio-8090-exec-59 url: /status] [confluence.util.profiling.DefaultActivityMonitor] close Exceeded the threshold of 60000 ms: ActivitySnapshot{startTime=1621921701223, threadId=13882, threadName='http-nio-8090-exec-59 url: /status', userId='<unknown>', type='web-request', summary='/status'}
 -- url: /status | traceId: 98643738cbe68254 | userName: anonymous
2021-05-25 01:50:42,154 WARN [http-nio-8090-exec-13 url: /status] [confluence.util.profiling.DefaultActivityMonitor] close Exceeded the threshold of 60000 ms: ActivitySnapshot{startTime=1621921736283, threadId=411, threadName='http-nio-8090-exec-13 url: /status', userId='<unknown>', type='web-request', summary='/status'}
 -- url: /status | traceId: e9b11240652a7c46 | userName: anonymous
2021-05-25 01:50:42,154 WARN [http-nio-8090-exec-69 url: /status] [confluence.util.profiling.DefaultActivityMonitor] close Exceeded the threshold of 60000 ms: ActivitySnapshot{startTime=1621921781252, threadId=25435, threadName='http-nio-8090-exec-69 url: /status', userId='<unknown>', type='web-request', summary='/status'}
 -- url: /status | traceId: e2396d2c8415c734 | userName: anonymous
2021-05-25 01:50:42,156 WARN [http-nio-8090-exec-46 url: /status] [confluence.util.profiling.DefaultActivityMonitor] close Exceeded the threshold of 60000 ms: ActivitySnapshot{startTime=1621921721253, threadId=623, threadName='http-nio-8090-exec-46 url: /status', userId='<unknown>', type='web-request', summary='/status'}
 -- url: /status | traceId: 0eba7f5425983dab | userName: anonymous
2021-05-25 01:50:42,158 WARN [http-nio-8090-exec-68 url: /status] [confluence.util.profiling.DefaultActivityMonitor] close Exceeded the threshold of 60000 ms: ActivitySnapshot{startTime=1621921779637, threadId=25434, threadName='http-nio-8090-exec-68 url: /status', userId='<unknown>', type='web-request', summary='/status'}
 -- url: /status | traceId: 20f56684c4c282c3 | userName: anonymous
2021-05-25 01:50:42,162 WARN [http-nio-8090-exec-11 url: /status] [confluence.util.profiling.DefaultActivityMonitor] close Exceeded the threshold of 60000 ms: ActivitySnapshot{startTime=1621921733614, threadId=408, threadName='http-nio-8090-exec-11 url: /status', userId='<unknown>', type='web-request', summary='/status'}
 -- url: /status | traceId: 37fdb3f28d2429ed | userName: anonymous
2021-05-25 01:51:01,158 WARN [hz.confluence.cached.thread-30] [internal.cluster.impl.ClusterHeartbeatManager] log [142.136.96.235]:5801 [CONFLUENCE_APP_NCW] [3.11.6] Suspecting Member [142.136.96.236]:5801 - 1a46fbe9-da3a-4469-be93-e2250c7a73f5 because it has not sent any heartbeats since 2021-05-25 01:50:00.516. Now: 2021-05-25 01:51:01.158, heartbeat timeout: 60000 ms, suspicion level: 1.00

Following that, the error below will be spammed in the application logs, until the connection with the database is re-established.

atlassian-confluence.log

2021-05-25 04:25:23,567 WARN [Caesium-1-4] [impl.schedule.caesium.JobRunnerWrapper] runJob Scheduled job EhCacheCompactionJob#EhCacheCompactionJob completed unsuccessfully with response JobRunnerResponse[runOutcome=FAILED,message='CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is net.sf.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
GenericJDBCException: Unable to acquire JDBC Connection
SQLException']

Cause

This is caused when a network glitch or maintenance happens, breaking the communication between the confluence server and the database.

When in a clustered instance, after the nodes re-establish the communication with the database, only the first node will be recognized as part of the cluster, and all of the others will be in a database state conflict, making them not be recognized as valid and part of the current data center connected to it.

Solution

Restart all impacted nodes, so the cache on the nodes is cleared and the connection with the database is re-established.

Источник

So why is JDBCConnectionException thrown?

Solutions to fix JDBCConnectionException

A solution that really works to solve JDBCConnectionException problem:

Other Hibernate Tutorials:

About the Author:

Add comment

Environment

Issue

Solution

So why is JDBCConnectionException thrown?

Solutions to fix JDBCConnectionException

A solution that really works to solve JDBCConnectionException problem:

Other Hibernate Tutorials:

About the Author:

Add comment

[Solved] JDBCConnectionException: Unable to acquire JDBC Connection with Hibernate and MySQL

So why is JDBCConnectionException thrown?

Solutions to fix JDBCConnectionException

A solution that really works to solve JDBCConnectionException problem:

Other Hibernate Tutorials:

Невозможно приобрести соединение JDBC при тестировании интеграции при использовании сети моста Docker

Не удалось получить соединение JDBC в тесте интеграции при использовании сети моста Docker

JDBCConnectionException «Невозможно получить соединение JDBC» при загрузке Spring

Summary

Environment

Diagnosis

Cause

Solution