Spring is losing connection to the DB and does not recover or reconnect

Question

I have a spring-boot application on the same host as the Maria DB and both are running fine for some time. But between 12 hours and 2 days it seems that the spring boot application looses the connection to the database (stacktrace) and does not recover from that.

When I restart the spring application all is fine again for some time.

The application is not under load and when it looses the connection the application is still working but the db connection does not recover. The DB did not restart in the meantime (uptime 4 weeks). Only the monitoring service pings the application which pings the DB once a minute. (spring boot health)

Other Java applications that are connected to the same DB are running fine and do not have any issues.

My Question is:

Why does spring not recover from that error and try to reconnect to the DB again? How can I set up spring to reconnect to the DB?

2015-02-19 15:25:48.392  INFO 4931 [qtp92662861-19] --- o.s.b.f.xml.XmlBeanDefinitionReader      : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2015-02-19 15:25:48.580  INFO 4931 [qtp92662861-19] --- o.s.jdbc.support.SQLErrorCodesFactory    : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
2015-02-19 15:25:48.616  WARN 4931 [qtp92662861-19] --- o.s.jdbc.support.SQLErrorCodesFactory    : Error while extracting database product name - falling back to empty error codes

org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:296)
    at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:320)
    at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:214)
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:134)
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:97)
    at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:413)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:468)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:478)
    at org.springframework.boot.actuate.health.DataSourceHealthIndicator.doDataSourceHealthCheck(DataSourceHealthIndicator.java:98)
    at org.springframework.boot.actuate.health.DataSourceHealthIndicator.doHealthCheck(DataSourceHealthIndicator.java:87)
    at org.springframework.boot.actuate.health.AbstractHealthIndicator.health(AbstractHealthIndicator.java:38)
    at org.springframework.boot.actuate.endpoint.HealthEndpoint.invoke(HealthEndpoint.java:67)
    at org.springframework.boot.actuate.endpoint.HealthEndpoint.invoke(HealthEndpoint.java:34)
    at org.springframework.boot.actuate.endpoint.mvc.HealthMvcEndpoint.invoke(HealthMvcEndpoint.java:102)
    at sun.reflect.GeneratedMethodAccessor78.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:769)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1667)
    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:1650)
    at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration$ApplicationContextHeaderFilter.doFilterInternal(EndpointWebMvcAutoConfiguration.java:280)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1650)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:186)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1650)
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1650)
    at onlinevalidation.CorsFilter.doFilter(CorsFilter.java:20)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1650)
    at org.springframework.boot.actuate.autoconfigure.MetricFilterAutoConfiguration$MetricsFilter.doFilterInternal(MetricFilterAutoConfiguration.java:90)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1650)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1125)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1059)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
    at org.eclipse.jetty.server.Server.handle(Server.java:497)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:248)
    at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:610)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:539)
    at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.Util.getInstance(Util.java:360)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
    at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1232)
    at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1225)
    at com.mysql.jdbc.ConnectionImpl.getMetaData(ConnectionImpl.java:2932)
    at com.mysql.jdbc.ConnectionImpl.getMetaData(ConnectionImpl.java:2927)
    at sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
    at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
    at com.sun.proxy.$Proxy68.getMetaData(Unknown Source)
    at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:285)
    ... 66 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 758,805 milliseconds ago.  The last packet sent successfully to the server was 37 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3427)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3327)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:452)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:402)
    ... 60 common frames omitted
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3337)
    ... 69 common frames omitted

@Configuration
@ComponentScan(value = "com.demo.validation",scopedProxy = TARGET_CLASS)
@EnableAutoConfiguration
@EnableAspectJAutoProxy(proxyTargetClass = true)
@EnableCaching(proxyTargetClass = true)
@EnableAsync(proxyTargetClass = true)
@EnableJpaRepositories
@EnableTransactionManagement(proxyTargetClass = true)
public class Configuration {
  main(...)
}

The Configuration

spring.datasource.url=jdbc:mysql://localhost/validation
spring.datasource.username=validation
spring.datasource.password=****
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Gradle.Build

