search

alibaba / druid

阿里云计算平台DataWorks(https://help.aliyun.com/document_detail/137663.html) 团队出品，为监控而生的数据库连接池
https://github.com/alibaba/druid/wiki
Apache License 2.0
27.77k stars 8.55k forks source link

CommunicationsException: The last packet successfully received from the server was 901,702 milliseconds ago. The last packet sent successfully to the server was 901,705 milliseconds ago. #5889

Open waterystone opened 2 months ago

waterystone commented 2 months ago

大佬好，我们用Druid一直偶发CommunicationsException，升级到最新的1.2.22仍然出现，辛苦帮看下我们哪的姿势不对，万分感谢。 dbtype:MySQL dbversion:5.7 druid verion:1.2.22

配置如下：

druid:
  # 参考配置见：https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE
  datasource:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://xyz.db.abc.com:3980/dbzz_xyz?useSSL=false&useUnicode=true&characterEncoding=utf-8
    username: ***
    password: ***

    initialSize: 5
    # 连接池只应该有maxPoolSize和minPoolSize，druid只保留了maxActive和minIdle，分别相当于maxPoolSize和minPoolSize。
    minIdle: 5
    maxActive: 100
    # 获取连接等待超时的时间（默认会使用公平锁）
    maxWait: 3000

    # 连接泄露检查，打开removeAbandoned功能，连接从连接池借出后，长时间不归还，将触发强制回连接。回收周期随timeBetweenEvictionRunsMillis进行，如果连接为从连接池借出状态，并且未执行任何sql，并且从借出时间起已超过removeAbandonedTimeout时间，则强制归还连接到连接池中。
    # 对性能会有一些影响，建议怀疑存在泄漏之后再打开，不建议在生产环境中使用。
    removeAbandoned: true
    # 超过时间限制多长，单位是秒
    removeAbandonedTimeout: 600
    # 关闭abanded连接时输出错误日志
    logAbandoned: true

    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    minEvictableIdleTimeMillis: 300000
    # 用来检测连接是否有效的sql，要求是一个查询语句
    validationQuery: SELECT 1
    # 申请连接的时候检测
    testWhileIdle: true
    #申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能
    testOnBorrow: false
    #归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能
    testOnReturn: false

    #打开PSCache，并且指定每个连接上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 50

    #属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：
    #监控统计用的filter:stat 日志用的filter:log4j 防御SQL注入的filter:wall
    filters: stat, log4j2

异常信息如下：

[05-06 19:55:40 698] 6638c1b60a2924980000003107f7023e-ce92e23554c3e814-false ScfRequestProcessor_98 ERROR c.a.d.f.l.Log4j2Filter:151 - {conn-10034, pstmt-25029} execute error. select

    id, remark, create_time, app_id, parent_id, business_id, data_code, data_status, extend_data, update_time, data_name, data_path, allow_application

    from t_n_data
    where app_id = ?
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 901,702 milliseconds ago.  The last packet sent successfully to the server was 901,705 milliseconds ago.
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_191]
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_191]
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_191]
  at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_191]
  at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:981) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2176) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1964) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3316) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:463) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3040) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2288) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2681) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3462) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3460) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3460) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3460) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:158) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483) ~[druid-1.2.22.jar!/:?]
  at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.4.2.jar!/:3.4.2]
  at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111) ~[pagehelper-5.2.1.jar!/:?]
  at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.4.2.jar!/:3.4.2]
  at com.sun.proxy.$Proxy119.query(Unknown Source) ~[?:?]
  at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.2.jar!/:3.4.2]
  at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source) ~[?:?]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191]
  at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_191]
  at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.1.jar!/:1.3.1]
  at com.sun.proxy.$Proxy82.selectList(Unknown Source) ~[?:?]
  at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) ~[mybatis-spring-1.3.1.jar!/:1.3.1]
  at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[mybatis-3.4.2.jar!/:3.4.2]
  at com.sun.proxy.$Proxy87.getDataByAppId(Unknown Source) ~[?:?]
  at com.ttt.uniqueauth.service.DataServiceImpl.listSiteByUserApp(DataServiceImpl.java:324) ~[classes!/:?]
  at com.ttt.uniqueauth.service.DataServiceImpl$$FastClassBySpringCGLIB$$7ef8379e.invoke(<generated>) ~[classes!/:?]
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at com.ttt.uniqueauth.config.CommonLogHandler.around(CommonLogHandler.java:33) ~[classes!/:?]
  at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source) ~[?:?]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191]
  at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_191]
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at com.ttt.uniqueauth.service.DataServiceImpl$$EnhancerBySpringCGLIB$$f2eade6c.listSiteByUserApp(<generated>) ~[classes!/:?]
  at sun.reflect.GeneratedMethodAccessor171.invoke(Unknown Source) ~[?:?]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191]
  at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_191]
  at com.bj.ttt.rpc.extension.impl.protocol.ScfServerInvoker.invoke(ScfServerInvoker.java:52) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerBizExecMonitorFilter.invoke(ServerBizExecMonitorFilter.java:18) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerExceptionFilter.invoke(ServerExceptionFilter.java:17) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerLimitingFilter.invoke(ServerLimitingFilter.java:84) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerCallPermissionFilter.invoke(ServerCallPermissionFilter.java:62) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerTimeoutFilter.invoke(ServerTimeoutFilter.java:41) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerMonitorFilter.invoke(ServerMonitorFilter.java:39) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerTraceFilter.invoke(ServerTraceFilter.java:80) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerLoggerFilter.invoke(ServerLoggerFilter.java:46) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerTransmittableContextFilter.invoke(ServerTransmittableContextFilter.java:33) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerRpcContextFilter.invoke(ServerRpcContextFilter.java:49) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerDebugLoggerFilter.invoke(ServerDebugLoggerFilter.java:42) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.protocol.scfv2.ScfV2ServerHandler.invoke(ScfV2ServerHandler.java:242) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.protocol.scfv2.ScfV2ServerHandler.doHandleRequest(ScfV2ServerHandler.java:164) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.protocol.scfv2.ScfV2ServerHandler.handleRequest(ScfV2ServerHandler.java:150) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.protocol.scfv2.ScfV2ServerHandler.lambda$channelRead$1(ScfV2ServerHandler.java:86) ~[rpc.all-2.9.6.jar!/:?]
  at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:60) ~[?:?]
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_191]
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_191]
  at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191]
