Re-light spring JDBC Connection pool Disconnect reconnection settings

Look at the error log first:

# # # Error querying database.  Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:No operations allowed after connection closed.### the error may be exist in file .....) # # # The error may involve ... # # # # The error occurred while executing a query### Cause:com.mysql.jdbc.exceptions.jdbc4.My Sqlnontransientconnectionexception:no operations allowed after connection closed.; SQL []; No operations allowed after connection closed.; Nested exception is Com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:No operations allowed after Connection closed.

This is when the connection pool is disconnected (network, database disconnect), without confirming that the connection in the pool continues to be available, to operate the database.

A search on the internet, a lot of solutions, the basic configuration is as follows:

<bean id= "DataSource" class= "Org.apache.commons.dbcp.BasicDataSource" destroy-method= "Close" ><property Name= "Driverclassname" value= "${jdbc.driverclassname}"/><property name= "url" value= "${jdbc.url}"/>< Property name= "username" value= "${jdbc.username}"/><property name= "password" value= "${jdbc.password}"/> <!--the minimum number of waits in the queue--><property name= "Minidle" value= "${jdbc.minidle}" ></property>< maximum waits in the!--queue-- ><property name= "Maxidle" value= "${jdbc.maxidle}" ></property><!--maximum wait time, per millisecond--><property Name= "maxwait" value= "${jdbc.maxwait}" ></property><!--maximum active number--><property name= "Maxactive" value= "${jdbc.maxactive}" ></property><property name= "InitialSize" value= "${jdbc.initialsize}" ></ Property><property name= "Validationquery" value= "Select 1"/><property name= "Testonborrow" value= "false" /><property name= "Testwhileidle" value= "true"/><property name= "Testonreturn" ValUe= "false"/><property name= "Numtestsperevictionrun" value= "${jdbc.maxactive}"/><!--5 min  detects idle connections for more than 10 minutes every 5 minutes -<property name= "Timebetweenevictionrunsmillis" value= "300000"/> <property name= " Minevictableidletimemillis "value=" 600000 "/> <property name=" removeabandoned "value=" true "/></bean>

but is that enough?

It is useless to put it in your own environment at first, or to make an error.

Keep trying, and finally it's settled.

The first is to understand the configuration of the connection pool (above)

The second is to know the wait_timeout settings in MySQL

Two-point combination to determine the proper configuration of the connection pool in the project.

If Wait_timeout is set to a large value, such as a year, the configuration above is correct in many cases.

If the wait_timeout is set to very small, such as 1 minutes, then the above configuration is problematic. Because the server 1 minutes to disconnect the idle connection, the client after 5 minutes to check the connection situation, what is the point? Previously it was not understood to be misled, the Timebetweenevictionrunsmillis set a relatively large value, so there has been a problem. This includes the 8-hour problem (the MySQL database is idle for 8 hours by default).

My reason is that MySQL's wait_timeout value is set to a small size, and the client detection interval is too large.

The correct approach is to:

Timebetweenevictionrunsmillis and in the connection pool configuration

Minevictableidletimemillis's

time is less than or equal to the time wait_timeout in the MySQL database.

Re-light spring JDBC Connection pool Disconnect reconnection settings