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 16:00	Modified:	25 Feb 13:13
Reporter:	Christian Gruber
Status:	Closed
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:	5.0.5	OS:	Linux
Assigned to:		Target Version:
Triage:	D3 (Medium)

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.