Hibernate와 MySQL 설정에서 커넥션을 잃어버리는 문제 해결

2011/11/09 16:03

이번 프로젝트에서 Spring framework + Hiberante + MySQL을 사용하여 프로젝트 개발을 진행하는 도중에 스케줄러가 돌면서 특정 시점이 되어서 아래와 같은 JDBCConnectionException 을 자꾸만 발생하였다. apache의 common-dbcp를 사용해서 dataSource를 하다가 tomcat-jdbc로 교체하여 JNDI(Java Naming and Directory Interface) dataSource로 사용하면서 발생했다. JNDI 방법을 사용하는 것은 이번이 처음이였기 때문에 더구나 지금까지 대부분 Oracle을 사용하다가 Spring framework + MySQL을 처음 다루기에 전에 보지 못한 예외들이 발생하는거라 생각했다. 여러가지 자료를 찾아보다 이 문제는 MySQL이 기본적으로 8시간동안 요청이 없으면 커넥션을 해지하고 풀링을 해지 하기 때문이라는 것을 알게 되었다. 프로젝트에서는 Spring framework의 @Scheduled를 이용해서 Tomcat 서버에서 스케줄을하는데 웹 요청이 들어오지 않고 JNDI를 이용해서 스케줄에서 Hibernate와 MySQL을 사용해서 생기는 문제로 여겨졌다.

org.springframework.dao.DataAccessResourceFailureException: could not execute query; nested exception is org.hibernate.exception.JDBCConnectionException: could not execute query

at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:629)

at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)

at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)

at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)

at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:912)

at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:904)

at net.hibrain.apps.push.model.PushProviderDaoImpl.findWillPushDevices(PushProviderDaoImpl.java:50)

at net.hibrain.apps.push.model.PushProviderDaoImpl.insertAllPushDeviceJSON(PushProviderDaoImpl.java:131)

at net.hibrain.apps.push.service.PushProviderServiceImpl.start(PushProviderServiceImpl.java:111)

at net.hibrain.apps.push.service.PushProviderServiceImpl.start(PushProviderServiceImpl.java:61)

at net.hibrain.apps.push.schedule.PushScheduleServiceImpl.startSyncronous(PushScheduleServiceImpl.java:34)

at sun.reflect.GeneratedMethodAccessor47.invoke(Unknown Source)

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

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

at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:64)

at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53)

at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:206)

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

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

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

Caused by: org.hibernate.exception.JDBCConnectionException: could not execute query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

at org.hibernate.loader.Loader.doList(Loader.java:2231)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)

at org.hibernate.loader.Loader.list(Loader.java:2120)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)

at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)

at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)

at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)

at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:921)

at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:1)

at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)

... 22 more

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 35,986,917 milliseconds ago. The last packet sent successfully to the server was 22 milliseconds ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)

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

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

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

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

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

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

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)

at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)

at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)

at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)

at org.hibernate.loader.Loader.doQuery(Loader.java:697)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)

at org.hibernate.loader.Loader.doList(Loader.java:2228)

... 32 more

MySQL에서 wait_time을 조회하는 방법은 다음과 같다.

show global variables like 'wait%';

MySQL은 기본적으로 8시간동안 요청이 없으면 커넥션을 해지한다.
방법은 리소스를 설정하는 MySQL url에다가 autoReconnection=true로 변경하면 된다. 또는 valdationQuery="select 1"을 전처리로 실행하게하면 된다.


<Resource name="jdbc/test_database" auth="Container" type="javax.sql.DataSource"               
maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"              
username="ID" password="PASSWORD" driverClassName="com.mysql.jdbc.Driver"              
url="jdbc:mysql://localhost: 3306/test_database?autoReconnection=true"/>

또는 스프링 프레임워크에서 hibernateProperties를 설정하는 곳에서 설정하면 된다. 다음은 그 설정 예이다. 이 설정 방법은 다음 블로그에서 참조했다. (http://mimul.com/pebble/default/2008/06/24/1214258760000.html)

<bean id="hibernateProperties"
          class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="properties">
            <props>
                <prop key="hibernate3.hbm2ddl.auto">update</prop>
                <prop key="hibernate3.dialect">org.hibernate.dialect.MySQLDialect</prop>
                <prop key="hibernate3.c3p0.minPoolSize">15</prop>
                <prop key="hibernate3.c3p0.maxPoolSize">50</prop>
                <prop key="hibernate3.c3p0.timeout">600</prop>
                <prop key="hibernate3.c3p0.max_statement">50</prop>
                <prop key="hibernate3.c3p0.testConnectionOnCheckin">true</prop>
                <prop key="hibernate3.c3p0.testConnectionOnCheckout">false</prop>
                <prop key="hibernate3.c3p0.maxStatementsPerConnection">5</prop>
                <prop key="hibernate3.c3p0.maxIdleTime">300</prop>
                <prop key="hibernate3.c3p0.maxConnectionAge">14400</prop>
                <prop key="hibernate3.c3p0.acquireRetryAttempts">10</prop>
                <prop key="hibernate3.c3p0.preferredTestQuery">SELECT 1;</prop>
                <prop key="hibernate3.c3p0.idleConnectionTestPeriod">300</prop>
                <prop key="hibernate3.provider">org.hibernate.connection.C3P0ConnectionProvider</prop>
            </props>
        </property>
    </bean>