Solution to persistent idle connection failure of Mysql database in Hibernate

A new online examination management system website http://zjsx0575.vicp.net was developed, and the following error message was found on the website the next day

HTTP status 500-

--------------------------------------------------------------------------------

Type exception report

Message

Description the server encountered an internal error () that prevented it from fulfilling this request.

Exception

Org. hibernate. Exception. jdbcconnectionexception: cocould not execute Query
Org. hibernate. Exception. sqlstateconverter. Convert (sqlstateconverter. Java: 74)
Org. hibernate. Exception. jdbcexceptionhelper. Convert (jdbcexceptionhelper. Java: 43)
Org. hibernate. loader. loader. dolist (loader. Java: 2148)
Org. hibernate. loader. loader. listignorequerycached (loader. Java: 2029)
Org. hibernate. loader. loader. List (loader. Java: 2024)
Org. hibernate. loader. hql. queryloader. List (queryloader. Java: 375)
Org. hibernate. hql. Ast. querytranslatorimpl. List (querytranslatorimpl. Java: 308)
Org. hibernate. Engine. query. hqlqueryplan. Sort MList (hqlqueryplan. Java: 153)
Org. hibernate. impl. sessionimpl. List (sessionimpl. Java: 1106)
Org. hibernate. impl. queryimpl. List (queryimpl. Java: 79)
Com. futuremap. Dao. hibernate. userinfodaohibernate. finduserinfobyusername (userinfodaohibernate. Java: 52)
Com. futuremap. Service. impl. userserviceimpl. finduserinfobyusername (userserviceimpl. Java: 36)
Com. futuremap. Service. Web. servlet. loginchecker. doget (loginchecker. Java: 73)
Com. futuremap. Service. Web. servlet. loginchecker. dopost (loginchecker. Java: 132)
Javax. servlet. http. httpservlet. Service (httpservlet. Java: 710)
Javax. servlet. http. httpservlet. Service (httpservlet. Java: 803)

Note the full stack trace of the root cause is available in the Apache Tomcat/5.5.25 logs.

I checked the MySQL help document online and found that one of MySQL sets wait_outtim to 28800, which is equivalent to 8 hours. My database layer uses hibernate. I found a problem only after querying the hibernate data, when the MySQL connection is lost, it is not detected as Hibernate that the connection has been interrupted, resulting in the above error message.

There are many ways to increase the wait_timeout time, reduce the connection life cycle in the connection pool, so that it is less than the value of wait_timeout set in the previous item, regular use of connections in the connection pool, this prevents them from being disconnected by MySQL due to idle timeout.

You can use the connection pool of c3p0 to configure c3p0.

First of all to import c3p0 package, I use the hibernate3.1 version, you can find this file in myeclipse, c3p0-0.9.0.jar. The COM/mchange/v2/c3p0/poolconfig issue occurs when you connect to the project to start Tomcat. Due to the rush, you forget to put the file into webroot/WEB-INF/lib, which leads to such a problem.

The following is the configuration file of c3p0.

<Property name = "hibernate. Connection. pool_size"> 1 </property>
<Property name = "hibernate. c3p0. max_size"> 2 </property>
<Property name = "hibernate. c3p0. min_size"> 2 </property>
<Property name = "hibernate. c3p0. Timeout"> 5000 </property>
<Property name = "hibernate. c3p0. max_statements"> 100 </property>
<Property name = "hibernate. c3p0. idle_test_period"> 3000 </property>
<Property name = "hibernate. c3p0. acquire_increment"> 2 </property>
<Property name = "hibernate. c3p0. Validate"> false </property>