Ошибка 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

Вернуться в основную статью

Для облегчения поиска ошибок был создан отдельный раздел для сбора ошибок из АРМ ЭЛН, читайте внимательнее подсказки ниже:

В случае возникновения ошибки «ERR_SIGN Некорректная подпись головной организации: Ошибка при проверке сертификата. VALID_SIGNATURE ЭП действительна; При проверке сертификата ЭП произошла ошибка. Не удалось найти/получить доступ к списки отозванных сертификатов УЦ. Обратитесь в службу поддержки ФСС

Скачать данный файл — https://disk.yandex.ru/d/HoOPJ5WPC097RQ

Кликнуть правой кнопкой мыши по нему — Установить список отзыва

В случае возникновения ошибки при установке или обновлении программы «GostCryptography.dll Этому файлу не сопоставлена программа для выполнения этого действия»

Необходимо обновить систему и выполнить команды в командной строке от имени администратора:

sfc /scannow

и

DISM /Online /Cleanup-Image /RestoreHealth

После этого необходимо перезагрузиться

Подробнее вы можете прочитать здесь

В случае возникновения «Internal Error COMCryptoAPIClient» :

В командной строке CMD выполнить (с правами администратора):

cd C:FssTools

— для 32 бита:

C:WindowsMicrosoft.NETFrameworkv4.0.30319RegAsm.exe /registered C:FssToolsGostCryptography.dll

— для 64 бита:

C:WindowsMicrosoft.NETFramework64v4.0.30319RegAsm.exe /registered C:FssToolsGostCryptography.dll

В случае возникновения ошибки «Сообщение не соответствует формату XML Encryption»

В меню Администрирование – Настройки сервисов ФСС – Строка соединения укажите следующий адрес сервиса:

https://eln.fss.ru/WSLnCryptoV20/FileOperationsLnService?WSDL

Далее в меню Администрирование – Настройка подписей для сервисов установите галку «Шифровать сообщение». После этого Вам необходимо указать Имя сертификата ФСС и Тип контейнера.

В случае возникновения ошибки «Software caused connection abort: recv failed»

Обычно данная ошибка возникает при бездействии. Когда сервер автоматически закрывает соединение через некоторое время, а клиент получает данную ошибку в ответ на запрос
В иных случаях соединение может прерываться из-за перезапуска СУБД на сервере, когда клиент пытается запрашивать данные по уже несуществующим соединениям

В случае возникновения ошибки «HibernateException: Collection is not associated with any session

Данная ошибка обозначает, что в БД АРМ ЛПУ сохранены строки с одинаковым номером ЭЛН.
Необходимо войти в PGAdmin по пути: C:postgresqlbin
Исполняемый файлpgAdmin3.exe

Пароль для пользователя Postgres — Manager1
и на схеме public выполнить запрос:

WITH t AS (
SELECT ln_code, COUNT(1) FROM public.fc_eln_data_history
GROUP BY ln_code
HAVING COUNT(1) > 1)
SELECT * FROM public.fc_eln_data_history
WHERE ln_code IN (SELECT ln_code FROM t);

Этот запрос выведет строки с одинаковыми номерами ЭЛН. Затем необходимо будет удалить ошибочную строку:
delete from public.fc_eln_data_history where id = ‘ваш id неверной строки’;

Если данный способ не работает и после обновления ПО ошибка повторяется

Необходимо выполнить запрос:

ALTER TABLE public.fc_eln_data_history ADD CONSTRAINT unique_lncode UNIQUE (ln_code);

Это ограничение запрещает создавать в таблице public.fc_eln_data_history строки с одинаковым значением ln_code

В случае возникновения ошибки «В базе данных АРМ ЛПУ имеется некорректная запись» (Transaction already active)

Данная ошибка возникает в случае, когда в какой либо таблице БД приложения сохранены некорректные записи

Для исправления нужно удалить из БД приложения неверную строку (такие записи можно удалить только вручную).

Необходимо подключиться к серверу базы данных PostgreSQL, найти и удалить из базы ошибочную строку. При установке АРМ ЛПУ, вместе с БД и компонентами PostgreSQL устанавливается клиент для подключения к БД. По умолчанию находится здесь: C:postgresqlbinpgAdmin3.exe

В интерфейсе клиента открывается сервер PostgreSQL 9.5. Затем открывается схема fss (пользователь fss, пароль fss) – Схемы – public – Таблицы.

Данные в АРМ ЛПУ хранятся в таблицах:
fc_eln_data_history — данные листков нетрудоспособнсти;

fc_eln_periods — сведения о периодах нетрудоспособности;

ref_ln_numbers — список запрошенных номеров ЭЛН.

Для просмотра таблицы необходимо выделить ее в дереве таблиц и нажать на значок «Просмотр данных в выбранном объекте»
Выделяете и удаляете (delete) строку, которая содержит пустое значение номера ЭЛН или другие ошибки.
Как вариант, для поиска и удаления ошибочных записей возможно использование SQL запроса типа:
select id from fc_eln_data_history where ln_code is null;
delete from fc_eln_data_history where id = ваш id;
Для открытия окна SQL запросов необходимо в главном меню нажать на значок «SQL».

