ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > Communications link fai分析解决

Communications link fai分析解决

原创 MySQL 作者：godsonfly 时间：2017-05-19 09:35:42 0 删除 编辑

 今天晚上一个财务子系统上线，部署完成后，测试反馈程序会抽风，一会儿正常，一会儿异常，报如下错误：

nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link fai

 

通过查找资料，可能是wait_timeout设置过小导致的，尝试加大wait_timeout的值后，系统恢复正常。

更改过程如下：

##查看MySQL配置
root@localhost:mysql.sock [finance]>show global variables like 'wait_timeout'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 10    |
+---------------+-------+
1 row in set (0.01 sec)
root@localhost:mysql.sock [finance]>show global variables like '%interactive_timeout%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
 
##先修改全局的值为300，重启后将恢复原值
root@localhost:mysql.sock [finance]>set global wait_timeout=300;
root@localhost:mysql.sock [finance]>show global variables like 'wait_timeout';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| wait_timeout             | 300      |
+--------------------------+----------+
 
##修改my.cnf，保证MySQL重启后依然生效
[mysqld]  
wait_timeout=300  
 
## wait_timeout扩展
MySQL服务器默认的“wait_timeout”默认是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时，MySQL将自动断开该连接，而连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致上面的报错。
解决方法：
 
修改my.cnf： 
[mysqld]  
wait_timeout=31536000  
interactive_timeout=31536000   
将过期时间修改为1年。 
 
Error Log日志如下：

点击(此处)折叠或打开

  1. The last packet successfully received from the server was 9,369 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
  2. ; SQL []; Communications link failure
  3.  
  4. The last packet successfully received from the server was 9,369 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
  5.  
  6. The last packet successfully received from the server was 9,369 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
  7. at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)
  8. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
  9. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
  10. at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
  11. at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
  12. at com.sun.proxy.$Proxy11.selectList(Unknown Source)
  13. at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
  14. at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
  15. at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
  16. at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
  17. at com.sun.proxy.$Proxy12.searchRules(Unknown Source)
  18. at com.tianlian.order.server.finance.service.impl.poundage.WPOSPoundageServiceImpl.searchRules(WPOSPoundageServiceImpl.java:55)
  19. at com.tianlian.order.server.finance.service.impl.poundage.WPOSPoundageServiceImpl$$FastClassBySpringCGLIB$$aec2fff5.invoke(<generated>)
  20. at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
  21. at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
  22. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
  23. at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:72)
  24. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  25. at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
  26. at com.tianlian.order.server.finance.service.impl.poundage.WPOSPoundageServiceImpl$$EnhancerBySpringCGLIB$$db4e378e.searchRules(<generated>)
  27. at com.alibaba.dubbo.common.bytecode.Wrapper0.invokeMethod(Wrapper0.java)
  28. at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)
  29. at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72)
  30. at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53)
  31. at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:64)
  32. at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
  33. at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:42)
  34. at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
  35. at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75)
  36. at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
  37. at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)
  38. at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
  39. at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:60)
  40. at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
  41. at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:112)
  42. at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
  43. at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)
  44. at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
  45. at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)
  46. at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
  47. at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:108)
  48. at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:84)
  49. at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:170)
  50. at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)
  51. at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:82)
  52. at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
  53. at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
  54. at java.lang.Thread.run(Thread.java:722)
  55. Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14968506/viewspace-2139329/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 没有了~
下一篇： MySQL5.7多源复制
请登录后发表评论 登录
全部评论
godsonfly

注册时间：2010-11-06

  • 博文量
    2
  • 访问量
    3776

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593