修改数据库mysql的wait_timeout

最近在测试项目的时候，特别每天第一次测试的时候，总是报数据库连接不上，连接超时的错误，连续连接才能解决问题

类似于一下错误

### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 47,795,922 milliseconds ago.  The last packet sent successfully to the server was 47,795,922 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 47,795,922 milliseconds ago.  The last packet sent successfully to the server was 47,795,922 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 47,795,922 milliseconds ago.  The last packet sent successfully to the server was 47,795,922 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.

我的理解是wait_timeout设置的是一个访问等待一个周期时长，比如这个wai_timeout默认是8个小时，如果两次访问的时间差大于这个值，就会报连接超时的错误。这个是mysql设置的一个connection的空闲时间，超过这个时间，mysql就会主动断了这个connection，而连接池并不知道该connection已经失效，如果这时有Client请求connection，DBCP将该失效的Connection提供给Client，将会造成异常。 

 

1.mysql分析：

打开MySQL的控制台，运行:show variables like ‘%timeout%’，查看和连接时间有关的MySQL系统变量，得到如下结果： 

 

wait_timeout = 28800ms = 8 hours 

 

 其中wait_timeout就是负责超时控制的变量，其时间为长度为28800s，就是8个小时，那么就是说MySQL的服务会在操作间隔8小时后断开，需要再次重连。也有用户在URL中使用jdbc.url=jdbc:mysql://localhost:3306/nd?autoReconnect=true来使得连接自动恢复，当然了，这是可以的，不过是MySQL4及其以下版本适用。MySQL5中已经无效了，必须调整系统变量来控制了。MySQL5手册中对两个变量有如下的说明： 

    interactive_timeout：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。又见wait_timeout 
    wait_timeout:服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)，又见interactive_timeout 
    如此看来，两个变量是共同控制的，那么都必须对他们进行修改了。继续深入这两个变量wait_timeout的取值范围是1-2147483(Windows)，1-31536000(linux)，interactive_time取值随wait_timeout变动，它们的默认值都是28800。 
    MySQL的系统变量由配置文件控制，当配置文件中不配置时，系统使用默认值，这个28800就是默认值。要修改就只能在配置文件里修改。Windows下在%MySQL HOME%/bin下有mysql.ini配置文件，打开后在如下位置添加两个变量，赋值。（这里修改为388000） 

 

解决方法,linux的服务器上的mysql:

set  global  interactive_timeout=31536000;
show VARIABLES like '%timeout%';
  当只改wait_timeout  是不能改interactive_timeout，但若只改interactive_timeout，wait_timeout  也会跟着改

 

set  interactive_timeout=31536000; 是修改session变量，是不起作用的，当关闭此次会话，再次打开发现还是原来的值