Springboot Application fails on DB restart

Question

We have developed a Springboot Java application and I put it on a docker container for development environment.

The Spring boot version is 1.5.6

The DB used is SQL server 2016 which is again on docker ( windows container).

Issue: Whenever I restart the SQL DB container, the application starts giving this error.

2018-10-29 16:00:08,993 ERROR pool-13-thread-1  org.springframework.scheduling.support.TaskUtils$LoggingErrorHandler - Unexpected error occurred in scheduled task.
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
        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.$Proxy155.findOne(Unknown Source)
        at au.com.outware.swepad.tasks.WorkOrderManagerTask.taskFetchAndProcessWorkOrderDetails(WorkOrderManagerTask.java:63)
        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.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
        at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
        at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: javax.persistence.PersistenceException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
        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)
        ... 30 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:206)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:724)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(SQLServerConnection.java:2615)
        at sun.reflect.GeneratedMethodAccessor163.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
        at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
        at com.sun.proxy.$Proxy102.setAutoCommit(Unknown Source)
        at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:67)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:238)
        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)
        ... 32 common frames omitted
2018-10-29 16:00:08,995 WARN pool-30-thread-1  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: null
2018-10-29 16:00:08,996 ERROR pool-30-thread-1  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - The connection is closed.
2018-10-29 16:30:00,017 INFO pool-13-threa

The only way to get applicaiton working is doing the application restart ( container restart)

We searched on forums and set this property in our application.yml.

spring.datasource.tomcat.testOnBorrow=true
spring.datasource.tomcat.validation-query=SELECT 1

Our applicaiton yml looks like shown below.

spring:
  datasource:
    type: org.apache.tomcat.jdbc.pool.XADataSource
    tomcat:
      test-on-borrow: true
      validation-query: select 1
      validation-interval: 30000
  jpa:
    database: sql_server
    database-platform: org.hibernate.dialect.SQLServer2012Dialect
    generate-ddl: false
    hibernate:
      ddl-auto: none

We do have a separate section in yml file which is for dev,staging,prod environments.

And we are using dev profile for dev environment.Does that require us to put tomcat properties in this section?

---
spring:
  profiles: dev,staging,prod
  datasource:
    url: "jdbc:sqlserver://${DB_IP}:${DB_PORT};databaseName=${DB_NAME}"
    username: "${DB_USER}"
    password: "${DB_PASS}"
    initialize: false
  jpa:
    show-sql: true
    generate-ddl: false
    hibernate:
      ddl-auto: none

Any idea what can be done to solve this issue.

Thanks.

Answer 1

Please make sure your Database is running on the port and ip-address that you defined in your properties file (either.yamlor.properties).

org.springframework.transaction.CannotCreateTransactionException: Could not 
open JPA EntityManager for transaction; nested exception is 
javax.persistence.PersistenceException: com.microsoft.sqlserver.jdbc.SQLServerException: 
The connection is closed.

if you see your exception a little carefully and read the message you will easily be able to identify the problem.

Here stacktrace says EntityManager won't be able to initialise because The Connection is closed.

Note : Please Verify that is your db is running or not ??

Answer 2

Issue 1 : Connection pool config for auto-reconnect when available

What profile you use and what type of datasource you construct is unclear for me. But I will post this guideline reference.

What type of data source you use enables you to use properties specific to that.

From Spring boot doc(https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html#boot-features-connect-to-production-database), I quote -

• We prefer HikariCP for its performance and concurrency. If HikariCP is available, we always choose it.
• Otherwise, if the Tomcat pooling DataSource is available, we use it.
• If neither HikariCP nor the Tomcat pooling datasource are available and if Commons DBCP2 is available, we use it.

If you use the spring-boot-starter-jdbc or spring-boot-starter-data-jpa “starters”, you automatically get a dependency to HikariCP.

Thus unless you specifically ask to use Tomcat connection pool(which you did in application.yml via spring.datasource.type), you get HikariCp. So choose carefully.

---

So, these are the properties for Tomcat and HikariCP that can help you to auto-reconnect:

Tomcat JDBC connection pool:

spring.datasource.tomcat.testOnBorrow = true
spring.datasource.tomcat.validation-query = SELECT 1

Hikari CP:

#connectionTestQuery not needed for JDBC4 drivers. You can add them unless complained
spring.datasource.hikari.connectionTestQuery = SELECT 1
spring.datasource.hikari.connectionInitSql = SELECT 1

P.S: : Watch out for the profiles you use and the datasource type. You have tomcat in one and in dev, qa and prod you get default HikariCp. Be mindful. On validation queries for DBs, reference article here: https://stackoverflow.com/a/3670000/2931410

Issue 2 : Docker Windows containers that disconnects often

• Try installing latest docker version
• If still doesn't work, try switching to Linux containers in Docker.

Answer 3

We were able to find a solution for this issue. The issue was this line
type: org.apache.tomcat.jdbc.pool.XADataSource.
We replaced this above line with
type: org.apache.tomcat.jdbc.pool.DataSource

Now application is able to reconnect to db without a restart, after a connection drop to db.

Thanks everyone for your ideas.

Vishnu