Tomcat 에서 jndi 등으로

MySQL Connection 세팅을 하고 쓰다보면

가끔. java.io.EOFException: Can not read response from server.

Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

이런류의 에러를 뱉을때가 있다.

원인을 찾아보니 MySQL Connection Error 인데,

어느 정도 사용을 하는 어플리케이션이라면 이런 문제는 발생하지 않을것이다.

MySQL 은 기본적으로 컨넥션을 통해서 8시간 동안 request가 오지 않는다면, 

강제적으로 컨넥션을 닫아버린다.

( 이는 물론 MySQL 의 설정을 변경함으로써 값을 바꿀 수는 있다. 하지만, 좋은 방법이라고 생각되지는 않는다. )

해결방법은 의외로 간단했다.

<GlobalNamingResources>

    <Resource name="커넥션이름" 

              auth="Container" 

              type="javax.sql.DataSource"

              maxActive="1000"

              maxIdle="30"

              maxWait="180"

              logAbandoned="true"

              removeAbandoned="true"

              removeAbandonedTimeout="60"

              <!-- validationQuery:유효 검사용 쿼리

                  ( 1개 이상의 row를 반환하는 쿼리를 넣어주면 된다. ) -->

           validationQuery="select 1"

              <!-- testWhileIdle:컨넥션이 놀고 있을때 -_-; 

                  validationQuery 를 이용해서 유효성 검사를 할지 여부. -->

           testWhileIdle="true"

              <!-- timeBetweenEvictionRunsMillis:

                  해당 밀리초마다 validationQuery 를 이용하여 유효성 검사 진행 -->

           timeBetweenEvictionRunsMillis="7200000"

              username="DB접속ID"

              password="DB접속비밀번호"

              driverClassName="com.mysql.jdbc.Driver"

              url="jdbc:mysql://DBurl/DBname"/>

  </GlobalNamingResources>

Error Log

2013. 7. 30 오후 6:18:56 org.apache.catalina.core.StandardWrapperValve invoke

심각: Servlet.service() for servlet [dispatcher] in context with path [/test] threw exception [Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 34,961 milliseconds ago.  

The last packet sent successfully to the server was 1 milliseconds ago.] with root cause

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:3052)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)

at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5333)

at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371)

at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328)

at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:223)

at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)

at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)

at com.sun.proxy.$Proxy23.sendLearnPush(Unknown Source)

at com.unus.englishcall.common.controller.CommonController.sendLearnPush(CommonController.java:199)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:947)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1009)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)

at java.lang.Thread.run(Thread.java:662)

	<environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />

<dataSource type="DBCP">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://ec2-54-180-93-87.ap-northeast-2.compute.amazonaws.com:3306/111?autoReconnect=true" />
<property name="username" value="1111" />
<property name="password" value="111" />
<property name="defaultAutoCommit" value="true" />
<property name="maximumActiveConnections" value="5" />
<property name="maximumIdleConnections" value="3" />
<property name="maximumWait" value="60000" />
<!-- validationQuery:유효 검사용 쿼리( 1개 이상의 row를 반환하는 쿼리를 넣어주면 된다. ) -->
<property name="validationQuery" value="select 1"/>
<!-- testWhileIdle:컨넥션이 놀고 있을때 -_-; validationQuery 를 이용해서 유효성 검사를 할지 여부. -->
<property name="testWhileIdle" value="true"/>
<!-- timeBetweenEvictionRunsMillis:해당 밀리초마다 validationQuery 를 이용하여 유효성 검사 진행 -->
<property name="timeBetweenEvictionRunsMillis" value="7200000"/>
</dataSource>
      
        </environment>
    </environments>
    

Warning... too many connections..."

라고 하는 메시지와 함께 종료되는 경우가 발생합니다.

이 문제의 root cause는 MYSQL의 환경변수에 설정되어있는 "max_connections" 값을 초과하는 쿼리가 발생하는 것입니다.

"mysqladmin -u root -p variables" 를 실행해보면 MYSQL의 전체 환경변수의 값을 확인 할 수 있는데 그 가운데 다음 3가지의 값을 조절함으로서 해당 에러를 응급조치 할 수 있습니다.

- max_connections  : MYSQL에 접속할 수 있는 최대 연결 수

- table_open_cache : MYSQL의 캐쉬 크기

- wait_timeout     : MYSQL의 쿼리가 지속되는 시간

이 방법으로 "too many connections" 문제가 해결되었다면 부팅시마다 MYSQL을 시작하면서 매번 자동 적용 되도록 /etc/rc.d/rc.local 파일에 아래와 같이 설정해두는 것도 좋은 방법이 됩니다.

예시)

/usr/local/mysql/bin/mysqlid_safe -O max_connections=1000 -O table_open_cache=512 -O wait_timeout=57600 &