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

太过爱你忘了你带给我的痛 2022-11-10 14:13 198阅读 0赞

# 问题描述 #

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

# 分析 #

查看了`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`异常。