当前位置:   article > 正文

解决数据库查询时间过长导致com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure_caused by: com.mysql.cj.exceptions.cjcommunication

caused by: com.mysql.cj.exceptions.cjcommunicationsexception: communications

1.问题描述

大数据量下数据库查询中断,抛出异常,异常信息见附录1。

2.排查过程

使用springboot项目测试分库分表,使用sharding-jdbc插件,2000w数据量查询总数count(*)查询,查询失败,经过排查,排除了sharding-jdbc插件的问题,还原原始的mybatis-plus查询方式依然报错,经过查阅网上的相关文章,尝试修改druid连接池参数和mysql服务参数都没有解决问题,但大致确定是查询超时导致的,通过使用数据库连接工具测试没有问题,排除mysql服务参数配置的问题,基本确定问题在druid连接池上,查看使用的是1.2.12版本的druid连接池,于是切换版本为1.2.9,问题解决,于是查询1.2.12新增的特性,发现1.2.12新增connectTimeout和socketTimeout参数,指定该参数后依然无法解决,通过进一步查询得知1.2.12版本默认这两个参数是10s与报错信息中10s相匹配,于是查询参数配置方式得知该参数需要单独配置,不能在connectionProperties参数中配置,在以后版本中取消了默认参数且可以在connectionProperties参数中配置。

  1. connectionProperties: connectTimeout=60000;socketTimeout=60000 #1.2.12版本配置失效
  2. connectTimeout: 60000 #1.2.12版本需要单独配置才生效
  3. socketTimeout: 60000 #1.2.12版本需要单独配置才生效

3.解决方法

1.升级druid版本到1.2.12以上且使用以上配置中的一种(推荐)

2.使用1.2.12版本使用单独配置的方式

3.降低druid版本到1.2.12以下无需配置该参数(不推荐)

4.完整配置文件(1.2.12及以上版本适用)

  1. spring:
  2. datasource:
  3. type: com.alibaba.druid.pool.DruidDataSource
  4. druid:
  5. driver-class-name: com.mysql.cj.jdbc.Driver
  6. url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
  7. username: root
  8. password: 123456
  9. # connectionProperties: connectTimeout=60000;socketTimeout=60000 #1.2.12版本配置失效
  10. connectTimeout: 60000 #1.2.12版本需要单独配置才生效
  11. socketTimeout: 60000 #1.2.12版本需要单独配置才生效
  12. initialSize: 5
  13. minIdle: 5
  14. maxActive: 20
  15. maxWait: 60000
  16. timeBetweenEvictionRunsMillis: 60000
  17. min-evictableIdleTimeMillis: 300000
  18. validationQuery: SELECT 1 FROM DUAL
  19. testWhileIdle: true
  20. testOnBorrow: false
  21. testOnReturn: false
  22. poolPreparedStatements: true
  23. maxPoolPreparedStatementPerConnectionSize: 20
  24. filters: stat,wall

5.附录

