MySQL Bugs: #26632: Memory leak

Bug #26632	Memory leak
Submitted:	26 Feb 2007 12:38	Modified:	31 Mar 2014 12:20
Reporter:	Julian Steinlein	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	5.0.4	OS:	Windows (Windows XP SP2)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
If I do not close the connection after fetching rows via a ResulSet, I will get a memory leak even if I close the ResultSet and the Statement.

How to repeat:
1) Memory leak:

public void foo(...) {
      // use class member con as connection
      Statement st = con.createStatement();
      ResultSet rs = st.executeQuery(...);

      while (rs.next()) {
         ... = rs.getString(...);
         ... = rs.getInt(...);
         ... = rs.getFloat(...);
         ...
      }

      rs.close();
      st.close();
   }
   catch (SQLException e) {
      e.printStackTrace();
   }

}

2) No memory leak:

public void foo(...) {
   try {
      Connection con = DriverManager.getConnection(...);

      Statement st = con.createStatement();
      ResultSet rs = st.executeQuery(...);

      while (rs.next()) {
         ... = rs.getString(...);
         ... = rs.getInt(...);
         ... = rs.getFloat(...);
         ...
      }

      rs.close();
      st.close();
      con.close();
   }
   catch (SQLException e) {
      e.printStackTrace();
   }
}

Call foo() multiple times (> 1000).

In the case of my application I call a method which is similar to foo() approx. 35000 times.

Hi Julian and thanks for your report.

Can you please post MySQL server version, JDK version and attach full test case.
I presume there are problems with connection settings rather than memory leak:
*holdResultsOpenOverStatementClose*
Should the driver close result sets on Statement.
close() as required by the JDBC specification?
false 3.1.7
and
*dontTrackOpenResources* The JDBC specification requires the driver to automatically
track and close resources, however if
your application doesn't do a good job of explicitly
calling close() on statements or result sets, this can
cause memory leakage. Setting this property to
true relaxes this constraint, and can be more
memory efficient for some applications.
for example.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi,

I have this exact same problem.

It is easy to reproduce.

Simply Keep a connection open and keep sending thousands of queries to the database in a loop.

In my case I get this error by fetching a resultset i.e.

Select * from Table;

Then doing an insert on the table.

Then close resultset, fetch the resultswt again, and repeat, using the same connection.

You can also get the same result by not using a resultSet and simply doing repeated

insert into ...... where ....

After many thousands of loops it will again crash with the the same results.

This is using default settings.

It occurs on:

Connector 5.0.5 with server:

5.0.27

It also occurs with server

5.1 (beta version)

This occurs on linux with xeon processor, pc with pentium 4 and linux running opn power pc.

I have tried it on all three.

You didn't post any _java_ code, but my guess is your code either opens thousands of result sets and/or statements and doesn't close them. 

They won't "go away" until the connection instance is closed. This behavior is per the JDBC-spec.

Matthew,

(as posted in the forums as well)

The language about GC automatically closing statement and result sets is actually _removed_ from JDBC-4.0, because in practice it's not possible to implement. 

This is certainly the case with prepared statements that hold on to server-side resources, since an implementor can not synchronize access to the communications with the server to free up things like cursors or prepared statements from within a finalizer (which is how the instance will be notified that it is being GC'd, either because it's out of scope or in your patch, weakly referenced). 

Take a look at http://java.sun.com/javase/6/docs/api/java/sql/Statement.html#close()

(you'll notice that there's no reference to GC closing statements automatically, it has been removed, and similar language in ResultSet.close() has been removed).

We've already tried the weak reference and finalizer routes. If you benchmark a driver using it, and not using it, you'll find that you take a hit of anywhere between 10 and 15% in throughput as well, specifically due to the "registration" into the reference queue subsystem that is required for this to work.

All vendors who particpiate in the JDBC experts' group have concluded that relying on garbage collection to free up JDBC resources is a bad idea, and generally not implementable, so don't depend on this behavior when you write your applications.

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.