[SPRING] 스프링이 DB와의 연결이 끊어지고 복구하거나 다시 연결하지 않습니다
SPRING스프링이 DB와의 연결이 끊어지고 복구하거나 다시 연결하지 않습니다
Maria DB와 동일한 호스트에 스프링 부트 응용 프로그램이 있으며 둘 다 한동안 제대로 실행 중입니다. 그러나 12 시간에서 2 일 사이에 스프링 부트 응용 프로그램은 데이터베이스 (스택 추적) 연결을 느슨하게하고 복구하지 않는 것 같습니다.
스프링 응용 프로그램을 다시 시작하면 잠시 동안 다시 괜찮습니다.
응용 프로그램이로드되지 않았고 연결이 끊어지면 응용 프로그램은 계속 작동하지만 db 연결은 복구되지 않습니다. 그동안 DB가 시작되지 않았습니다 (가동 시간 4 주). 모니터링 서비스 만이 1 분에 한 번씩 DB를 핑하는 애플리케이션을 핑합니다. (봄 부츠 건강)
동일한 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.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'
해결법
-
==============================
1.Spring 포럼의 선임 멤버에 따라 Spring DataSource는 프로덕션 용도로 사용되지 않습니다.
Spring 포럼의 선임 멤버에 따라 Spring DataSource는 프로덕션 용도로 사용되지 않습니다.
대신 C3P0을 재 연결을 처리하고 성능이 훨씬 우수한 DataSource로 사용할 수 있습니다. 다음은 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> -
==============================
2.최대 절전 및 Oracle에 대한 Spring (3) 부팅 설정 :
최대 절전 및 Oracle에 대한 Spring (3) 부팅 설정 :
spring.datasource.test-on-borrow = true
spring.datasource.validation-query = 이중에서 1을 선택하십시오
-
==============================
3.연결 URL을 다음과 같이 변경하십시오.
연결 URL을 다음과 같이 변경하십시오.
spring.datasource.url=jdbc:mysql://localhost/validation?autoReconnect=true
from https://stackoverflow.com/questions/28609565/spring-is-losing-connection-to-the-db-and-does-not-recover-or-reconnect by cc-by-sa and MIT license