Tomcat + MySQL에서 [java.sql.SQLException: Already closed.] 발생

java.sql.SQLException: Already closed.

환경

Tomcat 6.x, MySQL 5.3.x, Spring Framework 3.2.9

문제

새벽 5시마다 배치작업을 하면서 조회 쿼리를 한 번 실행하는데, 어떤 날은 제대로 되고 어떤 날은 실패했다.

로그를 보니 아래와 같다.

2017-09-19 05:10:00,004 DEBUG [org.springframework.orm.ibatis.SqlMapClientTemplate] Could not close JDBC Connection
java.sql.SQLException: Already closed.
	at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114) ~[commons-dbcp-1.4.jar:1.4]
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191) ~[commons-dbcp-1.4.jar:1.4]
	at org.springframework.jdbc.datasource.DataSourceUtils.doCloseConnection(DataSourceUtils.java:341) ~[spring-jdbc-3.2.9.RELEASE.jar:3.2.9.RELEASE]
	at org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(DataSourceUtils.java:328) ~[spring-jdbc-3.2.9.RELEASE.jar:3.2.9.RELEASE]
	at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:211) ~[spring-jdbc-3.2.9.RELEASE.jar:3.2.9.RELEASE]
	at com.sun.proxy.$Proxy147.close(Unknown Source) ~[?:?]
	at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212) [spring-orm-3.2.9.RELEASE.jar:3.2.9.RELEASE]
	at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271) [spring-orm-3.2.9.RELEASE.jar:3.2.9.RELEASE]
	at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:265) [spring-orm-3.2.9.RELEASE.jar:3.2.9.RELEASE]

원인

찾아보니 톰캣은 커넥션을 잡고 있었는데 MySQL에서 오래 지나면 커넥션풀을 끊기 때문이라고 한다.

해결

Spring 설정 중 dataSource에 커넥션에 대한 validation을 추가해주면 된다.

내 경우는 아래와 같다.

    <!-- context-datasource.xml -->
    <!-- mysql -->
    <bean id="dataSource-mysql" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${Globals.DriverClassName}"/>
        <property name="url" value="${Globals.Url}"/>
        <property name="username" value="${Globals.UserName}"/>
        <property name="password" value="${Globals.Password}"/>
        <property name="validationQuery" value="select 1"/> <!-- 뭐든 조회하는 가장 간단한 쿼리를 추가함. MySQL이라서 select 1 넣음 -->
    </bean>