请教下mysql 8小时问题。不甚感激！！

Question

mysql 8小时问题相信很多朋友都遇到过，不知道您是怎么处理的呢？

我是打算改my.cnf文件改wait_timeout值。。

不过看到很多人处理这个问题都不推荐这种搞法。。

我是用的spring+dbcp的连接方式，这样的配置里面不支持autoReconnect这个属性的设置

受教了。。。

-:org.springframework.dao.RecoverableDataAccessException: PreparedStatementCallback; SQL [select userPassword from sysuser where userName=? AND userStatus='1']; The last packet successfully received from the server was227267 milliseconds ago.The last packet sent successfully to the server was 227267 milliseconds ago, which  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 was227267 milliseconds ago.The last packet sent successfully to the server was 227267 milliseconds ago, which  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.

Answer 1

碰到类似问题，修改的mysql配置

[mysqld]

wait_timeout=86400
interactive_timeout=86400

Answer 2

愿闻其详

Answer 3

回复
你可以去查查timeout和waittimeout这两个参数，官网上有详细的解释。我会在程序中写一个随机函数，a = random(1,10)，如果a<5,那么我会去激活一次连接。这样的话你的数据库链接会保持很长时间

Answer 4

我记得有个激活事件，conn.ping，你可以不定时（<8小时）去执行以下conn.ping,去重新激活以下链接，而再也不用担心八小时问题了。

Answer 5

我没用spring,直接写的properties的配置文件用dbcp去加载 spring我觉得一样吧，把对应属性写到spring配置里面

Answer 6

回复
嗯。。是了。非常感谢

Answer 7

回复
甭客气，哈，我也是上次因为这个问题，困扰了好久，这个方法应该没问题的

Answer 8

我上次也遇到的,配置里面加个sql心跳检查

#sql 心跳检查
testWhileIdle=true
testOnBorrow=true
testOnReturn=true
validationQuery=SELECT 1
validationQueryTimeout=1
timeBetweenEvictionRunsMillis=30000
numTestsPerEvictionRun=50

Answer 9

你好，请问这个timeout时间配在哪呢？呵呵

Answer 10

回复
在/etc/mysql/my.cnf中有

Answer 11

连接池也配置一个 timeout 时间，值小于 wait_timeout

Answer 12

恩，查了下，就用这个方法试了。嘿嘿谢谢

Answer 13

配了这个心跳，可是问题还是存在，不知道咋回事了

Answer 14

我上次也遇到的,配置里面加个sql心跳检查

#sql 心跳检查
testWhileIdle=true
testOnBorrow=true
testOnReturn=true
validationQuery=SELECT 1
validationQueryTimeout=1
timeBetweenEvictionRunsMillis=30000
numTestsPerEvictionRun=50