Invalid connection using Apache common DBCP + common pool + MySql

Invalid connection using Apache common DBCP + common pool + MySql

Throwable occurred: Org. springframework. transaction. cannotcreatetransactionexception: cocould not open JDBC connection for transaction; Nested exception is com. mySQL. JDBC. exceptions. jdbc4.communicationsexception: The last packet successfully encoded ed from the server was 50,123,505 milliseconds ago. the last packet sent successfully to the server was 50,123,505 milliseconds ago. is longer than the server configured value of 'wait _ timeout '. you shoshould 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.

This is mainly caused by two reasons:
1. MySQL automatically closes connections that are not used for a long time. If a connection reaches the specified time (8 hours by default) of the MySQL parameter wait_timeout, the connection is automatically closed.
2. The Connection check parameters are not specified in the common pool.

Solution: solve the problem from the configuration parameters of the common pool:

<Bean id = "datasource" class = "org. Apache. commons. DBCP. basicdatasource" Destroy-method = "close">
<Property name = "driverclassname">
<Value >$ {dB. Driver} </value>
</Property>
<Property name = "url">
<Value >$ {dB. url} </value>
</Property>
<Property name = "username">
<Value >$ {dB. User} </value>
</Property>
<Property name = "password">
<Value >$ {dB. Password} </value>
</Property>
<Property name = "maxactive">
<Value> 100 </value>
</Property>
<Property name = "maxidle">
<Value> 50 </value>
</Property>
<Property name = "maxwait">
<Value> 10000 </value>
</Property>

<Property name = "timebetweenevictionrunsmillis">
<Value> 3600000 </value> <! -- 1 hours -->
</Property>
<! --
<Property name = "minevictableidletimemillis">
<Value> 20000 </value>
</Property>
-->

<Property name = "testwhileidle">
<Value> true </value>
</Property>
<Property name = "validationquery">
<Value> select 1 from dual </value>
</Property>
</Bean>
The preceding three red parameters can be used to avoid this problem:

Timebetweenevictionrunsmillis: Start the connection verification timer. The timer run time interval is the value of timebetweenevictionrunsmillis. The default value is-1, indicating that the timer is not started. It is set to 1 hour here, as long as it is smaller than

Testwhileidle: True, indicates checking the idle connection. False indicates not checking.

Validationquery: the SQL statement used to check the connection.

This is only one method. There are several other methods:

Timebetweenevictionrunsmillis + minevictableidletimemillis: This method does not check the validity of the connection, but checks the idle time of the connection. If it is greater than minevictableidletimemillis, it is cleared.

<Property name = "timebetweenevictionrunsmillis">
<Value> 3600000 </value> <! -- 1 hours -->
</Property>

<Property name = "minevictableidletimemillis">
<Value> 120000 </value> <! -- If the idle time of connection is greater than this value, it will be closed and deleted from the connection pool. -->
</Property>

If you do not like to use a timer, you can also configure the testonborrow + validationquery parameter: each time you take a parameter from the connection pool, the connection validity will be verified. In fact, the performance of this method will be worse than the timer.
<Property name = "testonborrow">
<Value> true </value>
</Property>
<Property name = "validationquery">
<Value> select 1 from dual </value>
</Property>

In addition, you can also use testonreturn + validationquery, but it may not solve the problem: this indicates that the connection is valid when the connection pool is returned after the connection is used up, this may result in an invalid connection.

The preceding methods can be used in combination, but it may not be a good thing to check more points.