MySQL Bugs: #7698: JDBC:ODBC - Large queries max out memory

Bug #7698	JDBC:ODBC - Large queries max out memory
Submitted:	6 Jan 2005 6:07	Modified:	6 Jan 2005 14:31
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	4.0.17	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
MySQL Connector/J version: mysql-connector-java-3.0.10-stable-bin.jar

Keep getting OutOfMemory errors when retrieve large result sets.

How to repeat:
Do the following in java. MY_TABLE should have more than 250,000 records:

String sql = "SELECT * FROM MY_TABLE";
ResultSet rs;
try {
	rs = statement.executeQuery(sql);
} catch (SQLException e) {
}

try {rs.close();} 
catch (SQLException e1) {e1.printStackTrace();}

Get OutOfMemory error

Suggested fix:
MySQL seems to buffer all the records returned into memory. Perhaps need to only read first X records, then as rs iterates along read from memory again.

This is due to a limitation in the MySQL protocol itself, and how this limitation interacts with the JDBC API requirements for multiple open statements. This is going to be fixed in MySQL 5.0 when used with Connector/J 3.2 or newer.

Please see the manual section

http://dev.mysql.com/doc/connector/j/en/index.html#id2424991

(section 'ResultSet')

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to 'stream' the results back one row at-a-time.

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to "stream" result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row.

There are some caveats with this approach. You will have to read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown. Also, any tables referenced by the query that created the streaming result will be locked until all of the results have been read or the connection closed."