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 disconnects (network, database disconnect). To operate the database without confirming that the connection in the pool continues to be available.

An online search, the solution of a lot of methods, basic configuration such as the following:

<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. Units Ms--><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 spare 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 not practical to start putting it on your own environment, or to make an error.

Keep working hard and finally conquer.

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 combined talent determines the proper configuration of the connection pool in the project.

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

Assuming the wait_timeout is set to very small, such as 1 minutes, then the configuration above is problematic.

Because Server1 minutes to disconnect the spare connection, the client after 5 minutes to check the connection situation, what is the point? Previously it was misleading to understand that the Timebetweenevictionrunsmillis set a relatively large value, so there has been a problem. The inclusion of the 8-hour problem also stems from this (MySQL database default is spare 8 hours disconnected).

My reason is that MySQL's wait_timeout value is set to small. The time between the client tests 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