스프링과 DB의 연결이 끊겨 복구 또는 재연결되지 않음

스프링과 DB의 연결이 끊겨 복구 또는 재연결되지 않음

Maria DB와 같은 호스트에 스프링 부트 애플리케이션이 있으며, 두 애플리케이션 모두 한동안 정상적으로 실행됩니다.단, 12시간에서2일 사이에 스프링 부트애플리케이션에서 데이터베이스(스택 트레이스)로의 접속이 끊어지고 회복되지 않는 것 같습니다.

스프링 어플리케이션을 재기동하면 얼마간은 모든 것이 정상입니다.

응용 프로그램이 로드되어 있지 않으며 연결이 끊어질 경우 응용 프로그램은 여전히 작동 중이지만 DB 연결이 복구되지 않습니다.그 사이에 DB가 재시작되지 않았습니다(약 4주).1분에 1회 DB에 ping을 실행하는 어플리케이션에 ping을 실행하는 것은 모니터링 서비스뿐입니다.(스프링 부트 상태)

동일한 DB에 연결된 다른 Java 응용 프로그램은 정상적으로 실행되며 문제가 없습니다.

질문 내용:

스프링이 이 오류에서 복구되지 않고 DB에 다시 연결을 시도하지 않는 이유는 무엇입니까?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(...)
}

설정

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

Gradle.빌드

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'

Spring 포럼의 고위 구성원에 따르면 Spring DataSource는 운영 용도로 사용되지 않습니다.

위의 답변은 솔루션의 일부에 불과합니다.실제로 적절한 트랜잭션 관리 및 연결 풀이 필요합니다.Driver Manager Data Source는 실제 가동용이 아닙니다.필요할 때마다 날짜 기반 연결을 열고 닫습니다.

대신 재접속을 처리하고 성능을 크게 향상시키는 데이터 소스로 C3P0을 사용할 수 있습니다.다음으로 Spring xml 설정에서의 설정 예를 나타냅니다.

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

휴지 상태 및 오라클에 대한 스프링(3) 부팅 설정:

spring.contextsource.test-on-context=true

spring.contextsource.validation-context=듀얼에서1 을 선택합니다.

---

연결 URL을 다음과 같이 변경해 보십시오.

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

@user5101998의 답변을 자세히 설명하고 다른 답변을 업데이트하려면autoReconnect는 실제로 권장되지 않습니다.커넥터 매뉴얼에서 다음을 참조해 주세요.

이 기능은 응용 프로그램이 SQLExceptions를 제대로 처리하지 못할 때 세션 상태 및 데이터 일관성과 관련된 부작용이 발생하며 비활성 연결 및 오래된 연결로 인한 SQLExceptions를 적절하게 처리하도록 응용 프로그램을 구성할 수 없는 경우에만 사용하도록 설계되었기 때문에 사용하지 않는 것이 좋습니다.

test-on-borrow poolproperty에서 하기 전에(Tomcat, Hikari.

validate-query는, 접속이 유효한 것을 확인하기 위해서 사용되는 것입니다.

이러한 파라미터를 사용하면 스프링이 데드 접속을 사용하지 않도록 할 수 있습니다.예를 들어 Tomcat의 경우 이러한 매개 변수에 대한 설명서를 여기에서 찾을 수 있습니다.

언급URL : https://stackoverflow.com/questions/28609565/spring-is-losing-connection-to-the-db-and-does-not-recover-or-reconnect