Caused by: java.net.SocketException: Connection reset
  at java.net.SocketInputStream.read(SocketInputStream.java:210) ~[?:1.8.0_191]
  at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[?:1.8.0_191]
  at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:100) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:143) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:173) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2954) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2159) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  ... 94 more
[05-06 19:55:40 698] 6638c1b60a2924980000003107f7023e-ce92e23554c3e814-false ScfRequestProcessor_98 ERROR c.a.d.p.DruidPooledStatement:370 - CommunicationsException, druid version 1.2.22, jdbcUrl : jdbc:mysql://xyz.db.abc.com:3980/dbzz_xyz?useSSL=false&useUnicode=true&characterEncoding=utf-8, testWhileIdle true, idle millis 901804, minIdle 5, poolingCount 12, timeBetweenEvictionRunsMillis 60000, lastValidIdleMillis 901804, driver com.mysql.jdbc.Driver, exceptionSorter com.alibaba.druid.pool.vendor.MySqlExceptionSorter
[05-06 19:55:40 699] 6638c1b60a2924980000003107f7023e-ce92e23554c3e814-false ScfRequestProcessor_98 ERROR c.a.d.p.DruidDataSource:2012 - {conn-10034} discard
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 901,702 milliseconds ago.  The last packet sent successfully to the server was 901,705 milliseconds ago.
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_191]
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_191]
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_191]
  at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_191]
  at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:981) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2176) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1964) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3316) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:463) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3040) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2288) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2681) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3462) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3460) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3460) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3460) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:158) ~[druid-1.2.22.jar!/:?]
  at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483) ~[druid-1.2.22.jar!/:?]
  at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.4.2.jar!/:3.4.2]
  at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111) ~[pagehelper-5.2.1.jar!/:?]
  at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.4.2.jar!/:3.4.2]
  at com.sun.proxy.$Proxy119.query(Unknown Source) ~[?:?]
  at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.2.jar!/:3.4.2]
  at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source) ~[?:?]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191]
  at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_191]
  at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.1.jar!/:1.3.1]
  at com.sun.proxy.$Proxy82.selectList(Unknown Source) ~[?:?]
  at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) ~[mybatis-spring-1.3.1.jar!/:1.3.1]
  at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75) ~[mybatis-3.4.2.jar!/:3.4.2]
  at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[mybatis-3.4.2.jar!/:3.4.2]
  at com.sun.proxy.$Proxy87.getDataByAppId(Unknown Source) ~[?:?]
  at com.ttt.uniqueauth.service.DataServiceImpl.listSiteByUserApp(DataServiceImpl.java:324) ~[classes!/:?]
  at com.ttt.uniqueauth.service.DataServiceImpl$$FastClassBySpringCGLIB$$7ef8379e.invoke(<generated>) ~[classes!/:?]
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at com.ttt.uniqueauth.config.CommonLogHandler.around(CommonLogHandler.java:33) ~[classes!/:?]
  at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source) ~[?:?]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191]
  at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_191]
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) ~[spring-aop-5.3.4.jar!/:5.3.4]
  at com.ttt.uniqueauth.service.DataServiceImpl$$EnhancerBySpringCGLIB$$f2eade6c.listSiteByUserApp(<generated>) ~[classes!/:?]
  at sun.reflect.GeneratedMethodAccessor171.invoke(Unknown Source) ~[?:?]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191]
  at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_191]
  at com.bj.ttt.rpc.extension.impl.protocol.ScfServerInvoker.invoke(ScfServerInvoker.java:52) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerBizExecMonitorFilter.invoke(ServerBizExecMonitorFilter.java:18) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerExceptionFilter.invoke(ServerExceptionFilter.java:17) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerLimitingFilter.invoke(ServerLimitingFilter.java:84) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerCallPermissionFilter.invoke(ServerCallPermissionFilter.java:62) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerTimeoutFilter.invoke(ServerTimeoutFilter.java:41) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerMonitorFilter.invoke(ServerMonitorFilter.java:39) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerTraceFilter.invoke(ServerTraceFilter.java:80) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerLoggerFilter.invoke(ServerLoggerFilter.java:46) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerTransmittableContextFilter.invoke(ServerTransmittableContextFilter.java:33) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerRpcContextFilter.invoke(ServerRpcContextFilter.java:49) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.server.filter.ServerDebugLoggerFilter.invoke(ServerDebugLoggerFilter.java:42) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.api.config.Service$ServerFilterInvoker.invoke(Service.java:163) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.protocol.scfv2.ScfV2ServerHandler.invoke(ScfV2ServerHandler.java:242) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.protocol.scfv2.ScfV2ServerHandler.doHandleRequest(ScfV2ServerHandler.java:164) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.protocol.scfv2.ScfV2ServerHandler.handleRequest(ScfV2ServerHandler.java:150) ~[rpc.all-2.9.6.jar!/:?]
  at com.bj.ttt.rpc.extension.impl.protocol.scfv2.ScfV2ServerHandler.lambda$channelRead$1(ScfV2ServerHandler.java:86) ~[rpc.all-2.9.6.jar!/:?]
  at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:60) ~[?:?]
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_191]
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_191]
  at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191]
