Mysql数据库连接超时(8小时)问题

1.报错

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 56,766,426 milliseconds ago.  The last packet sent successfully to the server was 56,766,428 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)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3746)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2509)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2483)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2441)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
	at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3545)
	... 16 more
Caused by: java.net.SocketException: Broken pipe (Write failed)
	at java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
	at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
	at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
	at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3728)
	... 22 more

2.原因

MySQL服务器默认的“wait_timeout”是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时(8小时内没有进行数据库操作)，MySQL将自动断开该 连接，而连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致上面的报错.

3.解决

方式一:
JDBC连接中增加autoReconnect=true&failOverReadOnly=false

jdbc:mysql://hostname:3306/data_analy_center?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&autoReconnect=true&failOverReadOnly=false

经过测试这样解决没卵用,最好的方式是用连接池,每操作一个批次数据拿一次连接并还连接到连接池
参考资料

using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
https://blog.csdn.net/zljjava/article/details/7996091

连接数据库超时设置autoReconnect=true
https://www.cnblogs.com/jianzhixuan/p/6923216.html