MySQL Bugs: #65503: ResultSets created by PreparedStatement.getGeneratedKeys() are not close()d.

Bug #65503	ResultSets created by PreparedStatement.getGeneratedKeys() are not close()d.
Submitted:	3 Jun 2012 19:17	Modified:	5 Sep 2012 18:03
Reporter:	Stefan M	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.20	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	ResultSet leak

Description:
While I was working on a java application which was to execute a few million queries generated by a handful of prepared statements, after running for a while,
the application crashed reporting failure to allocate heap memory.

Upon looking at the heap dump, I noticed I had two PreparedStatement objects, each taking 400MB of memory. Although I had PreparedStatements corresponding to both SELECT-s and INSERTS, the thought that the leaks were caused by SELECT ResultSets not being closed was wrong.

The documentation of Statement.close() mentions that the resultsets are closed automatically on the next execute and this is what was happening. The leaks were instead caused by using Statement.RETURN_GENERATED_KEYS and getGeneratedKeys() on my inserts and not .close()ing the ResultSet (I don't think the examples on the documentation do it either).

How to repeat:
PreparedStatement stmt = conn.prepareStatement("INSERT INTO tbl (value) VALUES (?)", Statement.RETURN_GENERATED_KEYS);

while (true) {
    stmt.setString(1, "something");
    stmt.executeUpdate();
    ResultSet result = stmt.getGeneratedKeys();
    result.next();
    int id = result.getInt(1);
    //result.close();
}

Suggested fix:
Either document that the ResultSets created by getGeneratedKeys() are not closing by themselves, or make the behavior consistent with the ResultSets created by executeQuery().

Thank you for the report.

I can not repeat described behavior. Which versions of Connector/J and Java do you use?

Testcase

Attachment: MysqlTest.java (application/octet-stream, text), 1.30 KiB.

I'm running Windows 7 Ultimate, jdk1.6.0_23 and mysql-connector-java-5.1.20-bin.jar. I haven't tested if this occurs on jdk1.7 as well.

I believe this can be reproduced with jdk1.7.0_04 (64bit) as well if you comment out the result.next() on line 27 of my previously uploaded testcase.

Thank you for the test case.

Verified as described. To get results quicker add option -Xmx10M

Added to changelog for 5.1.22: 

ResultSet objects created by the getGeneratedKeys() method were not
being automatically closed, leading to potential memory leaks if the
application did not explicitly close the ResultSet objects.