由mysql超时调整引发的线上故障

问题描述

在线上访问医疗机构列表，随机出现访问失败。

分析

查看了sql执行计划，并不是慢查询导致。
查看报错日志，摘录如下：

org.springframework.dao.DataAccessResourceFailureException: could not extract ResultSet; nested exception is org.hibernate.exception.JDBCConnectionException: could not extract ResultSet

Caused by: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 55,964 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.

Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

发现线上有大量通信链路故障的报错。
后来询问了DBA，得知对应数据库的超时时间被调整了。

代码分析

druid配置

//配置获取连接等待超时的时间 一分钟
druidDataSource.setMaxWait(60 * 1000);

Mysql超时配置查询：

# 查看超时配置
show variables like '%timeout%';

查询结果如下：

connect_timeout,10
delayed_insert_timeout,300
have_statement_timeout,YES
innodb_flush_log_at_timeout,1
innodb_lock_wait_timeout,50
innodb_rollback_on_timeout,OFF
interactive_timeout,60
lock_wait_timeout,31536000
net_read_timeout,30
net_write_timeout,600
rpl_stop_slave_timeout,31536000
slave_net_timeout,60
wait_timeout,10

可以看到，wait_timeout=10，而druid数据库连接池配置的等待超时时间为60s。相当于数据库连接池里维护的连接超时时间还没到，就被数据库断开了连接，进而导致了Communications link failure异常。