赞
踩
大数据量下数据库查询中断,抛出异常,异常信息见附录1。
使用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参数中配置。
- connectionProperties: connectTimeout=60000;socketTimeout=60000 #1.2.12版本配置失效
- connectTimeout: 60000 #1.2.12版本需要单独配置才生效
- socketTimeout: 60000 #1.2.12版本需要单独配置才生效
- spring:
- datasource:
- type: com.alibaba.druid.pool.DruidDataSource
- druid:
- driver-class-name: com.mysql.cj.jdbc.Driver
- url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
- username: root
- password: 123456
- # connectionProperties: connectTimeout=60000;socketTimeout=60000 #1.2.12版本配置失效
- connectTimeout: 60000 #1.2.12版本需要单独配置才生效
- socketTimeout: 60000 #1.2.12版本需要单独配置才生效
- initialSize: 5
- minIdle: 5
- maxActive: 20
- maxWait: 60000
- timeBetweenEvictionRunsMillis: 60000
- min-evictableIdleTimeMillis: 300000
- validationQuery: SELECT 1 FROM DUAL
- testWhileIdle: true
- testOnBorrow: false
- testOnReturn: false
- poolPreparedStatements: true
- maxPoolPreparedStatementPerConnectionSize: 20
- filters: stat,wall
附录1
- org.springframework.dao.RecoverableDataAccessException:
- ### Error querying database. Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
-
- 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.
- ### The error may exist in file [E:\Example\Java\sharding_test\target\classes\mappers\UserMapper.xml]
- ### The error may involve defaultParameterMap
- ### The error occurred while setting parameters
- ### SQL: SELECT COUNT(*) FROM user
- ### Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
-
- 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.
- ; Communications link failure
-
- 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
-
- 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.
-
- at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:100)
- at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
- at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
- at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
- at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
- at com.sun.proxy.$Proxy62.selectList(Unknown Source)
- at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
- at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:122)
- at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:87)
- at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
- at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
- at com.sun.proxy.$Proxy68.findPage(Unknown Source)
- at com.fkp.ShardingTestApplicationTests.page(ShardingTestApplicationTests.java:79)
- 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.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
- at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
- at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
- at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
- at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
- at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
- at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
- at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
- at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
- at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
- at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
- at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
- at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
- at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
- at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
- at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
- at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)
- at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
- at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
- at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
- at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
- at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
- at java.util.ArrayList.forEach(ArrayList.java:1257)
- at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
- at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
- at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
- at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
- at java.util.ArrayList.forEach(ArrayList.java:1257)
- at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
- at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
- at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
- at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
- at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
- at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
- at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
- at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
- at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)
- at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
- at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
- at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
- at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
- at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
- at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
- at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
- at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
- at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
- at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
- at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
- at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
- at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
- at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
- Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
-
- 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.
- at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
- at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
- at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
- at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
- at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3446)
- at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:638)
- at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
- at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
- at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
- at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:152)
- at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
- at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:62)
- at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:58)
- at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82)
- at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
- at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.syncExecute(ExecutorEngine.java:97)
- at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.parallelExecute(ExecutorEngine.java:93)
- at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.execute(ExecutorEngine.java:76)
- at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:68)
- at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:51)
- at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:129)
- at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:148)
- at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:145)
- at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
- at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
- 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.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
- at com.sun.proxy.$Proxy92.query(Unknown Source)
- at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69)
- at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
- at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
- at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165)
- at com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:134)
- at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:59)
- at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
- at com.sun.proxy.$Proxy91.query(Unknown Source)
- at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
- at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
- 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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
- ... 77 more
- Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
-
- 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.
- at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
- at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
- at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
- at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
- at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
- at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
- at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
- at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
- at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:581)
- at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:761)
- at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:700)
- at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1051)
- at com.mysql.cj.NativeSession.execSQL(NativeSession.java:665)
- at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:893)
- ... 120 more
- Caused by: java.net.SocketTimeoutException: Read timed out
- at java.net.SocketInputStream.socketRead0(Native Method)
- at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
- at java.net.SocketInputStream.read(SocketInputStream.java:171)
- at java.net.SocketInputStream.read(SocketInputStream.java:141)
- at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107)
- at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150)
- at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180)
- at java.io.FilterInputStream.read(FilterInputStream.java:133)
- at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)
- at com.mysql.cj.protocol.a.SimplePacketReader.readHeaderLocal(SimplePacketReader.java:81)
- at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
- at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
- at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52)
- at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41)
- at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54)
- at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44)
- at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:575)
- ... 125 more
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。