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参数中配置。
connectionProperties: connectTimeout=60000;socketTimeout=60000 #1.2.12版本配置失效
connectTimeout: 60000 #1.2.12版本需要单独配置才生效
socketTimeout: 60000 #1.2.12版本需要单独配置才生效3.解决方法
1.升级druid版本到1.2.12以上且使用以上配置中的一种（推荐）
2.使用1.2.12版本使用单独配置的方式
3.降低druid版本到1.2.12以下无需配置该参数（不推荐）
4.完整配置文件（1.2.12及以上版本适用）
spring:
datasource:
type: ***.alibaba.druid.pool.DruidDataSource
druid:
driver-class-name: ***.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,wall5.附录
附录1
org.springframework.dao.RecoverableDataA***essException:
### Error querying database. Cause: ***.mysql.cj.jdbc.exceptions.***municationsException: ***munications link failure
The last packet su***essfully received from the server was 10,017 milliseconds ago. The last packet sent su***essfully 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 o***urred while setting parameters
### SQL: SELECT COUNT(*) FROM user
### Cause: ***.mysql.cj.jdbc.exceptions.***municationsException: ***munications link failure
The last packet su***essfully received from the server was 10,017 milliseconds ago. The last packet sent su***essfully to the server was 10,018 milliseconds ago.
; ***munications link failure
The last packet su***essfully received from the server was 10,017 milliseconds ago. The last packet sent su***essfully to the server was 10,018 milliseconds ago.; nested exception is ***.mysql.cj.jdbc.exceptions.***municationsException: ***munications link failure
The last packet su***essfully received from the server was 10,017 milliseconds ago. The last packet sent su***essfully 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 ***.sun.proxy.$Proxy62.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
at ***.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:122)
at ***.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:87)
at ***.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
at ***.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at ***.sun.proxy.$Proxy68.findPage(Unknown Source)
at ***.fkp.ShardingTestApplicationTests.page(ShardingTestApplicationTests.java:79)
at sun.reflect.NativeMethodA***essorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodA***essorImpl.invoke(NativeMethodA***essorImpl.java:62)
at sun.reflect.DelegatingMethodA***essorImpl.invoke(DelegatingMethodA***essorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.platform.***mons.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 ***.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
at ***.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at ***.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at ***.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at ***.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
at ***.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: ***.mysql.cj.jdbc.exceptions.***municationsException: ***munications link failure
The last packet su***essfully received from the server was 10,017 milliseconds ago. The last packet sent su***essfully to the server was 10,018 milliseconds ago.
at ***.mysql.cj.jdbc.exceptions.SQLError.create***municationsException(SQLError.java:174)
at ***.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at ***.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
at ***.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
at ***.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3446)
at ***.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:638)
at ***.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
at ***.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
at ***.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
at ***.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:152)
at ***.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.NativeMethodA***essorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodA***essorImpl.invoke(NativeMethodA***essorImpl.java:62)
at sun.reflect.DelegatingMethodA***essorImpl.invoke(DelegatingMethodA***essorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at ***.sun.proxy.$Proxy92.query(Unknown Source)
at ***.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 ***.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165)
at ***.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:134)
at ***.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:59)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at ***.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.NativeMethodA***essorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodA***essorImpl.invoke(NativeMethodA***essorImpl.java:62)
at sun.reflect.DelegatingMethodA***essorImpl.invoke(DelegatingMethodA***essorImpl.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: ***.mysql.cj.exceptions.CJ***municationsException: ***munications link failure
The last packet su***essfully received from the server was 10,017 milliseconds ago. The last packet sent su***essfully to the server was 10,018 milliseconds ago.
at sun.reflect.NativeConstructorA***essorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorA***essorImpl.newInstance(NativeConstructorA***essorImpl.java:62)
at sun.reflect.DelegatingConstructorA***essorImpl.newInstance(DelegatingConstructorA***essorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at ***.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at ***.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
at ***.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
at ***.mysql.cj.exceptions.ExceptionFactory.create***municationsException(ExceptionFactory.java:167)
at ***.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:581)
at ***.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:761)
at ***.mysql.cj.protocol.a.NativeProtocol.send***mand(NativeProtocol.java:700)
at ***.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1051)
at ***.mysql.cj.NativeSession.execSQL(NativeSession.java:665)
at ***.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:893)
... 120 more
Caused by: java.***.SocketTimeoutException: Read timed out
at java.***.SocketInputStream.socketRead0(Native Method)
at java.***.SocketInputStream.socketRead(SocketInputStream.java:116)
at java.***.SocketInputStream.read(SocketInputStream.java:171)
at java.***.SocketInputStream.read(SocketInputStream.java:141)
at ***.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107)
at ***.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150)
at ***.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180)
at java.io.FilterInputStream.read(FilterInputStream.java:133)
at ***.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)
at ***.mysql.cj.protocol.a.SimplePacketReader.readHeaderLocal(SimplePacketReader.java:81)
at ***.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
at ***.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
at ***.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52)
at ***.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41)
at ***.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54)
at ***.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44)
at ***.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:575)
... 125 more