DNSBrood 开发日志——确保数据库连接的等待时间足够长

做 DNS Server 已经快一个月了，期间遇到各种各样零零碎碎的问题，我觉得以日志的形式记录下来应该能够有所帮助。

问题描述

目前项目已经达到正向解析及记录的添加删除能够正常执行的阶段，不过今天在测试添加用户唯一标识的时候突然报出下列错误：

17-07-25 15:09:07,476 WARN  com.liumapp.DNSQueen.worker.process.WokerEar(WokerEar.java:96) ## Oops! Maybe not inited 
org.springframework.dao.DataAccessResourceFailureException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 88,402,280 milliseconds ago.  The last packet sent successfully to the server was 88,402,280 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
### The error may exist in com/liumapp/DNSBrood/dao/ZonesDAO.java (best guess)
### The error may involve com.liumapp.DNSBrood.dao.ZonesDAO.getByDomain-Inline
### The error occurred while setting parameters
### SQL: select * from zones where domain=?
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 88,402,280 milliseconds ago.  The last packet sent successfully to the server was 88,402,280 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
; SQL []; The last packet successfully received from the server was 88,402,280 milliseconds ago.  The last packet sent successfully to the server was 88,402,280 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 88,402,280 milliseconds ago.  The last packet sent successfully to the server was 88,402,280 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

解决方案

这么长一段，其实就一个 com.mysql.jdbc.exceptions.jdbc4.CommunicationsException，分析了一下，DNS Server 肯定要长期运行在服务器中，而系统对 Mysql 的操作却并不一定经常触发，如果两次触发的时间超过了 mysql 允许的最久等待时间，那么程序跟 Mysql 的连接将会断开，从而报出异常。这个问题即使设定 tomcat 的 autoReconnect=true 也不能够妥善解决，最好的办法就是修改 mysql 的默认允许的最长连接时间为 1 个月。（或者你也可以再设置长一点，但是 1 个月应该足够了）

具体操作

首先要确定一下问题的产生确实是由于 Mysql 引起的，在 Mysql 命令 shell 中敲以下命令：

show global variables like 'wait_timeout'; 

不出意外，出来的值应该为“28800”秒，也就是 8 个小时。

所以接下来把它的值修改为"2678400"秒，也就是 31 天。当然，这个操作我们要去 mysql 的配置文件中执行。

首先我的本地开发环境为 Mac OS，在/etc 目录下并没有 my.cnf 文件，所以我需要先把/usr/local/mysql/support-files/my-default.cnf 拷贝一份到/etc/my.cnf。（之所以啰嗦这一句是因为...好吧...因为我比较啰嗦）

cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

vim /etc/my.cnf

添加：

wait_timeout = 2678400

保存后重启 mysql 即可。

不过重启后，再次查询 wait_timeout 发现其值仍为 28800，额，不管那么多了，直接在 mysql 的 shell 面板里面修改它的值：

set global wait_timeout=2678400;