Kee WebLogic JDBC ' No operations allowed after statement closed ' cause

Platform application developers reported a issue to our platform that they encountered a strange problem when testing their application, and the system reported undefinedexception (developer-defined exception, capturing the JPA Exception an undefined exception when found unable to handle it, they posted the exception stack information reported in the WebLogic:

<sep, 7:56:45 AM sast> <Error> <org.hibernate.transaction.JDBCTransaction> <bea-000000 > <could not toggle autocommitcom.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link  Failurethe last packet successfully received from the server was 33,177,390 milliseconds ago. The last packet sent successfully to the server was 4 milliseconds ago.at Sun.reflect.NativeConstructorAccessorImpl.newIns Tance0 (Native Method) at Sun.reflect.NativeConstructorAccessorImpl.newInstance (Nativeconstructoraccessorimpl.java : In Sun.reflect.DelegatingConstructorAccessorImpl.newInstance (delegatingconstructoraccessorimpl.java:27) at Java.lang.reflect.Constructor.newInstance (constructor.java:513) at Com.mysql.jdbc.Util.handleNewInstance ( util.java:395) truncated. See log file for complete stacktracecaused By:java.io.EOFException:Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.at Com.mysql.jdbc.MysQlio.readfully (mysqlio.java:3052) at Com.mysql.jdbc.MysqlIO.reuseAndReadPacket (mysqlio.java:3503) at Com.mysql.jdbc.MysqlIO.reuseAndReadPacket (mysqlio.java:3492) at Com.mysql.jdbc.MysqlIO.checkErrorPacket ( mysqlio.java:4043) at Com.mysql.jdbc.MysqlIO.sqlQueryDirect (mysqlio.java:2664) truncated. See log file for complete stacktrace> Sep, 7:56:45 AM org.hibernate.type.NullableType nullsafesetinfo:could No T bind value ' ... ' to parameter:1; No operations allowed after statement closed. Sep 7:56:45 AM org.hibernate.jdbc.AbstractBatcher closequerystatementwarning:exception clearing maxrows/ QueryTimeoutcom.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:No operations allowed after Statement closed.at Sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Native Method)

and they also posted the MySQL error.log.

140917  8:35:37 [Warning] aborted connection 35011 to DB: "User: ' Host: ' Z-2-pl-6 ' (Got an error reading Communicat Ion packets) 140917  8:35:38 [Warning] aborted connection 35367 to DB: "User: ' Host: ' Z-2-pl-4 ' (Got an error Readin G communication packets) 140917  8:35:39 [Warning] aborted connection 35080 to DB: "User: ' Host: ' Z-2-pl-5 ' (Got an Error reading communication packets)

inside the connection abort, in the official MySQL document there is a description of possible causes, the most common package size,connection_timeout, network failure. Since our system is the intranet, and the connection ETH is Bond mode, data statistics is not a problem, then the biggest suspicion is connection_timeout,connection_timeout in connection Pool is a very common behavior, so it has a corresponding solution, regardless of which JDBC connection pool, has provided a configuration to resolve the connection pool inside the cache connection is disconnected from the database. If there are three solutions to this problem:

1. Increase the time of wait_timeout, default 8 hours. 2. Reduce the lifetime of connection in connection pools (less than wait_timeout). 3. Test the effectiveness of connection in connection pools.

The first scenario may cause the actual pending connection resource to not be reclaimed, and the second scenario increases the number of connections in the buffer pool, so the JDBC connection pool that our platform is configured by default chooses a third option to test the validity of the connection when getting a connection from the connection pool (increased overhead, It is said to have 3%).

I asked the developer in detail the scene of the exception, the developer said in the system integration test, the first day so the test passed, the system put a night, to the next day to test, the beginning of a few use cases failed, a few times after testing all passed. It is obvious that the connection inactive time is too long, is broken by the database, the test case to store data when the connection from the connection pool is invalid, resulting in a test failure. and the system connection pool configuration has not been modified.

It may be surprising that you already have a configuration to test the connection validity in the connection pool, why is this happening? What's going on here? This shows the test configuration for the validity of the JDBC connection in our system:

Here we focus on the following 3 configurations:

We have done a test when we return to connection, and there is a 2-minute periodic unused connection test, the idle connection without testing is just a 10-second interval, should not have this kind of error, this is what is going on?

the first test to return connection is limited, in order to prevent the system problems (database connection failure, database machine), test the number of connections is limited

<p>when connectivity to the DBMS are lost, even if only momentarily, some or all of the JDBC connections in a data so Urce typically become defunct. If The data source is configured to test connections on reserve, then an application requests a database connection, Weblo GIC Server tests the connection, discovers that the connection are dead, and tries to replace it with a new connection to s Atisfy the request. Ordinarily, when the DBMS comes back online, the refresh process succeeds. However, in some cases and for some modes of failure, testing a dead connection can impose a long delay.</p><p&gt ; To minimize this delay, WebLogic data sources include logic, considers <span class= "italic" >all</span> con Nections in the data source as dead after a number of consecutive test failures, and closes all connections on the data so Urce. After all connections is closed, when an application requests a connection, the data source creates a connection without First haveTo test a dead connection. This behavior minimizes the delay for connection requests following the data source ' s connection pool flush.</p>< P>weblogic Server determines the number of test failures before closing all connections based on the test Frequency set Ting for the data source:</p><ul><li><p>if test Frequency is greater than 0, the number of test FA Ilures before closing all connections are set to 2.</p></li><li><p>if Test Frequency are set to 0 (p Eriodic testing is disabled), the number of test failures before closing all connections are set to 25% of the Maximum Capa City for the data source.</p></li></ul>

looks a bit like a continuous fetch connection failed, causing connection to fail and return an error .

However, the system also has periodic detection connection validity, in theory will not cause connection consecutive invalid, here I can not understand, later on the Internet query, see a possible explanation, if the system in a large number of connections for a long time without access, Cyclic testing may have a large part of the connection can never be tested (because the system in the test is also required to ensure that certain connection can be used, some can not be tested to).

The fact that our system has a large number of connections in the real world and the possibility of failure to test connection validity is zero, and it is only possible to encounter this problem if the system is not used for a long time in the test environment, so we do not provide solution.

Note: Online There is a solution is to set the Autoreconnect=true, but after the MySQL upgrade to 5X no use, I have not tried

Kee WebLogic JDBC ' No operations allowed after statement closed ' cause