Bug #9812 Memory leak in closing statement
Submitted: 11 Apr 2005 11:30 Modified: 11 Apr 2005 14:53
Reporter: Alessandro Valbonesi Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.1 - 3.2 OS:MacOS (Mac OS X)
Assigned to: CPU Architecture:Any

[11 Apr 2005 11:30] Alessandro Valbonesi
Description:
When a Statement object is reclaimed from the Java garbage collector, not all the resources are released.
This causes each query to consume some memory resources. 
Executing many queries in a loop, the garbage collector runs more and more frequently, the system slows down progressively until you end up with an out of memory error message in Java.

How to repeat:
1. run System.gc() and check the available memory
  System.gc();
  System.out(Runtime.getRuntime().freeMemory());

2. create a Statement and execute a query
  Statement stat = connection.createStatement()
  ....
  stat.executeQuery();

3. set any created object to null to make new objects eligible for gc
  stat = null;
  ...

4. run gc() again and check the available memory 
  System.gc();
  System.out(Runtime.getRuntime().freeMemory());

you should see a smaller amount of free memory, wich means something didn't clean-up properly

Notes:
MySql Connctor/J 3.0 does not have this bug
MySql Connctor/J 3.1 and 3.2 have the bug

Workaround:
If you explicitly close the statement after usage with stat.close(); the memory is freed up.
The statement should be closed automatically when the statement is garbage collected, but it seems it isn't.
[11 Apr 2005 14:53] Mark Matthews
Connector/J 3.1.8 and newer implement finalizers to take care of this, however note that even though the JDBC spec does state that it is required for statements to clean up after themselves, effectively in many cases they _can't_ completely (because of the indeterminate way in which finalizers run, most JDBC drivers, including ours will not attempt to communicate with the server to release server-side resources, as it is usally a surefire deadlock situation), which is why in other JDBC-related documents from Sun, they state that client programs really, really should close any JDBC resources they create.

There is a reason those methods are there, so use them! (In fact, even more such methods for resource control are being added for JDBC-4.0).

3.1.8 will be released before the users' conference next week. This code is in the current nightly snapshots available at http://downloads.mysql.com/snapshots.php