附录1

  1. org.springframework.dao.RecoverableDataAccessException:
  2. ### Error querying database. Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
  3. The last packet successfully received from the server was 10,017 milliseconds ago. The last packet sent successfully to the server was 10,018 milliseconds ago.
  4. ### The error may exist in file [E:\Example\Java\sharding_test\target\classes\mappers\UserMapper.xml]
  5. ### The error may involve defaultParameterMap
  6. ### The error occurred while setting parameters
  7. ### SQL: SELECT COUNT(*) FROM user
  8. ### Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
  9. The last packet successfully received from the server was 10,017 milliseconds ago. The last packet sent successfully to the server was 10,018 milliseconds ago.
  10. ; Communications link failure
  11. The last packet successfully received from the server was 10,017 milliseconds ago. The last packet sent successfully to the server was 10,018 milliseconds ago.; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
  12. The last packet successfully received from the server was 10,017 milliseconds ago. The last packet sent successfully to the server was 10,018 milliseconds ago.
  13. at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:100)
  14. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
  15. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
  16. at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
  17. at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
  18. at com.sun.proxy.$Proxy62.selectList(Unknown Source)
  19. at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
  20. at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:122)
  21. at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:87)
  22. at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
  23. at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
  24. at com.sun.proxy.$Proxy68.findPage(Unknown Source)
  25. at com.fkp.ShardingTestApplicationTests.page(ShardingTestApplicationTests.java:79)
  26. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  27. at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  28. at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  29. at java.lang.reflect.Method.invoke(Method.java:498)
  30. at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
  31. at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
  32. at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
  33. at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
  34. at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
  35. at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
  36. at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
  37. at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
  38. at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
  39. at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
  40. at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
  41. at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
  42. at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
  43. at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
  44. at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
  45. at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  46. at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)
  47. at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
  48. at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)
  49. at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
  50. at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  51. at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
  52. at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
  53. at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
  54. at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  55. at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
  56. at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
  57. at java.util.ArrayList.forEach(ArrayList.java:1257)
  58. at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
  59. at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
  60. at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  61. at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
  62. at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
  63. at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
  64. at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  65. at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
  66. at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
  67. at java.util.ArrayList.forEach(ArrayList.java:1257)
  68. at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
  69. at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
  70. at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  71. at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
  72. at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
  73. at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
  74. at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  75. at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
  76. at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
  77. at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
  78. at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
  79. at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
  80. at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)
  81. at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
  82. at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
  83. at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
  84. at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
  85. at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
  86. at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
  87. at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
  88. at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
  89. at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
  90. at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
  91. at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
  92. at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
  93. at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
  94. at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
  95. Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
  96. The last packet successfully received from the server was 10,017 milliseconds ago. The last packet sent successfully to the server was 10,018 milliseconds ago.
  97. at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
  98. at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
  99. at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
  100. at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
  101. at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3446)
  102. at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:638)
  103. at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
  104. at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
  105. at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
  106. at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:152)
  107. at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
  108. at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:62)
  109. at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:58)
  110. at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82)
  111. at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
  112. at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.syncExecute(ExecutorEngine.java:97)
  113. at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.parallelExecute(ExecutorEngine.java:93)
  114. at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.execute(ExecutorEngine.java:76)
  115. at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:68)
  116. at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:51)
  117. at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:129)
  118. at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:148)
  119. at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:145)
  120. at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
  121. at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
  122. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  123. at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  124. at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  125. at java.lang.reflect.Method.invoke(Method.java:498)
  126. at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
  127. at com.sun.proxy.$Proxy92.query(Unknown Source)
  128. at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69)
  129. at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
  130. at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
  131. at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165)
  132. at com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:134)
  133. at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:59)
  134. at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
  135. at com.sun.proxy.$Proxy91.query(Unknown Source)
  136. at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
  137. at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
  138. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  139. at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  140. at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  141. at java.lang.reflect.Method.invoke(Method.java:498)
  142. at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
  143. ... 77 more
  144. Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
  145. The last packet successfully received from the server was 10,017 milliseconds ago. The last packet sent successfully to the server was 10,018 milliseconds ago.
  146. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  147. at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
  148. at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  149. at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
  150. at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
  151. at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
  152. at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
  153. at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
  154. at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:581)
  155. at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:761)
  156. at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:700)
  157. at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1051)
  158. at com.mysql.cj.NativeSession.execSQL(NativeSession.java:665)
  159. at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:893)
  160. ... 120 more
  161. Caused by: java.net.SocketTimeoutException: Read timed out
  162. at java.net.SocketInputStream.socketRead0(Native Method)
  163. at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
  164. at java.net.SocketInputStream.read(SocketInputStream.java:171)
  165. at java.net.SocketInputStream.read(SocketInputStream.java:141)
  166. at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107)
  167. at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150)
  168. at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180)
  169. at java.io.FilterInputStream.read(FilterInputStream.java:133)
  170. at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)
  171. at com.mysql.cj.protocol.a.SimplePacketReader.readHeaderLocal(SimplePacketReader.java:81)
  172. at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
  173. at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
  174. at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52)
  175. at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41)
  176. at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54)
  177. at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44)
  178. at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:575)
  179. ... 125 more

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/不正经/article/detail/158097
推荐阅读
相关标签
  

闽ICP备14008679号