MyBatis abnormality: No operations allowed after connection closed

MyBatis Connection MySQL Report exception: No operations allowed after connection closed. Connection was I
Connection Pool Error
MyBatis Connection MySQL Report exception: No operations allowed after connection closed. Connection was implicitly closed

Exception information

Org.hibernate.exception.JDBCConnectionException:could not execute query at
Org.hibernate.exception.SQLStateConverter.convert (sqlstateconverter.java:74) at
Org.hibernate.exception.JDBCExceptionHelper.convert (jdbcexceptionhelper.java:43)
..... caused By:com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:No operations allowed after connection Closed. Connection is implicitly closed due to underlying exception/error:
* * BEGIN NESTED Exception * * COM.MYSQL.JDBC.C
Ommunicationsexception
message:communications link failure due to underlying exception:
* * BEGIN NESTED EXCEPTION * *
java.net.SocketException
message:broken pipe
stacktrace:
java.net.SocketException : Broken pipe at
java.net.SocketOutputStream.socketWrite0 (Native method)
...

Cause analysis

Looking at the MySQL documentation, as well as the connector/j documentation and the online instructions, the reason for this anomaly is that:

MySQL server default "Wait_timeout" is 8 hours, that is to say a connection idle more than 8 hours, MySQL will automatically disconnect the connection. This is the problem, in C3P0 pools connections if idle more than 8 hours, MySQL disconnected it, and c3p0 did not know that the connection has been invalidated, if there is a client request connection, C3P0 the failure of the connection to the client, will cause the above exception.
Solution

There are 3 ways to solve this problem:

Increase the wait_timeout time.
reduce the lifetime of Connection in Connection pools.
test the effectiveness of Connection in Connection pools.

Of course, the best way is to combine the above 3 methods at the same time, the following DBCP, c3p0 and simple jdbc DataSource, respectively, assuming that Wait_timeout is the default 8 hours

DBCP Add the following configuration information:

Validationquery = "Select 1"
testwhileidle = "true"

//some positive integer
timebetweenevictionrunsmillis = 3600000

//set to something smaller than ' wait_timeout ' minevictableidletimemillis
= 18000000

//if Don ' t mind a hit for every getconnection (), set to "true"
Testonborrow = "true"

C3P0 Add the following configuration information:

The test is valid
Testconnectiononcheckin = True

//The table name of the automatic test when getting connnection
automatictesttable= C3p0testtable

//set to something more less than wait_timeout, prevents connections from going stale
Ntestperiod = 18000

//set to something slightly less than wait_timeout, preventing ' stale ' connections from being hand ed out
maxidletime = 25000

//if/can take the performance ' hit ', set to "true"
Testconnectiononcheckout = t Rue

Simple JDBC DataSource Adds the following configuration information:

Pool.pingquery = Select 1

pool.pingenabled = true

Pool.pingconnectionsolderthan = 0

//one-hour check for idle connections C13/>pool.pingconnectionsnotusedfor = 3600000

Other scenarios (not recommended)

For MySQL5 versions, such as mysql4.x, you only need to modify the URL in the connection pool configuration to add a parameter: Autoreconnect=true (such as jdbc:mysql://hostaddress:3306/schemaname?) Autoreconnect=true), in the case of MySQL5 and later versions, you will need to modify MY.CNF (or My.ini) files and add them after [mysqld]:
Wait_timeout = n
Interactive-timeout = n

Where n is the number of seconds to wait for the activity before the server closes the interactive connection. But in terms of deployment, each modification my.ini more trouble, and n equals how much is the appropriate value. Therefore, this solution is not recommended. )