[Spring] Mysql Connection을 잃어 버릴 경우

Spring + Mysql + Tomcat9 환경에서 로그인 시 간헐적으로 아래와 같은 오류가 발생했다.

org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [spring] in context with path [/****] threw exception [Request processing failed; nested exception is org.springframework.dao.RecoverableDataAccessException:

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 40,760,216 milliseconds ago.  The last packet sent successfully to the server was 40,760,225 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

### The error occurred while setting parameters

### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 40,760,216 milliseconds ago.  The last packet sent successfully to the server was 40,760,225 milliseconds ago.

구글링 결과 MySQL이 기본적으로 8시간동안 요청이 없으면 커넥션을 해지하고 풀링을 해지 하기 때문이라는 것을 알게 되었다. 아래와 같이 쿼리를 치면 기본 설정값이 8시간 ( 60초 * 60분 * 8시간 = 28800 초) 인 것을 알 수 있다.

mysql> show global variables like 'wait%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| wait_timeout  | 28800 |

+---------------+-------+

이를 해결하기 위해서는 

1. 오류 로그에서 권장하는 jdbc url 끝에 ?autoReconnect=true 를 추가한다.

2. dataSource를 설정하는 영역에서 몇가지 속성을 추가한다.(validationQuery, testWhileIdle)

<bean id="mysqlDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

        <property name="driverClassName" value="${jdbc.driverClassName}"/>

        <property name="url" value="${jdbc.url}"/>

        <property name="username" value="${jdbc.username}"/>

        <property name="password" value="${jdbc.password}"/>

        <property name="validationQuery" value="SELECT 1" />

        <property name="testWhileIdle" value="true" />

 </bean>

출처 

 - http://blog.saltfactory.net/solving-lost-hibernate-connection-mysql-in-springframework/

 - http://www.hyoyoung.net/65