dependencies {
    //Boot
    compile 'org.codehaus.groovy:groovy-all:2.3.7:indy'
    compile 'org.springframework.boot:spring-boot-starter-actuator:1.1.8.RELEASE'
    compile 'org.springframework.boot:spring-boot-starter-security:1.1.8.RELEASE'
    compile 'org.springframework:spring-aspects:4.0.7.RELEASE'
    compile 'org.springframework.boot:spring-boot-starter-aop:1.1.8.RELEASE'
    compile 'org.springframework:spring-instrument:4.0.7.RELEASE'
    compile('org.springframework.boot:spring-boot-starter-web:1.1.8.RELEASE'){
        exclude module: 'spring-boot-starter-tomcat'
    }

    //servlet container
    compile 'org.eclipse.jetty:jetty-webapp:9.2.3.v20140905'
    compile 'org.eclipse.jetty:jetty-servlets:9.2.3.v20140905'

    //DB
    compile 'org.springframework.boot:spring-boot-starter-data-jpa:1.1.8.RELEASE'
    compile 'mysql:mysql-connector-java:5.1.34'
    //compile 'org.mariadb.jdbc:mariadb-java-client:1.1.8'
    runtime 'com.h2database:h2:1.4.182'

Cureton

ehi, i get the same problem!.. did you solved with autoReconnect=true or you found another (better) solution? – Coth

I have Chosen to use autoreconnect=true, as in my case I don't have that many transactions. – Cureton

Related: #22685307 – Rhomb

Answer 1

Per a senior member in the Spring forums, the Spring DataSource is not intended for production use:

The above answers are only part of the solution. Indeed you need proper transaction managent AND you need a connection pool. The DriverManagerDataSource is NOT meant for production, it opens and closes a datebase connection each time it needs one.

Instead you can use C3P0 as your DataSource which handles the reconnect and is much better in performance. Here's a quick example of a potential configuration in a Spring xml configuration:

<bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close">
    <property name="driverClass" value="com.mysql.jdbc.Driver" />
    <property name="jdbcUrl" value="#{systemProperties.dbhost}" />
    <property name="user" value="#{systemProperties.dbuser}" />
    <property name="password" value="#{systemProperties.dbpass}" />
    <property name="maxPoolSize" value="25" />
    <property name="minPoolSize" value="10" />
    <property name="maxStatements" value="100" />
    <property name="testConnectionOnCheckout" value="true" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg ref="c3p0DataSource" />
</bean>

Status

@Status is correct. From current Spring documentation "Implementations in the Spring distribution are meant only for testing purposes and do not provide pooling." docs.spring.io/spring-framework/docs/current/… – Praseodymium

this is the only thing that has helped me in 4 days of searching -- thank you so much @Hazok. i had that driver manager datasource and kept losing my connection!!!! – Maynord

Wow. The speedup is insane. – Keelin

You would think of all things that Spring Boot provides, a connection pool would be at the top of the list! How do you integrate C3P0 using annotations? – Rhomb

@chloe - Spring Boot does automatically provide a connection pool. The default as of Spring Boot 1.5.9 was Tomcat JDBC pooling. However, I believe it now defaults to Hikari CP instead. To bring in the connection pool, the project needs to add spring-boot-starter-data-jpa or spring-boot-starter-jdbc. In the case of the Tomcat JDBC pool, you can confirm that it's being used by doing something like this: java @Autowired private DataSource dataSource; @Test public void printSettings() { System.out.println("DataSource: " + dataSource); } – Sec

Is this true of the actuator? WE are using connection pooling but it doesn't appear the actuaor DB health check is using that connection pool by deafult....can somone confirm? – Tva

Answer 2

Spring (3) boot settings for hibernate and oracle:

spring.datasource.test-on-borrow=true

spring.datasource.validation-query=select 1 from dual

---

Catechetical

Could you please elaborate more on how this fixes the problem? – Footlocker

Answer 3

Try changing your connection URL to:

spring.datasource.url=jdbc:mysql://localhost/validation?autoReconnect=true

Gasworks

I saw autoReconnect=true as well. It is not recommended, but I never found a profound reason why, especially in context with spring. – Cureton

I know - there is a bit of controversy around that feature, e.g. #667789. But it is still supported and maybe it solves your problem. Did you give it a try? – Gasworks

Answer 4

To elaborate on @user5101998's answer and update the other ones, autoReconnect is actually not recommended. From the connector documentation:

The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly

The test-on-borrow property makes your provider (Tomcat, Hikari, etc.) tests that the connection is valid before fetching it from the connection pool.

The validate-query is what will be used to ensure that the connection is valid.

Using these parameters should prevent Spring from using dead connections. For Tomcat for instance, you can find documentation here on these parameters.

Brunei