技術
2019.8.16(Fri)

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

#プログラム #Java #データベース
記事をシェア：

こんにちは、堀部です。
 
長時間アクセスのないアプリケーションで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
５.１.３１
MySQL
５.７.１０

エラー解決方法

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

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

２８,８００秒（８時間）となっている時間を延ばせばタイムアウトする可能性は低くなりますが、暫定的な対応でしかありません。
 
確実な対応としては、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>

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

記事をシェア
ITエンジニアリング事業部 マネージャー K.Horibe 技術ブログを中心に小ネタを書いています。たまにはライフスタイルも書いてみようかな。
MOST VIEWED ARTICLES
お知らせ
2023.4.6(Thu)

SFA／CRM・日報クラウドサービス「feels」の3周年記念 「営業活動DX診断・無料個別相談キャンペーン」を開始！

#リリース情報 #feels
お知らせ
2024.4.4(Thu)

ソフトウェアの導入を支援！ IT導入補助金2024のIT導入支援事業者に「feels」が認定

#リリース情報 #feels
ライフスタイル
2021.2.10(Wed)

WEB面接で失敗しない！ 面接官が実は見ている７つのポイント

#採用 #ビジネス
ライフスタイル
2016.2.8(Mon)

新卒採用担当はここを見ている！ 絶対にやってはいけない履歴書の書き方

#採用 #ビジネス
技術
2022.1.17(Mon)

これさえ見ればもう怖くない！ Javaでよく出るエラーと対策5選

#まとめ #Java
技術
2022.2.25(Fri)

正規表現入門　否定の正規表現の書き方《～ではない行。～を含まない行。》

#まとめ #プログラム
技術
2020.12.16(Wed)

Java初心者の登竜門！ Java Bronze試験で間違えやすい問題６選

#プログラム #Java