Caused by: java.net.SocketException: Connection reset
  at java.net.SocketInputStream.read(SocketInputStream.java:210) ~[?:1.8.0_191]
  at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[?:1.8.0_191]
  at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:100) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:143) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:173) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2954) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2159) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
  ... 94 more
lizongbo commented 2 months ago

你这个看上去db操作频率很低，不然也不会连接等到15分钟超时。 直接把keepAlive设置成true,testOnBorrow也配置成true吧，参考我更新后的最新属性信息介绍和配置示例。

https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8

waterystone commented 2 months ago

你这个看上去db操作频率很低，不然也不会连接等到15分钟超时。 直接把keepAlive设置成true,testOnBorrow也配置成true吧，参考我更新后的最新属性信息介绍和配置示例。

https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8

我们的QPS其实很高。按我们的如下配置，理论上，每次借用连接时，如果其空闲时间超过60S，会进行一次探测；而且每隔60S也会对空闲时间超过300S的进行关闭，这样不应该会借用到空闲900S的连接吧。不知道我这么理解对不对？ timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 testWhileIdle: true

lizongbo commented 2 months ago

你这个看上去db操作频率很低，不然也不会连接等到15分钟超时。 直接把keepAlive设置成true,testOnBorrow也配置成true吧，参考我更新后的最新属性信息介绍和配置示例。 https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8

我们的QPS其实很高。按我们的如下配置，理论上，每次借用连接时，如果其空闲时间超过60S，会进行一次探测；而且每隔60S也会对空闲时间超过300S的进行关闭，这样不应该会借用到空闲900S的连接吧。不知道我这么理解对不对？ timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 testWhileIdle: true 确实诡异，只能后面加点日志来分析连接的物理连接创建和保活的触发时间点了。 你可以参考我的配置先调整连接池的配置。

liuyandeng commented 1 month ago

我也有这类问题,请问怎么解决呢,我的问题是#5920

zrlw commented 1 month ago 
maxActive: 100

除非每台机器配置的cpu核数很多，或者你有耗时很长的单条sql或db事务，否则用不了这么多的连接，可以找台机器netstat动态监控一下你应用创建的数据库连接数。 如果你不配keepAlive为true（默认false），testWhileIdle这个参数配成true也是不起作用的，testWhileIdle（防止空闲连接被防火墙或数据库服务器直接reset，注意，被reset的连接属于tcp底层直接断开，但客户端看这些连接的状态仍然为正常状态，但这些连接无法继续使用。）生效的前提条件是keepAlive要设为true

zrlw commented 1 month ago

此类问题过一段时间就会冒出来，如果看过wiki配置说明，应该知道连接保活的前提条件是keepAlive选项设置为true

zrlw commented 1 month ago

@lizongbo 建议下一个版本把keepAlive这个选项默认值改为true

lizongbo commented 1 month ago

@lizongbo 建议下一个版本把keepAlive这个选项默认值改为true

直接改为true会有130个单测失败，周末温少会发1.2.23，等发完版本之后再来调整

lhq2253 commented 3 weeks ago

@lizongbo 大佬，设置keepAlive以后你这个问题解决了么

liuyandeng commented 3 weeks ago

@lizongbo 大佬，设置keepAlive以后你这个问题解决了么

我试了,不行

lizongbo commented 3 weeks ago

@lizongbo 大佬，设置keepAlive以后你这个问题解决了么

我试了,不行

我们项目没有遇到这样的问题，参考我更新之后的wiki的配置 https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8 这里面有我们项目实际用到的配置示例，可以参考这个调整你们的配置。

Kang-Yang commented 6 days ago

请问解决了吗？我这边是每天早上9点左右就会定时出现这个问题