MySQL Bugs: #620: Operation not allowed after ResultSet closed Exception

Bug #620	Operation not allowed after ResultSet closed Exception
Submitted:	8 Jun 2003 1:03	Modified:	24 Aug 2010 15:25
Reporter:	Jim Birkenmaier	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.x	OS:	Linux (RedHat 9.0)
Assigned to:		CPU Architecture:	Any

Description:
Hello,

I am getting an Exception thrown from my Java program. I do the following:

1. Select from database -> one record returned (via ResultSet rs) and processed.
2. Perform the same Select -> no records returned (which is ok).
   While processing, call rs.next() and the connector code thinks the
   rs object is closed (it isn't).

I have tried the following connector jars:
    mysql-connector-java-3.0.8-stable-bin.jar
    mysql-connector-java-3.1.0-alpha-bin.jar

Both fail in the same manner. I have reverted back to the following:
    mysql-connector-java-2.0.14-bin.jar
My program works with this jar file so I do have a workaround but I am sure
you want to look into this problem.

Here is the stack trace:

java.sql.SQLException: Operation not allowed after ResultSet closed
        at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:3562)
        at com.mysql.jdbc.ResultSet.next(ResultSet.java:2406)
        at stockdata.stockdata.SymbolNameTable.getNewEntry(SymbolNameTable.java:192)
        at stockdata.taskmanager.HistoricalDataWorkOrder.getObject(HistoricalDataWorkOrder.java:210)
        at stockdata.taskmanager.TaskManagerImpl.getWorkOrder(TaskManagerImpl.java:184)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:324)
        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
        at sun.rmi.transport.Transport$1.run(Transport.java:148)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
        at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
        at java.lang.Thread.run(Thread.java:536)

How to repeat:
Here is an excerpt from my program. The Exception is thrown from the rs.next()
method call:

    public String getNewEntry ()
        throws SQLException
    {
        String   symbolName = null;
        String   dataSeparator = "|";

        synchronized (newEntryList)
        {
            if (newEntryList.hasMoreElements ())
            {
                symbolName = newEntryList.nextToken ();
            }
            else
            {
                StringBuffer sb;
                sb = new StringBuffer ();

                // This is the first time through here. Perform a select on the
                // database and build a list of symbol names which have
                // the "NewEntry" field set.

                ResultSet rs = executeQuery ("SELECT Symbol FROM " +
                                             tableName + " WHERE NewEntry='1'");
                while (rs.next ())
                {
                    sb.append (rs.getString (1));
                    sb.append (dataSeparator);
                }
                rs.close ();

                newEntryList = new StringTokenizer (sb.toString (),
                                                    dataSeparator);
                if (newEntryList.hasMoreElements ())
                {
                    symbolName = newEntryList.nextToken ();
                }
            }
        }

        return symbolName;
    }

Have you closed the Statement that created the ResultSet _or_ executed another query that creates a ResultSet using the same Statement? The JDBC spec requires that when either of these happen, the currently open ResultSet must be implicitly closed by the driver. Connector/J and MM.MySQL-2.0.14 did not implement this correctly, Connector/J 3.0.x and beyond do. 

If this is _not_ the case, please re-open this bug with a testcase that show the _exact_ sequence of events you use that cause this exception to be thrown.

I should clarify my previous comment. I need to see what executeQuery() does, and see what method has the responsibility for closing the Statement instance that the query was performed on.

You were a little hasty there, weren't you? Closing this bug report before exploring it thoroughly and getting all the facts? I have a base class that handles all DB functions for a single thread. I reuse the Statement object for these operations. However, during the time that I execute the SELECT to the time I call the ResultSet's next() call, no one is doing anything else. The Statement object isn't (re)used by anyone else and it isn't closed either because it is declared on the class level. Buy hey, maybe if you ignore this, it will just go away.

Sorry if I offended you, that was not the intent. I am still asking for a _repeatable_ testcase, because the code snippet you've sent is not enough to reproduce the behavior.

