No operations after a closed connection, bypassing my check for a closed connection?

Question

I have code that goes through a queue of objects that describe the information that should be stored in MySQL db. I recently changed the code a bit to close the connections in the finally block throughout the application so that we don't leak when an exception occurs. It works great except for:

Some users sometimes see the error No operations allowed after resultset closed - I know what the error means, but I can’t understand how this can be closed.

Violation Code:

  PreparedStatement s = null; Connection conn = null; try { if( !queue.isEmpty() ){ conn = Prism.dbc(); if(conn == null || conn.isClosed()){ return; } conn.setAutoCommit(false); s = conn.prepareStatement("INSERT query goes here"); int i = 0; while (!queue.isEmpty()){ Handler a = queue.poll(); if( a == null || a.isCanceled() ) continue; // .. value setting code here s.addBatch(); if ((i + 1) % perBatch == 0) { s.executeBatch(); // Execute every x items. } i++; } s.executeBatch(); conn.commit(); } } catch (SQLException e) { // error logging code } finally { if(s != null) try { s.close(); } catch (SQLException e) {} if(conn != null) try { conn.close(); } catch (SQLException e) {} } 

The error points to the string conn.setAutoCommit(false); . However, I don’t see how the connection can be closed at this point, because I explicitly check the closed / null connection just above it.

Answer 1

The main problem I found in the stack trace is this: 2013-03-31 13:21:11 [SEVERE] Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 30,491,788 milliseconds ago. The last packet sent successfully to the server was 30,491,788 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. 2013-03-31 13:21:11 [SEVERE] Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 30,491,788 milliseconds ago. The last packet sent successfully to the server was 30,491,788 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

The connection you get when you call Prism.dbc(); is actually one of the connection pool; and in this particular case, the combined connection is interrupted. It states that there was no successful connection for more than 8 hours between the JDBC connection in the pool and the database. Perhaps this is because of the wait_timeout mentioned, defined on the database server, or the firewall dropped the connection or something else; There can be many reasons why a connection is broken.

You must follow the recommendations made by the exception to solve this problem.

Answer 2

Most likely, the cause of this problem is incompatibility between the JDK version and the DB driver used.

Check out this link: http://dev.mysql.com/downloads/connector/j , it provides different versions of the DB driver.

EDIT:

Put this link to map jdk and drivers: http://dev.mysql.com/doc/refman/5.1/en/connector-j-versions-java.html .

EDIT:

The reason why I say that this is the most likely reason is related to the experience with the database. I do not think this is a code problem as it works for some people. Using incompatible drivers can lead to strange problems like the ones you encounter. I encountered such problems with Oracle DB, where I was able to execute all the queries except the Merge query, which led to a closed connection, and the problem arose due to an incompatible driver.

So, I say this from my own experience.

I also feel that even if this is not the exact cause of the problem [its most likely cause of the problem], if the user can publish the driver version and jdk version, we can completely eliminate this, this post can certainly help people, so I don’t see the reason for downvote.