Mysql中的wait_timeout错误

问题出现的场景：现在有个定时任务，执行了12个小时，在定时任务的最后，有更新Mysql的操作。在进行Mysql更新操作的时候，报错。错误如下：

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 43,589,747 milliseconds ago.  The last packet sent successfully to the server was 43,589,748 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.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_181]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_181]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_181]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_181]
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.42.jar!/:5.1.42]
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989) ~[mysql-connector-java-5.1.42.jar!/:5.1.42]
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3746) ~[mysql-connector-java-5.1.42.jar!/:5.1.42]
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2509) ~[mysql-connector-java-5.1.42.jar!/:5.1.42]
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.42.jar!/:5.1.42]
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490) ~[mysql-connector-java-5.1.42.jar!/:5.1.42]
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.42.jar!/:5.1.42]

原因是：

MySQL 的默认设置下，当一个连接的空闲时间超过8小时后，MySQL 就会断开该连接，而 c3p0/dbcp 连接池则以为该被断开的连接依然有效。在这种情况下，如果客户端代码向c3p0/dbcp 连接池请求连接的话，连接池就会把已经失效的连接返回给客户端，客户端在使用该失效连接的时候即抛出异常。

mysql5将其连接的等待时间(wait_timeout)缺省为8小时。在其客户程序中可以这样来查看其值： 
 

mysql﹥ 

mysql﹥ show global variables like 'wait_timeout'; 

+---------------+---------+ 

| Variable_name | Value | 

+---------------+---------+ 

| wait_timeout | 28800 | 

+---------------+---------+ 

1 row in set (0.00 sec) 


28800 seconds，也就是8小时。 

如果你只是个程序员，你会想着，在去对数据库做操作前，我不是先对数据库连接做个校验或判断什么的，连接是working的，我才干活，那么你得到的解决方案-或许就是这样的

#c3p0配置
<!--最大空闲时间，60秒内未使用则连接被丢弃。若为0则永不丢弃。默认值: 0 -->   
<property name="maxIdleTime">60</property>  
<!-- 当连接池连接耗尽时，客户端调用getConnection()后等待获取新连接的时间，
超时后将抛出SQLException，如设为0则无限期等待。单位毫秒。默认: 0 -->   
<property name="checkoutTimeout" value="3000"/>  
<!--c3p0将建一张名为Test的空表，并使用其自带的查询语句进行测试。
如果定义了这个参数那么属性preferredTestQuery将被忽略。
你不能在这张Test表上进行任何操作，它将只供c3p0测试使用。默认值: null -->   
 <property name="automaticTestTable">Test</property>  
<!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的   
　　时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable   
　　等方法来提升连接测试的性能。Default: false -->   
<property name="testConnectionOnCheckout">false</property>   
<!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->   
<property name="testConnectionOnCheckin">true</property> 
<!--每60秒检查所有连接池中的空闲连接。Default: 0 -->   
<property name="idleConnectionTestPeriod">60</property> 

如果你只是个DBA，你会想着，为什么数据库连接自己断了，是不是哪里有配置，我得去看看，那么你得到的解决方案-可能就是这样的

#my.cnf
wait_timeout=31536000  
interactive_timeout=31536000 

加大wait_timeout的时间。

But 现实环境中需要你考虑的是：

1. 你设置多久检查一次连接有效 依据是什么？
2. 默认加大/减小wait_timeout除了解决当前问题，会不会带来其他影响？

个人当前觉得此题 第一需考虑的是

你业务当前高峰期mysql的连接是多少？保留多久连接在高峰期都不会撑爆你数据库连接池？

如果你知道这个池-那么是改mysql ？还是改c3p0？还是双管齐下都是有据可循且不会带来后遗症的-最佳解决方案

如我当前有环境，一个现网的后台管理系统，使用人数在50以内，那么我wait_timeout 就是默认8小时，c3p0不用做连接有效性检查等，都是万事ok的。

而我还有一个EPG前台管理系统，用户量在300万以内，如果我wait_timeout为8小时，那我一到高峰期肯定就是死翘翘的，会有太多的TCP连接没关闭，数据库连接数肯定是不够的。
因EPG的一个访问中一次对数据库操作量不大，查询完数据就完成ok啦，wait_timeout 设置在120s内应该是够用啦，那么相对应的c3p0中设置小于wait_timeout 的时间来进行有效性检查 -就能确保获取到连接是有效的。

请根据业务场景，来配置参数，不要解决了A问题，带来了B问题。

解决办法：为了解决这个异常，我们在配置数据库连接池的时候需要做一些检查连接有效性的配置，这里以Druid为例，相关配置如下（更多配置）：

字段名	默认值	说明
validationQuery		用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
validationQueryTimeout		单位：秒，检测连接是否有效的超时时间。底层调用jdbc Statement对象的void setQueryTimeout(int seconds)方法
testOnBorrow	true	申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn	false	归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testWhileIdle	false	建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis	1分钟（1.0.14）	有两个含义：1) Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明

为了避免空闲时间过长超过最大空闲时间而被断开，我们设置三个配置：

validationQuery: SELECT 1
testWhileIdle: true
timeBetweenEvictionRunsMillis: 28000

转载于：https://blog.csdn.net/lzwglory/article/details/78752563

https://www.cnblogs.com/chrischennx/p/7279215.html?utm_source=itdadao&utm_medium=referral