I have seen this exact same behavior reported 10's of times, and it's always been a misunderstanding of how the JDBC API works...So yes, maybe I was a bit hasty in marking it as bogus, however your explanation of what's happening is very suspiciously like all other times this has happened, and it's always happened when a Statement lifecycle is managed _outside_ where result sets are processed, which because of the JDBC spec requirements for explictly closing ResultSets, can lead to behavior that most developers aren't expecting.

Simple code inspection will show why this is most likely what is happening:

1. checkClosed() (where the exception you report is happening) only checks ResultSet's member variable 'isClosed' and throws an exception if isClosed() is true.
2. 'isClosed' is accessed in only _two_ places in ResultSet, in checkClosed(), and in close(). It is set to 'false' by default during member initialization.
3. If you're using standard Statements, ResultSet.close() is only accessed either directly by your code, or by Statement.execute(), executeQuery() or executeUpdate() or Statement.close(). Statement's calling of ResultSet.close() is to implement the JDBC requirement of closing open result sets when executing new queries.
4. Statement.close can either be called directly by your code, or implicitly by Connection if the Connection is closed (either directly by your code, or by a communications-level error in the Driver).

If you can issue other queries after this exception is thrown on the same Connection, then you can rule out the Connection being implicitly closed, which would thus close the Statement.

If you can create a standalone testcase that can reproduce this behavior, then the bug can be investigated. Unfortunately, without that, there's not a lot that can be done, as I've already mocked this up as a testcase based on the information you give, and it works as expected.

No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.

hey, ..i got this error , Update your connection Pool... i was using C3po connection pool and just updated it to the latest and it worked..

i did not have to update the mysql driver..

I have the same error using this connector mysql-connector-java-5.1.13-bin.jar

java.sql.SQLException: Operation not allowed after ResultSet closed
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
        at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:794)
        at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7145)

I am using a sigle stament because this is in a transaction

please helpme

Jeryl, Diego,

thank you for the feedback, but we still need complete test case demonstrating the problem.

table when I have the error

Attachment: create_table (application/octet-stream, text), 1.34 KiB.

Thank you for the feedback.

In the file attached there are methods for connecting and using BD, but no main() method and I don't see sequence of calls you use before getting exception. Please send us test case demonstrating this too.

I have the error only excecuting the function on the table that I attach. on other tables excecutes successfuly.

furthermore, when I excecute the query using consultarConNuevoStmt(), I don't get error

I hope this can help you

Thank you for the feedback.

In the loop you are trying to execute query, then go through older result set of same statement which is closed when you executed UPDATE query. Of course if you create separate statement object for SELECT query error does not occur. So this is not MySQL bug.

Yes, I got this problem when trying to migrate from mysql-connector-3.0.9 to mysql-connector-5.1.12 on Windows XP/2003. 

Here is the test case on Windows:-

import java.sql.*;

public  class TestEndResultSet
{
  public static void main(String[] args) throws 
      SQLException, ClassNotFoundException
  {
    Connection conn=null;
    String database="test";
    String user="user"; 
    String password="password";

    Class.forName("com.mysql.jdbc.Driver");

    String URL="jdbc:mysql://localhost:3306/"+database
    +"?zeroDateTimeBehaviour=convertToNull&noDatetimeStringSync=true"
    +"&jdbcCompliantTruncation=false"
    ;
    conn = DriverManager.getConnection(URL,user,password);

    Statement stmt=null;
    ResultSet rs=null;

    //drop table foo
    String sqlStmt="drop table foo"
      ;
    try {
      stmt = conn.createStatement();
      stmt.executeUpdate(sqlStmt);  
    }catch (SQLException e) {
    }
    //create table
    sqlStmt="create table foo ("
      +" pkey int not null "
      +",value int not null "
      +",PRIMARY KEY  (pkey) "
      +") ENGINE=InnoDB DEFAULT CHARSET=latin1 "
      ;
    try {
      stmt = conn.createStatement();
      stmt.executeUpdate(sqlStmt);  
    }catch (SQLException e) {
    }
    //insert 3 rows of data
    try {
      stmt = conn.createStatement();
      stmt.executeUpdate("insert into foo values (1,0)");  
      stmt.executeUpdate("insert into foo values (2,1)");  
      stmt.executeUpdate("insert into foo values (3,0)");  
    }catch (SQLException e) {
    }
    //now retrive all three rows then update value equals 1 to 2;
    //this works for mysql-connector-3.0.9 but not 5.1.12
    sqlStmt="select * from foo";
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sqlStmt);
      int count=0;
      while (rs.next()) {  // <-- It fails here for 5.1.12 
        String value=rs.getString("value");
        if (value.equals("1")) {
          sqlStmt="update foo set value=2 " 
          +"where value=1 " 
          ;
          stmt.executeUpdate(sqlStmt);
          System.out.println("****:"+(count++));
        }
      }
    }catch (SQLException e) {
      e.printStackTrace(System.out);
    }
  }
}

