报错信息:
Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 304,030,789 milliseconds ago. The last packet sent successfully to the server was 304,030,789 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 org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy6.getDevByIpAdnDevNo(Unknown Source)
at com.xuanyutech.processor.imms.netty.ProductionCapacityProcessor.signalTimeDeal(ProductionCapacityProcessor.java:117)
at com.xuanyutech.processor.imms.netty.ProductionCapacityProcessor.excute(ProductionCapacityProcessor.java:71)
at com.xuanyutech.processor.imms.netty.NettyHandlerProcessor.channelRead(NettyHandlerProcessor.java:89)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:323)
at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:310)
at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:426)
at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:278)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340)
at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1434)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:965)
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:163)
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:656)
at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:591)
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:508)
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:470)
at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:909)
at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.lang.Thread.run(Thread.java:745)
总的来说,就是数据库连接池失效。
对于连接池失效的解决方法网上也很多,比如修改mysql的连接超时时间,比如修改配置
<property name="poolMaximumActiveConnections" value="200" />
<property name="poolMaximumIdleConnections" value="20" />
<property name="poolMaximumCheckoutTime" value="60" />
<property name="poolPingEnabled" value="true"/>
<property name="poolPingQuery" value="select 1"/>
<property name="poolPingConnectionsNotUsedFor" value="28000000"/>
但经过配置之后还是会出现连接池报错,重启程序之后就能恢复。后经排查,主要是由于代码的逻辑问题导致的超时。
先介绍一下项目背景,该部分主要是项目的数据处理端,主要作用的和数据采集端进行数据通信,处理数据。利用netty来进行数据通信。其中用的框架只有netty和mybatis。在netty,客户端连接成功之后会回调逻辑处理器的channelActive()方法 , 而不管是服务端还是客户端 , 收到数据之后都会调用channelRead()方法。由于程序里没有整合spring等其他框架,所以在程序里的channelActive()里写了数据库的初始化连接。所以每当有一个客户端连接时,就会初始化数据库的连接。当有数据过来时,会通过责任链选择适合的对象进行处理。当连接时间过长时,数据库的连接并没有得到关闭。就会导致再次使用连接时会报连接超时的错误。