【エラー解決方法】MySQLでコネクションタイムアウト発生!?を解決する方法

こんにちは、堀部です。
 
長時間アクセスのないアプリケーションでDBアクセスを行った際にエラーが発生しました。

エラー内容

2018/10/23 08:51:48.264 [ERROR] [] (conn=51643) Communications link failure with primary host localhost:3306. Connection timed out (Write failed) - org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:147)
2018/10/23 08:51:48.487 [ERROR] [] org.hibernate.exception.GenericJDBCException: could not extract ResultSet - com.codematic.common.BaseExceptionResolver.resolveException(BaseExceptionResolver.java:45)
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:273)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:111)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:806)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:729)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.AbstractRequestLoggingFilter.doFilterInternal(AbstractRequestLoggingFilter.java:219)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:167)
        at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:80)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1468)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.GenericJDBCException: could not extract ResultSet
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:2036)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1836)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1815)
        at org.hibernate.loader.Loader.doQuery(Loader.java:899)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
        at org.hibernate.loader.Loader.doList(Loader.java:2522)
        at org.hibernate.loader.Loader.doList(Loader.java:2508)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2338)
        at org.hibernate.loader.Loader.list(Loader.java:2333)
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1827)
        at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:231)
        at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:157)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
        ... 55 more
Caused by: java.sql.SQLException: (conn=51643) Communications link failure with primary host localhost:3306. Connection timed out (Write failed)
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:198)
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
        at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:216)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeQuery(MariaDbPreparedStatementClient.java:164)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)
        ... 69 more
Caused by: java.sql.SQLException: Communications link failure with primary host localhost:3306. Connection timed out (Write failed)
on HostAddress{host='localhost', port=3306},master=true.  Driver has reconnect connection
        at org.mariadb.jdbc.internal.failover.AbstractMastersListener.throwFailoverMessage(AbstractMastersListener.java:515)
        at org.mariadb.jdbc.internal.failover.FailoverProxy.handleFailOver(FailoverProxy.java:354)
        at org.mariadb.jdbc.internal.failover.FailoverProxy.executeInvocation(FailoverProxy.java:292)
        at org.mariadb.jdbc.internal.failover.FailoverProxy.invoke(FailoverProxy.java:263)
        at com.sun.proxy.$Proxy77.executeQuery(Unknown Source)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209)
        ... 72 more
Caused by: java.net.SocketException: Connection timed out (Write failed)
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
        at org.mariadb.jdbc.internal.io.output.StandardPacketOutputStream.flushBuffer(StandardPacketOutputStream.java:110)
        at org.mariadb.jdbc.internal.io.output.AbstractPacketOutputStream.flush(AbstractPacketOutputStream.java:172)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:250)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.mariadb.jdbc.internal.failover.impl.MastersSlavesListener.invoke(MastersSlavesListener.java:217)
        at org.mariadb.jdbc.internal.failover.FailoverProxy.executeInvocation(FailoverProxy.java:270)
        ... 75 more

エラー発生時のバージョン

エラー発生時のバージョンは以下になります。

MySQL Connector/J
5.1.31
MySQL
5.7.10

エラー解決方法

MySQLはデフォルトで8時間以上アイドル状態が続くと、コネクションがタイムアウトしてしまいます。この設定はMySQLのwait_timeoutという項目で制御されています。

mysql> show variables like 'wait_timeout';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| wait_timeout                | 28800    |
+-----------------------------+----------+

28,800秒(8時間)となっている時間を延ばせばタイムアウトする可能性は低くなりますが、暫定的な対応でしかありません。
 
確実な対応としては、validationQueryを使用してコネクションが有効かどうかをチェックする方法があります。
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://127.0.0.1:3306/test" />
		<property name="username" value="root" />
		<property name="password" value="" />
		<property name="validationQuery" value="SELECT 1" />
	</bean>

 
これで無事、解決しました。

記事をシェア
MOST VIEWED ARTICLES