Streaming large result sets with MySQL

Question

I'm developing a spring application that uses large MySQL tables. When loading large tables, I get an `OutOfMemoryException`, since the driver tries to load the entire table into application memory. I tried using ``` statement.setFetchSize(Integer.MIN_VALUE); ``` but then every ResultSet I open hangs on `close()`; looking online I found that that happens because it tries loading any unread rows before closing the ResultSet, but that is not the case since I do this: ``` ResultSet existingRecords = getTableData(tablename); try { while (existingRecords.next()) { // ... } } finally { existingRecords.close(); // this line is hanging, and there was no exception in the try clause } ``` The hangs happen for small tables (3 rows) as well, and if I don't close the RecordSet (which happened in one method) then `connection.close()` hangs. Stack trace of the hang: SocketInputStream.socketRead0(FileDescriptor, byte[], int, int, int) line: not available [native method] SocketInputStream.read(byte[],…

Accepted Answer

Don't close your `ResultSet`s twice.

Apparently, when closing a `Statement` it attempts to close the corresponding `ResultSet`, as you can see in these two lines from the stack trace:

DelegatingResultSet.close() line: 152 DelegatingPreparedStatement(DelegatingStatement).close() line: 163

I had thought the hang was in `ResultSet.close()` but it was actually in `Statement.close()` which calls `ResultSet.close()`. Since the `ResultSet` was already closed, it just hung.

We've replaced all `ResultSet.close()` with `results.getStatement().close()` and removed all `Statement.close()`s, and the problem is now solved.

Streaming large result sets with MySQL

Solution

Problem

Related problems