JDBC Connection Timeout problem

This two days encountered a problem, early to login our project site for the first time login is always not up, the second time is good, I looked at the background of the error message is this:

The last packet successfully received from the server was 57,704,088
Milliseconds ago. The last packet sent successfully to the server is
57,704,089 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.

The general meaning is that the last time the message was successfully received exceeded the database timeout value, in order to solve this problem, should increase the database timeout or enable the database automatic interconnection mechanism. So we can do this in two ways:
1, from the DB layer to solve the problem: increase the database connection timeout time
2, from the application layer to solve the problem: set up an automatic reconnection mechanism, that is, in a certain period of time with the database again 1 increase the database connection timeout

A database timeout is the maximum time a connection can be idle, such as a timeout of 30 minutes, and if a connection is idle for 30 minutes, the connection is disconnected.

I am here MySQL database, first use the following command to query the database timeout time size:

Show  GLOBAL  VARIABLES like  ' wait_timeout ';

The results appear as follows:

This is the default value of MySQL settings, where the unit is seconds, 28,800 seconds is 8 hours.

To reset the timeout period, you can use the following command:

SET GLOBAL wait_timeout=28800;

However, the database connection timeout can not be set too long, too long, resulting in too much connection sleep, occupy more system resources. So theoretically it is possible to set the timeout time more, but the strong column does not recommend this method. Take a look at the second option. 2 Enable the automatic re-connect mechanism

The automatic reconnection mechanism is to say that the application sends a simple SQL, such as "Select 1", to the database to prove to the database that I'm still alive, for example, in an hour. The most common connection database used in my project is the DBCP connection pool, which is configured as follows:

    <bean id= "datasourceoracle" class= "Org.apache.commons.dbcp.BasicDataSource" > <property name= "DRIVERCL"
        Assname "value=" ${jdbc.oracle.driverclassname} "/> <property name=" url "value=" ${jdbc.oracle.url} "/> <property name= "username" value= "${jdbc.oracle.username}"/> <property name= "password" value= . Oracle.password} "/> <!--initialization connection number--> <property name=" InitialSize "value=" 1 "/> ;! --Maximum active connection number--> <property name= "maxactive" value= "/>" <!--maximum number of idle connections-->
        Ty name= "Maxidle" value= "5"/> <!--minimum number of idle connections--> <property name= "Minidle" value= ""/> <!--detect SQL--> <property name= "validationquery" value= "Select 1"/> <property name= "tes" Tonborrow "value= false"/> <!--timed to Validateobject validation of links in the thread pool--> <property name= "Testwhileidl
E "value=" true "/>        <property name= "Testonreturn" value= "false"/> <!--get connection maximum wait time--> <property name= " Maxwait "value=" 60000/> <!--Configure how often the interval is detected to detect idle connections that need to be closed, in milliseconds--> <property "name= Evictionrunsmillis "value=" 60000/> <!--the minimum idle time before an idle connection can be activated in the free pool (milliseconds)--> <property name= "Minevi" Ctableidletimemillis "value=" 25200000 "/> </bean>

Note here that the value of Timebetweenevictionrunsmillis and Minevictableidletimemillis is less than or equal to the connection timeout for the database.

Men were born to be suffering, the pain of struggle, or the pain of regret?