使用mysql报Communications link failure异常办理

配景

线上使用polarDB，基于mysql(5.7)，架构为springboot+mybatisplus+durid连接池，部门业务场景涉及大表更新和查询操作，在查询慢sql且超过肯定时间时就会报出"Communications link failure"异常，主要体现在界面查询或定时任务处置惩罚大批量数据是执行数据库的更新、查询异常。

现象

不区分业务高峰期，只要慢sql超过肯定时间（本例为10秒）无论如何更新设置连接池参数和polarDB实例参数设置，异常仍旧存在。
异常如下：

1. ### Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
3. The last packet successfully received from the server was 10,011 milliseconds ago. The last packet sent successfully to the server was 10,011 milliseconds ago.
4. ; Communications link failure
6. The last packet successfully received from the server was 10,011 milliseconds ago. The last packet sent successfully to the server was 10,011 milliseconds ago.; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
8. The last packet successfully received from the server was 10,011 milliseconds ago. The last packet sent successfully to the server was 10,011 milliseconds ago.
9.         at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:100)
10.         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
11.         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
12.         at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
13.         at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
14.         at com.sun.proxy.$Proxy152.selectList(Unknown Source)
15.         at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
16.         at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:121)
17.         at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:85)
18.         at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
19.         at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
20.         at com.sun.proxy.$Proxy535.selectPage(Unknown Source)
21.         at sun.reflect.GeneratedMethodAccessor2431.invoke(Unknown Source)
22.         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

复制代码

办理方案记载

根据ChatGPT或网上搜索的办理方案，对连接池大小、时间参数和连接池回收设置等都进行了优化设置，如max-wait、connect-timeout等超时设置，如下：

1. #mysql配置
2. spring.datasource.type = com.alibaba.druid.pool.DruidDataSource
3. #spring.datasource.druid.driverClassName = net.sf.log4jdbc.sql.jdbcapi.DriverSpy
4. spring.datasource.druid.driverClassName = com.mysql.cj.jdbc.Driver
5. #mysql 8.x使用 spring.datasource.druid.url
6. spring.datasource.druid.url = jdbc:mysql://xxx:3306/xxx?serverTimezone=GMT%2B8&characterEncoding=utf8&autoReconnect=true
7. spring.datasource.druid.username = xxx
8. spring.datasource.druid.password = xxx
9. spring.datasource.druid.initial-size = 5
10. spring.datasource.druid.min-idle = 10
11. spring.datasource.druid.max-active = 500
12. spring.datasource.druid.max-wait = 30000
13. spring.datasource.druid.connect-timeout = 30000
14. spring.datasource.druid.query-timeout = 30000
15. spring.datasource.druid.transaction-query-timeout = 30000
16. spring.datasource.druid.time-between-eviction-runs-millis = 60000
17. spring.datasource.druid.min-evictable-idle-time-millis = 300000
18. spring.datasource.druid.max-evictable-idle-time-millis = 900000
19. spring.datasource.druid.test-while-idle = true
20. spring.datasource.druid.test-on-borrow = false
21. spring.datasource.druid.test-on-return = false
22. spring.datasource.druid.validation-query = select 1
23. spring.datasource.druid.webStatFilter.enabled = true
24. spring.datasource.druid.stat-view-servlet.enabled = true
25. spring.datasource.druid.stat-view-servlet.url-pattern = /druid/*
26. spring.datasource.druid.stat-view-servlet.reset-enable = false
27. spring.datasource.druid.filter.stat.enabled = true
28. spring.datasource.druid.filter.stat.log-slow-sql = true
29. spring.datasource.druid.filter.stat.slow-sql-millis = 1000
30. spring.datasource.druid.filter.stat.merge-sql = true
31. spring.datasource.druid.filter.wall.config.multi-statement-allow = true

复制代码

polarDB/mysql实例设置 connect_timeout参数超过10秒；
题目得不到办理，业务层对索引优化和强制索引等都进行了处置惩罚，部门数据迁移到ES/IOTDB，但根本题目在于MYSQL中仍旧存在不可避免的大表慢sql，导致该题目仍旧存在。
最后在druid的github issue中找到了高度相似题目，办理方案如下：
连接设置url中增长socketTimeout参数如下，

1. spring.datasource.druid.url = jdbc:mysql://xxx:3306/xxx?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false&autoReconnect=true&socketTimeout=30000

复制代码

重启服务办理题目。
留意，该方式仅办理雷同"The last packet successfully received from the server was 10,011 milliseconds ago. The last packet sent successfully to the server was 10,011 milliseconds ago."的异常提示题目。
根据堆栈或连接池druid DruidDataSource溯源源码，使用spring.datasource.druid.socket-timeout设置存在参数覆盖题目，即使使用了connect-timeout设置也无法办理该异常。

至于为什么部门查询未走索引导致慢sql导致超时题目，和索引次序布局、查询条件、执行优化器等有很大关系，编码优化、索引优化或使用升级修复执行器索引的db版本，也是开辟人员需要评估和优化的点。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。