MySQL Bugs: #30508: ResultSet from getGeneratedKeys() not closed when closing the parent Statement

Bug #30508	ResultSet from getGeneratedKeys() not closed when closing the parent Statement
Submitted:	20 Aug 2007 14:00	Modified:	25 Feb 2008 12:13
Reporter:	Christian Gruber	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.5	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hi!

According to the JavaDoc of ResultSet.close() (see http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSet.html#close() ), a ResultSet has to be closed when its creating Statement is closed. I don't know whether this also is valid for ResultSets that are created by a Statement.getGeneratedKeys() method call, but I suspect that it should be. With the MySQL Connector/J version 5.0.5, the ResultSet stays open when the creating Statement is closed.

Greetings,
Christian

How to repeat:
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCTest
{
    // create table tbl (id integer primary key auto_increment, val varchar(10)) engine=innodb;
    public static void main(String[] args) throws Exception
    {
        String url = "jdbc:mysql://hostname:3306/test";

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

        Connection conn = DriverManager.getConnection(url, "user", "password");

        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement("insert into tbl values (null,?)");
        ps.setString(1, "test");
        ps.executeUpdate();
        ResultSet keys = ps.getGeneratedKeys();
        while (keys.next()) {
            int id = keys.getInt(1);
            System.out.println(id);
        }
        //keys.close(); // The ResultSet is not closed explicitly
        ps.close(); // But should be closed implicitly by this method call
        Field isClosed = keys.getClass().getDeclaredField("isClosed");
        isClosed.setAccessible(true);
        System.out.println("rs closed: " + isClosed.getBoolean(keys)); // prints "false"; expected: "true"
        conn.rollback();
        conn.close();
    }
}

Hi Christian and thanks for your report. Sorry for the delay in processing.

Can you please tell me what is the value of holdResultsOpenOverStatementClose (Should the driver close result sets on Statement.close() as required
by the JDBC specification?) in your connection string?

Hi Tonci!

I did not use any MySQL specific parameters to the connection URL. So, the value should be false (default, according to the documentation).

When I make a query with rs = ps.executeQuery() instead of ps.executeUpdate() and ps.getGeneratedKeys() instead with almost the same code, then the ResultSet is closed properly when the ps is closed, so the value is really false.

There are 2 ways to create a ResultSet from a Statement (executeQuery() and getGeneratedKeys()), and only the former ResultSet is closed properly when the Statement is closed.

By the way, the way I found this bug was because Hibernate does not close the ResultSet which gets the generated ID, which produces a memory leak: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1312
This issue seems to be fixed in the next Hibernate version, but there are probably other programs around that fall into this pit.

Greetings,
Christian

Hi Tonci!

I did not use any MySQL specific parameters to the connection URL. So, the value should be false (default, according to the documentation).

When I make a query with rs = ps.executeQuery() instead of ps.executeUpdate() and ps.getGeneratedKeys() instead with almost the same code, then the ResultSet is closed properly when the ps is closed, so the value is really false.

There are 2 ways to create a ResultSet from a Statement (executeQuery() and getGeneratedKeys()), and only the former ResultSet is closed properly when the Statement is closed.

By the way, the way I found this bug was because Hibernate does not close the ResultSet which gets the generated ID, which produces a memory leak: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1312
This issue seems to be fixed in the next Hibernate version, but there are

Christian, as you have located the problem in Hibernate, can we close this report now?

I don't think that this is a problem of Hibernate. Hibernate just relied on the correct implementation of the JDBC standard. And that clearly (IMHO) states that all the ResultSets of a Statement are closed when the Statement is closed. The Hibernate patch just ignores that and closes the ResultSet explicitly.

So, the combination Hibernate/MySQL JDBC driver might have one bug less. But my argument is that there are probably other programs or libraries around that rely on the documented behavior of ResultSets and Statements and will at some time run out of resources. My personal problem is solved by the Hibernate patch, but I still suspect there are other people around that would perhaps fall into this pit.

So, when you ask me personally whether my problem is solved, then I say yes. But I can't speak for other people here. I think that the MySQL JDBC driver violates the JDBC specification here, but feel free to close the bug report.

Christian

Christian, thanks for clarification. I don't get paid by closed report so I'll try figuring out why statement isn't closed although it should be according to specs.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39336

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39337

A note has been added to the 5.1.6 changelog: 

ResultSet returned by Statement.getGeneratedKeys() is not closed automatically when statement that created it is closed.