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' 

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>