testWhileIdle无效及最大连接问题

[ybqiang]

版本：1.0.24 JDK1.8 springboot项目

spring.datasource.initialSize=5 spring.datasource.minIdle=5 spring.datasource.maxActive=50 spring.datasource.maxWait=30000 spring.datasource.timeBetweenEvictionRunsMillis=60000 spring.datasource.minEvictableIdleTimeMillis=300000 spring.datasource.validationQuery=SELECT 'X' spring.datasource.testWhileIdle=true spring.datasource.testOnBorrow=false spring.datasource.testOnReturn=false spring.datasource.poolPreparedStatements=true spring.datasource.maxPoolPreparedStatementPerConnectionSize=20 spring.datasource.filters=stat spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

遇到异常 Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 87,933,951 milliseconds ago. The last packet sent successfully to the server was 87,933,953 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 128,793,500 milliseconds ago. The last packet sent successfully to the server was 128,793,501 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. at sun.reflect.GeneratedConstructorAccessor99.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3746) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2509) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931) at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929) at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:131) at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77) at sun.reflect.GeneratedMethodAccessor139.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMeth

添加autoReconnect=true后解决该异常，疑问：testWhileIdle=true 是不是也可以处理这个异常，是不是testWhileIdle=true 未生效？

解决该异常后，还有 com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 30000, active 30, maxActive 30 我压测并发1000，也没有再次压出这个异常 排查后，未发现泄露；

啥原因造成的？帮忙看看

[wenshao]

可以配置keepAlive https://github.com/alibaba/druid/wiki/KeepAlive_cn

[wenshao]

@lihengming

[ybqiang]

com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 30000, active 30, maxActive 30 有可能会导致像这样，连接数被打满的异常吗？

我配置了 这三个参数，未发现有断开连接的异常； datasource.setRemoveAbandoned(true); datasource.setRemoveAbandonedTimeout(180); datasource.setLogAbandoned(true);

对服务进行压测并发1000，也没有连接数被打满的情况；太奇怪了。。。

[ioandy]

@ybqiang 请教下： Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 87,933,951 milliseconds ago. The last packet sent successfully to the server was 87,933,953 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. 这个问题怎么解决的？