MySQL Bugs: #15477: Memory Leak, depending on SQL statement

Bug #15477	Memory Leak, depending on SQL statement
Submitted:	5 Dec 2005 9:29	Modified:	6 Dec 2005 13:05
Reporter:	Rolf Mueller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	3.1.12	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
Hello,

I am the author of the open source project IMS.Server (http://sourceforge.net/projects/ims-server/). The homepage for the project is http://www.managedimages.org.

I am using MySQL 4.1 together with the JDBC connector 3.1.12.

I experience a memory leak, obviously depending on the SQL statement I query.

The following query (length=449 chars) works fine:
SELECT ims_server_td_p_image_store.fileName FROM ims_server_md_imagesize,ims_server_td_p_image_thumbnail,ims_server_td_p_image_store WHERE ims_server_td_p_image_thumbnail.imageId=295253 AND ims_server_td_p_image_thumbnail.imageSizeId=2326 AND ims_server_md_imagesize.imageSizeId=ims_server_td_p_image_thumbnail.imageSizeId AND ims_server_md_imagesize.status=1 AND ims_server_td_p_image_store.imageStoreId=ims_server_td_p_image_thumbnail.imageStoreId

The following query (length=506 chars) retrieves the correct result, but leaks memory:
SELECT ims_server_td_p_image_store.fileName FROM ims_server_md_imagesize,ims_server_td_p_image_thumbnail,ims_server_td_p_image_store,ims_server_td_p_image WHERE ims_server_td_p_image_thumbnail.imageId=295253 AND ims_server_td_p_image_thumbnail.imageSizeId=2326 AND ims_server_md_imagesize.imageSizeId=ims_server_td_p_image_thumbnail.imageSizeId AND ims_server_td_p_image.status=2 AND ims_server_md_imagesize.status=1 AND ims_server_td_p_image_store.imageStoreId=ims_server_td_p_image_thumbnail.imageStoreId

I tested this with JProfiler, what I saw is that the byte[] are just accumulating for java.sql.executequery(), and they never get released or garbage collected. Forced garbage collection also does not work. After executing the query 300 times, the JVM uses about 40MBytes.

I am happy to help you out with a testcase. The code is open, please check sourceforge. Communication using e-mail preferred (sitting in Australia).

How to repeat:
I wrote a test program, which reproduces this behavior.

Suggested fix:
No idea, how to fix this.

Rolf, could you attach your testcase here?

Hello Aleksey,

What files do you need in order to be able to set up your test case?

Regards,

Rolf

Hello Aleksey,

Would you be able to download the app.ims.server.0.9.4.6.zip from http://sourceforge.net/project/showfiles.php?group_id=112160&package_id=121315

If yes, just unzip this file into C:\, it will create C:\managedimages. Goto C:\managedimages\install.windows, just ensure that JAVA_HOME and ANT_HOME is set properly. Then run install.bat. This creates a new directory: c:\managedimages\instance.dev. Goto C:\managedimages\instance.dev\properties\acf.0.9.4.6\acf.properties, edit persistence.properties: provide database name, user, password. Then goto C:\managedimages\instance.dev\installation, running the create-tables.bat script creates the tables for the application. 

In order to reproduce the case, I could send you the test-class I wrote...

It would be great if you attach table definitions and some test data , so we will be able to run your query and check if memory leaks...

Table Definition

Attachment: 2005-12-05 TableDefinitions.txt (text/plain), 12.08 KiB.

Just lost my content by accidentally dropping my tables...
Have to set up the test enviornment again...
But don't believe it depenst on the data, since I have
the same running on a server with different data,
and there it is reproducable as well...

rolf, thanks, I'll try to reproduce memory leak

Are you sure that all code paths call .close() on the result sets that are created? Otherwise the resources they allocate won't be GC'd until the statement that created them is closed, and if you don't explicitly call .close() on statements, then those survive until the physical connection is closed.

I noticed that you developed initial versions of your software with Connector/J 3.0, and are now deploying with Connector/J 3.1. This is a common issue that folks who are doing just that run into, as Connector/J 3.1 follows the JDBC specification much closer than 3.0, and the JDBC specification requires this "tracking" of open resources and .close() "chaining".

If adding "dontTrackOpenResources=true" to your URL makes the memory leak go away, then most likely it is your code that is leaking JDBC resources somewhere.

I tried to dig into your code, but found it complex enough that it would be better if you could provide a simple standalone testcase that demonstrates this behavior, or point us to where the query in question is being generated, issued and traversed, and where you think the result set in question should be getting closed.

Hello Mark, Aleksey,

I have to admit the error is on my side. I finally found it.
My PersistenceControler class, which extends the ConnectionPool,
just released the connection back into the pool. It never closed
the ResultSet. I changed it, and the memory leakage went away.
What took me by supirse is, that it worked on all other SQL statements
(using the same code) without problems. That's why I believed
in the first place, the error is caused by the JDBC driver.
Thanks anyway for looking into it, I'll update sourceforge soon.

Regards,

Rolf