****:0
java.sql.SQLException: Operation not allowed after ResultSet closed
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
        at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:795)
        at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7146)
        at TestEndResultSet.main(TestEndResultSet.java:59)

Connector 5.1.12 will close the resultSet when stmt.executeUpdate(sqlStmt) is executed within the while loop when using the same stmt as the query statement. Can I have backward compatibility with older connectors so that we do not have to change hundreds of our programs that use this coding?

Operation not allowed after ResultSet closed Exception

Attachment: TestEndResultSet.java (text/plain), 2.07 KiB.

What I'd do is to put stmt.Close() after executing each query/update, especially

    //insert 3 rows of data
    try {
      stmt = conn.createStatement();
      stmt.executeUpdate("insert into foo values (1,0)");  
      stmt.executeUpdate("insert into foo values (2,1)");  
      stmt.executeUpdate("insert into foo values (3,0)");  
    }catch (SQLException e) {
    }
    //now retrive all three rows then update value equals 1 to 2;
    //this works for mysql-connector-3.0.9 but not 5.1.12

    stmt.Close(); <<<<< HERE

    sqlStmt="select * from foo";
    try {
      stmt = conn.createStatement();

Hi Tonci,

Adding stmt.close() where you said will not work for connector-5.1.12 but work for connector-3.0.9

Actually, I'm not asking for the correct solution to the coding I have published. It is a test case to show that it works on connector-3.0.9 but not on connector-5.1.12 since Mark Matthews wanted a test case. I can change one or two objects containing this kind of coding but we have developed a very huge software over the years and this kind of coding might exists that we might overlooked. This will make the software unstable when we upgrade to newer connector-5.1.x to take advantage of MySQL 5.1.x.

Currently we use the right kind of coding for new modules which do not give raise to "Operation not allowed after ResultSet closed Exception".

I'm begging Mark Matthews to provide a backward compatibility of newer connector-5.1.12 to behaviour like older connector-3.0.x.

Hh Too, I can not guess what Mark will do but I can tell you we will, most probably, not fix anything in such old c/J version.

Now, did you tried setting holdResultsOpenOverStatementClose=true (http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html)? Afaiu, this is what you've been looking for, right?

And, I always test against latest code, so I do not understand your remark:
> Adding stmt.close() where you said will not work for connector-5.1.12 but work for connector-3.0.9

Hi Tonci,

Yes, setting holdResultsOpenOverStatementClose=true now works for both 5.1.x and 3.0.x.

The above setting is what I missed. Of course I am not asking you to fix old C/J. With this setting it is already a fix for new c/J with backward compatibility to older c/J. 

Thank you.

In connectors we generally do not break old behavior without connect string options thus I thought maybe you want us to fix something in old release.

Main thing is that it works now!

i think this will help you...
thanks to ROSEINDIA
http://www.roseindia.net/answers/viewqa/JSP-Servlet/9939-resultset-problem.html