Обратите внимание! При удалении строки ЭЛН, если в этом ЭЛН были созданы периоды нетрудоспособности, сначала необходимо удалить их. Периоды нетрудоспособности хранятся в отдельной таблице fc_eln_periods и связаны с fc_eln_data_history по номеру ЭЛН. Просмотр и удаление периодов аналогично, описанному выше.

В случае возникновения ошибки «Unable to acquire JDBC Connection»

Проблема связана с неработоспособностью сервисов ФСС, необходимо ожидать восстановления

В случае возникновения ошибки «Ошибка вызова сервиса передачи/получения данных. ЛПУ НЕ НАЙДЕН В СПРАВОЧНИКЕ»

Необходимо взять документы и лицензии МО, обратиться (подойти на приём) в территориальный орган Фонда по месту осуществления деятельности. Сотрудники ТОФ внесут МО в соответствующие справочники, после чего МО сможет формировать ЭЛН.

В случае возникновения ошибки «Ошибка вызова сервиса передачи/получения данных. Unmarchalling error: cvc-complex-type.2.4.a: Invalid content was found starting with element»

Для устранения ошибки необходимо снять галочку с постановки на учёт в ранние сроки Также необходимо обновить программу.

В случае возникновения ошибки «Ошибка при проверке сертификата. VALID_SIGNATURE ЭП действительна; При проверке сертификата ЭП произошла ошибка. Ошибка построения цепочки сертификатов. Не найден сертификат Удостоверяющего центра, указанный в сертификате пользователя

Необходимо переустановить ВСЮ цепочку сертификатов уполномоченного лица ФСС

В случае возникновения ошибки «ЭЛН с номером, указанным в поле «Продолжение ЭЛН» не закрыт»

Необходимо
закрыть предыдущий ЭЛН – невозможно отправить на сервис ЭЛН-продолжение, не
закрыв при этом предыдущий ЭЛН

В случае возникновения ошибки «Значение поля (групп полей) отличается от существующего значения»

Ошибка сообщает что невозможно внести изменения в ранее успешно отправленные данные ЭЛН. Понятие
«Группа полей» подразумевает некую неделимую целостность полей в ЭЛН,
например, если при открытии ЭЛН были успешно отправлены значения «Фамилия» и
«Имя», при продлении в эту группу полей невозможно будет добавить «Отчество».
Также невозможно исправить или дополнить ранее отправленные данные по периоду
нетрудоспособности, например, в ранее отправленный период добавить подпись
Председателя ВК;

В случае возникновения ошибки «Направленные данные ЭЛН уже присутствуют в системе»

Вы пытаетесь отправить данные, которые уже присутствуют в системе

В случае возникновения ошибки при запуске программы «Invalid Configuration Location» The configuration area at .. could not be created. Please choose a writable location using the ‘-configuration’ command line option

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

В случае возникновения ошибки «Количество новых периодов не должно превышать 1»

Ошибка говорит о том, что вы пытаетесь отправить несколько периодов. За один раз можно
отправить только один период нетрудоспособности

Всем, кому понравился или помог это проект — Вы можете помочь ему развиваться материально:
Donate (помощь проекту)

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

We are using c3p0 connection pool, Following are the setting

<bean id="dataSource"  
  class="com.mchange.v2.c3p0.ComboPooledDataSource">  
    <property name="driverClass" value="${jdbc.driver}" />  
    <property name="jdbcUrl" value="${jdbc.url}" />
    <property name="user" value="${jdbc.username}" />  
    <property name="password" value="${jdbc.password}" />  
    <property name="initialPoolSize"><value>3</value></property>
    <property name="minPoolSize"><value>25</value></property>
    <property name="maxPoolSize"><value>200</value></property>
    <property name="idleConnectionTestPeriod"><value>200</value></property>
    <property name="acquireIncrement"><value>25</value></property> <!--  -->
    <property name="maxStatements"><value>0</value></property>  <!-- 0 means: statement caching is turned off.  -->
    <property name="numHelperThreads"><value>3</value></property>  <!-- 3 is default --> 
    <property name="acquireRetryAttempts"><value>3</value></property>  
    <property name="maxIdleTime"><value>3600</value></property>    
    <property name="maxConnectionAge"><value>7200</value></property>    
    <property name="maxIdleTimeExcessConnections"><value>600</value></property>    
    <property name="preferredTestQuery"><value>select 1 from DUAL</value></property> 
    <property name="testConnectionOnCheckin"><value>true</value></property>    
    <property name="testConnectionOnCheckout"><value>false</value></property>    
    <property name="connectionCustomizerClassName"><value>com.common.ConnectionPoolLogger</value></property>    

 </bean>

We are getting following exception

org.hibernate.exception.JDBCConnectionException: could not extract 
ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
Communications link failure The last packet successfully 
received from the server was 4,314 milliseconds ago. The last packet sent successfully to the server was 168 milliseconds ago