DruidPooledStatement.error CommunicationsException；DruidDataSource.error discard connection_com.alibaba.druid.pool.druidpooledstatement-优快云博客

本文链接：https://blog.youkuaiyun.com/yhj674438511/article/details/108467349

请教大家呐！先拜谢啦！！！

问题已解决（更新于2020.9.11 start）

解决过程，一步步逼近mysql，先是不用druid，不用DBCP，依然报错，使用sqlyog并发三个select，也报错。

问题定位在mysql，修改了配置文件，无效。

查看mysql error日志，竟然没有关闭记录，但有启动记录，这貌似说明mysql直接被linux kill了。（mysql会自启）

查看linux日志，果然一堆kill mysql的记录，虽然知道服务器的配置低，知道内存经常逼近100%，但没想到三个并发select就能out of memory，触发oom killer。给排一下队不好么？（select语句是使用mysql自带的全文检索的检索，几百万条测试用的记录）

free -m一看，没有swap，之前一直没在意，然后开了个swap，三个并发不报错了，但会影响其他sql语句的执行效率。

别问为啥不增加内存，没钱！！！

现在问题转换成：有更合适的全文索引引擎么？要占资源少！求推荐

遗留一个问题未验证：在未开swap之前，从命令行运行 mysql>select.....;select.....;select.....;,没报错，莫非这里的三个select是顺序执行的？（貌似是顺序执行的，多次测试，每次输入结果的顺序都是和三个select对应一致，如果是并行执行，可能会有顺序不一致的情况，不排除测试次数太少而巧合的情况）

（更新于2020.9.11 end）

最近用druid做连接池，总报错误。

错误复现：

客户端java编写，运行在win10，server端tomcat8.5.50（druid1.1.22作连接池），数据库mysql80-community-release，运行在同一linux系统。

通过点击前端几个菜单按钮去请求mysql数据库，几个按钮的只是传的查询参数不同，但都是执行的一个server类，这个数据库查询是用的全文索引，mysql大概会执行6-10秒不等，才会返回查询结果。

如若点击按钮的速度慢，比如让每一次的查询结果都返回后，再点击按钮，不会出错，正常返回结果。

如若点击按钮速度过快，比如每秒点击两个按钮的频率，也就是不等着前一个或几个查询返回结果，直接点击下一个按钮，这样快速点个五六次，就报错了，有时甚至快速点两个按钮，就报错了，啥数据都没返回。

啥都不操作，过个几秒钟，再点击某一个按钮，又能正常返回结果！这种情况是tomcat没有挂掉。

还有就是，tomcat直接挂了，只能重启tomcat，然后还是有以上问题。

网上看了很多解决方法，没找到能够解决的，但貌似和mysql的wait_timeout参数无关。

druid配置如下：

druid工具类部分如下：

请求的webservlet：（每一个按钮也就是传的year参数不同，其他一样）

stateMent = (Statement) connection.createStatement();
result = stateMent.executeQuery(sql);//第118行，sql为具体执行语句

。。。

catalina.out打印信息如下：

getConnection /点一次按钮，没问题，正常返回结果
closeAll

getConnection /快速点两个按钮，就报错了，啥数据都没返回
getConnection
closeAll
08-Sep-2020 14:33:43.282 SEVERE [http-nio-80-exec-16] com.alibaba.druid.pool.DruidPooledStatement.error CommunicationsException, druid version 1.1.22, jdbcUrl : jdbc:mysql://localhost:2636/mydatabase?serverTimezone=Asia/Shanghai, testWhileIdle true, idle millis 33972, minIdle 5, poolingCount 4, timeBetweenEvictionRunsMillis 60000, lastValidIdleMillis 33972, driver com.mysql.cj.jdbc.Driver, exceptionSorter com.alibaba.druid.pool.vendor.MySqlExceptionSorter
08-Sep-2020 14:33:43.309 SEVERE [http-nio-80-exec-16] com.alibaba.druid.pool.DruidDataSource.error discard connection
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 33,656 milliseconds ago. The last packet sent successfully to the server was 33,656 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.StatementImpl.executeQuery(StatementImpl.java:1200)
       at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2883)
       at com.alibaba.druid.filter.FilterAdapter.statement_executeQuery(FilterAdapter.java:2514)
       at com.alibaba.druid.filter.FilterEventAdapter.statement_executeQuery(FilterEventAdapter.java:302)
       at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2880)
       at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeQuery(StatementProxyImpl.java:221)
       at com.alibaba.druid.pool.DruidPooledStatement.executeQuery(DruidPooledStatement.java:297)
       at servlet.getSearchArticleByYear.doPost(getSearchArticleByYear.java:118)
       at servlet.getSearchArticleByYear.service(getSearchArticleByYear.java:65)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
       at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
       at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
       at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
       at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
       at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
       at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:609)
       at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
       at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:810)
       at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1623)
       at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
       at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
       at java.lang.Thread.run(Thread.java:748)
   Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet successfully received from the server was 33,656 milliseconds ago. The last packet sent successfully to the server was 33,656 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:539)
       at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:703)
       at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:642)
       at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:941)
       at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:887)
       at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1073)
       at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1168)
       ... 34 more
   Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
       at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:67)
       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:533)
       ... 40 more
closeAll
08-Sep-2020 14:33:43.318 SEVERE [Druid-ConnectionPool-Create-1550823919] com.alibaba.druid.pool.DruidDataSource.error create connection SQLException, url: jdbc:mysql://localhost:2636/mydatabase?serverTimezone=Asia/Shanghai, errorCode 0, state 08S01
   com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
       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.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
       at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
       at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
       at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
       at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:156)
       at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:218)
       at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:150)
       at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1646)
       at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1710)
       at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2779)
   Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
       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.NativeSocketConnection.connect(NativeSocketConnection.java:91)
       at com.mysql.cj.NativeSession.connect(NativeSession.java:144)
       at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956)
       at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826)
       ... 9 more
   Caused by: java.net.ConnectException: Connection refused (Connection refused)
       at java.net.PlainSocketImpl.socketConnect(Native Method)
       at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
       at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
       at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
       at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
       at java.net.Socket.connect(Socket.java:607)
       at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:155)
       at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65)
       ... 12 more