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:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.20 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: ResultSet leak

[3 Jun 2012 19:17] Stefan M
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");
    ResultSet result = stmt.getGeneratedKeys();
    int id = result.getInt(1);

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().
[5 Jun 2012 17:38] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Which versions of Connector/J and Java do you use?
[12 Jun 2012 11:58] Stefan M

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

[12 Jun 2012 12:00] Stefan M
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.
[12 Jun 2012 12:38] Stefan M
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.
[13 Jun 2012 20:08] Sveta Smirnova
Thank you for the test case.

Verified as described. To get results quicker add option -Xmx10M
[5 Sep 2012 18:03] John Russell
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.