When mybatis is connected to MySQL, an exception is reported: No operations allowed after Connection closed. Connection was implicitly closed

Exception Information

 
Org. hibernate. exception. jdbcconnectionexception: cocould not execute queryat 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 was implicitly closed due to underlying exception/error: ** begin nested exception ** COM. mySQL. JDBC. communicationsexceptionmessage: communications link failure due to underlying exception: ** begin nested exception ** java.net. socketexceptionmessage: Broken pipestacktrace: java.net. socketexception: Broken pipeat java.net. socketoutputstream. socketwrite0 (native method )...... ** end nested exception **
 

Cause Analysis

I checked the MySQL documentation, the connector/J documentation, and online instructions. The cause of this exception is:

The default "wait_timeout" of the MySQL server is 8 hours. That is to say, if a connection is idle for more than 8 hours, MySQL will automatically disconnect the connection.. This is the problem. If c3ons in c3p0 pools is idle for more than 8 hours, MySQL disconnects it, while c3p0 does not know that the connection has expired. If a client requests a connection, c3p0 provides the invalid connection to the client, which will cause the above exception.

Solution

There are three solutions:

1. Increase the wait_timeout time.
2. Reduce the life time of the connection in connection pools.
3. Test the validity of the connection in connection pools.

Of course, the best way is to use the above three methods at the same time. The following describes DBCP, c3p0, and simple JDBC datasource respectively. Assume that wait_timeout is the default 8 hours.

Add the following configuration information to DBCP:

Validationquery = "select 1"

Testwhileidle = "true"

//Some positive integer
Timebetweenevictionrunsmillis = 3600000

//Set to something smaller than 'wait _ timeout'
Minevictableidletimemillis = 18000000

//If you don't mind a hit for every getconnection (), set to "true"
Testonborrow = "true"
 

Add the following configuration information to c3p0:

 //  Test validity when obtaining connnection  
 Testconnectiononcheckin = True 

 //  Table name for Automatic Test  
 Automatictesttable = c3p0testtable

 //  Set to something much less than wait_timeout, prevents connections from going stale  
 Idleconnectiontestperiod = 18000

 //  Set to something slightly less than wait_timeout, preventing 'stamp' connections from being handed out  
 Maxidletime = 25000

 // If you can take the performance 'hit', set to "true"  
 Testconnectiononcheckout = True 

The following configuration information is added to the simple JDBC datasource:

 
Pool. pingquery = select 1

Pool. pingenabled =True

Pool. pingconnectionsolderthan = 0

//Checks idle connections once an hour
Pool. pingconnectionsnotusedfor = 3600000
 

Other solutions (not recommended)

For versions earlier than mysql5, such as mysql4.x, you only need to modify the URL in the connection pool configuration and add a parameter:Autoreconnect = true(For example, JDBC: mysql: // hostaddress: 3306/schemaname? Autoreconnect = true). If it is MySQL 5 or later, you need to modifyMy. CNF(OrMy. ini) File, in[Mysqld]Add the following:

Wait_timeout = ninteractive-Timeout = N
 

N indicates the number of seconds before the server closes the interactive connection. However, for deployment, each modificationMy. iniIt is troublesome, and what is the value of N equal? Therefore, this solution is not recommended .)