| Bug #44056 | ResultSet from getGeneratedKeys() can not be closed => Java Heap Space exceeds | ||
|---|---|---|---|
| Submitted: | 2 Apr 2009 21:37 | Modified: | 17 Apr 2009 12:12 |
| Reporter: | Uli Sopar | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S1 (Critical) |
| Version: | 5.1.7 | OS: | Windows |
| Assigned to: | Mark Matthews | CPU Architecture: | Any |
| Tags: | generatedKeys auto_increment ResultSet prepared Statement | ||
[6 Apr 2009 14:14]
Tonci Grgin
Hi Uli and thanks for your report. Using both JDK 1.5 and 1.6 against latest c/J 5.1 sources I was not able to repeat your problem. The only thing I can come up with is: - Limit JVM to 16MB - Double your loop to 100000 executions yields: 86338 86339 java.lang.OutOfMemoryError: Java heap space Dumping heap to java_pid7480.hprof ... Heap dump file created [17987040 bytes in 0.689 secs] E Time: 41,073 There was 1 error: 1) testBug44056(testsuite.simple.TestBug44056)java.lang.OutOfMemoryError: Java heap space Now, this is not actual error. Actual error is: 84831 E Time: 39,137 There was 1 error: 1) testBug44056(testsuite.simple.TestBug44056)java.lang.OutOfMemoryError: GC overhead limit exceeded Although Mat shows no retained memory for ResultSet: Class name Shallow Heap Retained Heap Percentage com.mysql.jdbc.JDBC4ServerPreparedStatement @ 0x87e1e48 296 15.553.664 95,09% java.util.ArrayList @ 0x87e1f78 24 15.548.552 95,06% java.lang.Object[88256] @ 0x916db50 353.040 15.548.528 95,06% com.mysql.jdbc.JDBC4ResultSet @ 0x87a9c30 176 176 0,00% com.mysql.jdbc.JDBC4ResultSet @ 0x87a9ce0 176 176 0,00% com.mysql.jdbc.JDBC4ResultSet @ 0x87a9d90 176 176 0,00% --<cut>-- Total: 20 entries 3.520 3.520 0 accumulated objects by class show the problem: Label Number Of Objects com.mysql.jdbc.JDBC4ResultSet 86.338 Used Heap Size Retained Heap Size 15.195.488 15.195.488 So, verified as described as rs is kept in a list stored with statement which is *not* closed until after the loop finishes.
[6 Apr 2009 14:15]
Tonci Grgin
Full JVM and connection string setting: -XX:+HeapDumpOnOutOfMemoryError -Xmx16M -XX:+UseParallelGC -Djavax.net.debug=all -Dcom.mysql.jdbc.java6.javac=C:\jvms\jdk1.6.0\bin\javac.exe -Dcom.mysql.jdbc.java6.rtjar=C:\jvms\jdk1.6.0\jre\lib\rt.jar -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://xxx:yyy/test?user=root&password=****&autoReconnect=false&connectTimeout=5000&socketTimeout=30000&useUnicode=true&characterSetResults=utf8&useInformationSchema=true&useServerPrepStmts=true
[6 Apr 2009 14:17]
Tonci Grgin
Test case for our test framework.
Attachment: TestBug44056.java (text/x-java), 1.90 KiB.
[9 Apr 2009 19:08]
Mark Matthews
See http://bazaar.launchpad.net/~mark-mysql/connectorj/5.1/revision/769
[17 Apr 2009 12:12]
Tony Bedford
An entry was added to the 5.1.8 changelog: Statement.getGeneratedKeys() retained result set instances until the statement was closed. This caused memory leaks for long-lived statements, or statements used in tight loops.
[3 Aug 2009 6:17]
Tonci Grgin
Bug#41086 was marked as duplicate of this report.
Description: I wrote a simulation for my university research project. The data generated by the simulation is recorded via MySQL. For performance reasons I am using prepared statements. The tables have auto_increment columns. Therefore i use the pstmt.getGeneratedKeys() method to retrieve the keys. Even though I explicitly close the returned ResultSet, the memory used by the ResultSet adds up, be repeated calls of the prepared statement, until the java heap size exceeds. This is what the profiling tool tells me. U can repeat this problem with the attached source. It is just a small example but with a profiling tool u can see that the memory does not get released by the rs.close() call. Only closing the prepared statement releases the memory. OS: WinXP Mysql-Server: 5.0.51b-community-nt MySQL Community Edition (GPL) JDK: 1.6.0_10 Connector/J: 5.1.7 Run from Eclipse Ganymede How to repeat: // Just a simple example to verify package util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class MySQLTest { public void createRelation(Connection c) { try { Statement st = c.createStatement(); st.execute("CREATE TABLE `auto_incr` (" + "`entry` varchar(5) default NULL," + "`id` int(11) NOT NULL auto_increment," + "PRIMARY KEY (`id`)" + ")"); st.close(); } catch (Exception ex) { ex.printStackTrace(); } } public void dropRelation(Connection c) { try { Statement st = c.createStatement(); st.execute("DROP TABLE `auto_incr`"); st.close(); } catch (Exception ex) { ex.printStackTrace(); } } public void preparedStatementTest(Connection c) { try { PreparedStatement pstmt = c.prepareStatement("INSERT INTO auto_incr (entry) VALUES (?)", Statement.RETURN_GENERATED_KEYS); ResultSet rs = null; for (int i=0; i< 50000; i++) { pstmt.setString(1, "test"); pstmt.executeUpdate(); rs = pstmt.getGeneratedKeys(); while (rs.next()) { System.out.println(rs.getInt(1)); } rs.close(); } } catch (Exception ex) { ex.printStackTrace(); } } public static void main(String args[]) { try { Class.forName("org.gjt.mm.mysql.Driver").newInstance(); Connection c = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test", "root", "test"); MySQLTest t = new MySQLTest(); t.createRelation(c); t.preparedStatementTest(c); t.dropRelation(c); c.close(); } catch (Exception ex) { ex.printStackTrace(); } } } Suggested fix: None so far, sorry!