MySQL Bugs: #15062: Memory/Object leak on inserts

Bug #15062	Memory/Object leak on inserts
Submitted:	18 Nov 2005 23:03	Modified:	19 Nov 2005 18:20
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.11	OS:	Any (Any)
Assigned to:		CPU Architecture:	Any

Description:
When using the 3.1.11 version of the Connector/J driver there is a memory/object leak when performing inserts.

I have a batch insert program written in Java that inserts several hundred thousand rows of data into a single table.  When using the 3.1.11 version of the driver the heap continues to grow and the program crashes with an OutOfMemoryError reported by the JVM, even after multiple attempts to tune GC etc.  I have used the NetBeans profiler to solidly pinpoint this problem within the jdbc driver itself.  It should be noted that the memory leak problem starts to present itself almost immediately after inserts begin and tracking heap and object life it becomes very apparent.

The problem exists for BOTH Connection.prepareStatement(); AND Connection.createStatement();

When I revert to the 3.0.17 version of the driver the problem goes away and the program executes completely inserting over 5 Million rows w/ just 32MB of heap.

How to repeat:
Create a simple console app that inserts data into a database read from a flat file or another database table.  Use Connection.createStatement(); and monitor the heap.  

Suggested fix:
Identify leak, plug hole. :D

Are you closing statements when you're done with them? The only "leaks" we've seen are when folks don't follow the requirements of the JDBC spec and don't close statements when they're done with them. I'm not saying that you're not experiencing some leak we don't know about, but we have quite a few users using Connector/J 3.1.10 and .11 to do exactly what you do and they don't see leaks.

Inserting millions of records is one of the tests we run, and we don't see leaks during our unit testing.

Can you please post an example of the code you use to do this insertion? Maybe you're using some code path we or other users don't use that may leak, but with no example of what you're doing, we can't reproduce it to fix it.

A quick test if your application isn't closing statements as needed is to add "dontTrackOpenResources=true" to your JDBC URL configuration parameters. If the leak goes away, then something in your application isn't closing statements and/or result sets. The code that tracks open resources was added in 3.1, and is required by the JDBC specification, as the spec requires that various resources are explicitly closed during certain portions of the "parent's" lifecycle if the application doesn't close them explicitly. This means that if you don't close statements explicitly, they're not closed until the connection that created them is _physically_ closed, which _will_ look like a leak, since the statement instances can't be GC'd until unreferenced from the connection that created them, which only happens when you close the connection.

I've had multiple people look at my impl. of the code and we were all convinced this was a bug within the driver, but after further carefull looking and the feedback from the bug group we have determined that the close statement code is not executing as was previously thought.

Thanks for all the fast responses...