Hibernate uses c3p0 to connect to the MySQL database can't open session

Recently, the project used the data layer combination of hibernate3.0 C3PO MySQL, and the development and deployment were smooth. However, every morning when accessing the application, the cocould not open hibernate session for transaction exception was thrown, caused by: COM. mySQL. JDBC. communicationsexception: communications link failure due to underlying exception, but after several accesses, it returns to normal. The datasource configuration is as follows:

<Bean id = "datasource" class = "com. mchange. v2.c3p0. combopooleddatasource" Destroy-method = "close">
<Property name = "driverclass">
<Value> com. MySQL. JDBC. Driver </value>
</Property>
<Property name = "jdbcurl">
<Value> <! [CDATA [JDBC: mysql: /// localhost: 3306/wap2? Useunicode = true & amp; characterencoding = Latin1]> </value>
</Property>
<Property name = "user">
<Value> root </value>
</Property>
<Property name = "password">
<Value> zznode </value>
</Property>
<Property name = "minpoolsize">
<Value> 3 </value>
</Property>
<Property name = "maxpoolsize">
<Value> 20 </value>
</Property>
<Property name = "maxstatements">
<Value> 50 </value>
</Property>
<Property name = "maxidletime">
<Value> 6000000 </value>
</Property>
</Bean>

According to the problem phenomenon, we initially thought it was a problem with the database connection. We use exception as the keyword Google (this is a common method when I encountered a problem that cannot be solved). We got the relevant information: 1. The default timeout duration for MySQL database connections is 28800 seconds. 2. Meanings of the parameters c3p0;

Through the above information collection, we can see the problem (MySQL closes the idle link after 28800 seconds, that is, 8 hours, while c3p0 disconnects the link after 6000000 seconds, the connection maintained by C3PO may have been shut down by MySQL. Naturally, Hibernate cannot open the session and errors occur early the next day.) to verify my ideas, I decided to reproduce this error on the development machine. First, in the MySQL configuration file my. INI added wait_timeout = 30 so that MySQL would turn off the link after 30, restart the application, the first access was successful, and the same error occurred after one minute of access, indicating that the problem is correctly diagnosed, modify the configuration as follows:

<Property name = "maxidletime">
<Value> 28000 </value>
</Property>
<Property name = "idleconnectiontestperiod">
<Value> 28000 </value>
</Property>

Configuration explanation: 28000 <28800 enable c3p0 to close its own link before MySQL is disconnected. Configure the idleconnectiontestperiod parameter to enable C3PO to check whether existing connections are available at intervals of 28000, in this way, we should ensure that all the connections we get are available. If we are not at ease, we can add the testconnectioncheckout parameter to check whether the connection can be used whenever we pull out the connection. This may cause MySQL to have a certain performance sacrifice;
<Property name = "testconnectiononcheckout">
<Value> true </value>
</Property>

After detecting the above changes, the same problem does not occur for a period of time. The problem is solved! Summarize the three steps to solve the problem: collect relevant information, reproduce the problem, and solve the test.