About MySQL wait_timeout connection Timeout problem error resolution

My project is using hibernate to perform database operations.

The MySQL connection timeout problem, estimated that many people have encountered: the general situation is the case, the development of the test program is normal, one to the next day out of the first inexplicable error, such as in my project is scheduled task execution, one o'clock in the morning every day, that is, 24 hours every 24 hours to execute, Far beyond 8 hours.

If you happen to see the log record in the first time that the database times out, then the first time-out error is this:
ERROR [org.hibernate.util.JDBCExceptionReporter]-Communications link failure

Last packet sent to the server was 0 ms ago.

If it is not the first time after the execution, each subsequent error will become nested errors, this is the following:

ERROR [Org.hibernate.util.JDBCExceptionReporter]-
No operations allowed after connection closed. Connection is implicitly closed due to underlying exception/error:

* * BEGIN NESTED EXCEPTION * *

Com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

Message:the Last packet successfully received from the server was86395 milliseconds ago. The last packet sent successfully to the server were 86395 milliseconds ago, which  is longer than the server Configur Ed value of ' Wait_timeout '. Should consider either expiring and/or testing connection validity before use in your application, increasing the Serv Er configured values for client timeouts, or using the Connector/j Connection property ' Autoreconnect=true ' to avoid this Problem.
STACKTRACE:

Com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:The last packet successfully received from the server was86395 Milliseconds ago. The last packet sent successfully to the server were 86395 milliseconds ago, which is longer than the server configured VA Lue of ' wait_timeout '. Should consider either expiring and/or testing connection validity before use in your application, increasing the Serv Er 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 (Unknown Source)
At Sun.reflect.DelegatingConstructorAccessorImpl.newInstance (Unknown Source)
At Java.lang.reflect.Constructor.newInstance (Unknown Source)
At Com.mysql.jdbc.Util.handleNewInstance (util.java:406)
At Com.mysql.jdbc.SQLError.createCommunicationsException (sqlerror.java:1074)
At Com.mysql.jdbc.MysqlIO.send (mysqlio.java:3270)
At Com.mysql.jdbc.MysqlIO.sendCommand (mysqlio.java:1932)
At Com.mysql.jdbc.MysqlIO.sqlQueryDirect (mysqlio.java:2101)
At Com.mysql.jdbc.ConnectionImpl.execSQL (connectionimpl.java:2554)
At Com.mysql.jdbc.PreparedStatement.executeInternal (preparedstatement.java:1761)
At Com.mysql.jdbc.PreparedStatement.executeQuery (preparedstatement.java:1912)
At Org.hibernate.jdbc.AbstractBatcher.getResultSet (abstractbatcher.java:208)
At Org.hibernate.loader.Loader.getResultSet (loader.java:1812)
At Org.hibernate.loader.Loader.doQuery (loader.java:697)
At Org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections (loader.java:259)
At Org.hibernate.loader.Loader.doList (loader.java:2232)
At Org.hibernate.loader.Loader.listIgnoreQueryCache (loader.java:2129)
At Org.hibernate.loader.Loader.list (loader.java:2124)
At Org.hibernate.loader.hql.QueryLoader.list (queryloader.java:401)
At Org.hibernate.hql.ast.QueryTranslatorImpl.list (querytranslatorimpl.java:363)
At Org.hibernate.engine.query.HQLQueryPlan.performList (hqlqueryplan.java:196)
At Org.hibernate.impl.SessionImpl.list (sessionimpl.java:1149)
At Org.hibernate.impl.QueryImpl.list (queryimpl.java:102)
At Org.hibernate.impl.AbstractQueryImpl.uniqueResult (abstractqueryimpl.java:835)
At Com.cn.util.db.TargetRecordDaoImpl.findbyIdAndDate (targetrecorddaoimpl.java:23)
At Com.cn.util.parser.ExcelOperate.readExcel (exceloperate.java:324)
At Com.cn.util.parser.ExcelParser.parser (excelparser.java:41)
At Com.cn.util.timer.CRMExcelParserTarger.execute (crmexcelparsertarger.java:76)
At Org.quartz.core.JobRunShell.run (jobrunshell.java:199)
At Org.quartz.simpl.simplethreadpool$workerthread.run (simplethreadpool.java:549)
caused By:java.net.SocketException:Software caused connection Abort:socket write error
At Java.net.SocketOutputStream.socketWrite0 (Native Method)
At Java.net.SocketOutputStream.socketWrite (Unknown Source)
At Java.net.SocketOutputStream.write (Unknown Source)
At Java.io.BufferedOutputStream.flushBuffer (Unknown Source)
At Java.io.BufferedOutputStream.flush (Unknown Source)
At Com.mysql.jdbc.MysqlIO.send (mysqlio.java:3251)
... More

* * END NESTED EXCEPTION * *

The specific explanation is this: the MySQL server default "Wait_timeout" is 8 hours "is the default value is 28,800 seconds", that is, a connection idle for more than 8 hours, MySQL will automatically disconnect the connection, In layman's words, a connection is automatically disconnected if there is no activity within 8 hours.
The value of wait timeout can be set, but can be at most 2147483, no longer large. That's about 24.85 days.
So even if you mysql through My.ini in
# The TCP/IP Port the MySQL Server would listen on
port=3306 Add below
# This was Myown dinifition for MySQL connection timeout
wait_timeout=31536000
interactive_timeout=31536000
Regardless of the maximum number of values, can only be resolved by MySQL to 2147483,2147483 days your program What is wrong or wrong, can not be avoided
It was later found that Hibernate's built-in connection pool performance was very poor, rather than directly with the third-party c3p0, instead of C3P0 connection pool, which

A connection pool automatically handles the case where the database connection is closed. To use C3P0 it's easy to get C3p0-0.9.1.jar from hibernate first.

To the project's Lib directory, and then remove the annotations for those attributes that begin with HIBERNATE.C3P0 in the hibernate.properties (use missing

Save value or the value you need, so hibernate automatically uses C3P0 instead of the built-in connection pool. So far, the problem in front

No longer appeared.
Specifically configured in the Hibernate.cfg.xml as follows

<property name= "Hibernate.connection.provider_class" >org.hibernate.connection.c3p0connectionprovider</ Property>

< minimum number of connections!--connection pool--
<property name= "Hibernate.c3p0.min_size" >5</property>
<!--maximum number of connections--
<property name= "Hibernate.c3p0.max_size" >30</property>
<!--connection Timeout time-
<property name= "Hibernate.c3p0.timeout" >1800</property>
<!--statemnets Cache size-
<property name= "Hibernate.c3p0.max_statements" >100</property>
<!--How many seconds to detect if the connection is working properly-
<property name= "Hibernate.c3p0.idle_test_period" >121</property>
<!--when the connection in the pool is exhausted, the number of connections increased at one time, default is 3-->
<property name= "Hibernate.c3p0.acquire_increment" >1</property>
<property name= "Hibernate.c3p0.validate" >true</property>

About MySQL wait_timeout connection Timeout problem error resolution