Druid+mysql+mybatis做定时批量操作出现CommunicationsException: Communications link failure

boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception { if (conn.isClosed()) { return false; } if (this.usePingMethod) { if (conn instanceof DruidPooledConnection) { conn = ((DruidPooledConnection) conn).getConnection(); } if (conn instanceof ConnectionProxy) { conn = ((ConnectionProxy) conn).getRawObject(); } if (this.clazz.isAssignableFrom(conn.getClass())) { if (validationQueryTimeout < 0) { validationQueryTimeout = 1; } try { this.ping.invoke(conn, new Object[]{Boolean.valueOf(true), Integer.valueOf(validationQueryTimeout * 1000)}); } catch (InvocationTargetException e) { Throwable cause = e.getCause(); if (cause instanceof SQLException) { throw (SQLException) cause; } throw e; } return true; } } String query = validateQuery; if (validateQuery == null || validateQuery.isEmpty()) { query = "SELECT 1"; } stmt = null; rs = null; try { stmt = conn.createStatement(); if (validationQueryTimeout > 0) { stmt.setQueryTimeout(validationQueryTimeout); } rs = stmt.executeQuery(query); return true; } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); } }

解决的方法：

　　使用应用级别的心跳检测，不使用系统级别的 ping（禁止mysql connector自带的ping机制）

　　在jvm参数配置 -Ddruid.mysql.usePingMethod=false

附：

application.properties中的配置

## 数据库访问配置, 使用druid数据源
spring.datasource.druid.db-type=mysql
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url = jdbc:mysql://localhost:3306/bim?serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false
spring.datasource.druid.username=bimengine
spring.datasource.druid.password=fgBQLZpgDaxH7xuu
# 连接池配置
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20
# 配置获取连接等待超时的时间
spring.datasource.druid.max-wait=30000
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.validation-query= select 1
spring.datasource.druid.validation-query-timeout=60000
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.time-between-eviction-runs-millis=60000

# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-open-prepared-statements=20
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，‘wall‘用于防火墙
spring.datasource.druid.filters=stat,wall
# WebStatFilter配置
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=‘*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*‘
#是否启用StatFilter默认值true
spring.datasource.druid.stat-view-servlet.enabled=true
# 访问路径为/druid时，跳转到StatViewServlet
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
#druid监控管理界面登录帐号
spring.datasource.druid.stat-view-servlet.login-username=admin
#druid监控管理界面登录密码
spring.datasource.druid.stat-view-servlet.login-password=admin@tydt
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1

# 配置StatFilter
spring.datasource.druid.filter.stat.log-slow-sql=true

 

Druid+mysql+mybatis做定时批量操作出现CommunicationsException: Communications link failure

标签：read   bool   nic   操作   min   discard   str   ons   username