Streaming large result sets with MySQL

Question

I am developing a spring application that uses large MySQL tables. When loading large tables, I get an OutOfMemoryException , as the driver tries to load the entire table into the application memory.

I tried to use

 statement.setFetchSize(Integer.MIN_VALUE); 

but then every ResultSet that I open hangs on close() ; I found this to happen because it tries to load any unread lines before closing the ResultSet, but this is not the case as I do it:

 ResultSet existingRecords = getTableData(tablename); try { while (existingRecords.next()) { // ... } } finally { existingRecords.close(); // this line is hanging, and there was no exception in the try clause } 

Things happen for small tables (3 rows), and if I don't close the RecordSet (which happened in one method), then connection.close() freezes.

---

Stack trace:

SocketInputStream.socketRead0 (FileDescriptor, byte [], int, int, int) string: not available [native method]
SocketInputStream.read (byte [], int, int): 129
ReadAheadInputStream.fill (int): 113
ReadAheadInputStream.readFromUnderlyingStreamIfNecessary (byte [], int, int): 160
ReadAheadInputStream.read (byte [], int, int) string: 188
MysqlIO.readFully (InputStream, byte [], int, int): 2428 MysqlIO.reuseAndReadPacket (Buffer, int): 2882
MysqlIO.reuseAndReadPacket (buffer): 2871
MysqlIO.checkErrorPacket (int): 3414
MysqlIO.checkErrorPacket (): 910
MysqlIO.nextRow (field [], int, boolean, int, boolean, boolean, boolean, Buffer): 1405
RowDataDynamic.nextRecord () String: 413
String RowDataDynamic.next (): 392 String RowDataDynamic.close (): 170
JDBC4ResultSet (ResultSetImpl) .realClose (logical) string: 7473 JDBC4ResultSet (ResultSetImpl) .close (): 881 DelegatingResultSet.close (): 152
DelegatingResultSet.close (): 152
DelegatingPreparedStatement (DelegatingStatement) .close (): 163
(This is my class). Database.close (): 84

Answer 1

Do not close ResultSet twice.

Apparently, when closing the Statement he tries to close the corresponding ResultSet , as you can see in these two lines from the stack trace:

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

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

We replaced all ResultSet.close() with results.getStatement().close() and deleted all Statement.close() s, and the problem is now resolved.

Answer 2

Just setting the sample size is the wrong approach. javadoc Statement#setFetchSize() already states the following:

Gives the JDBC driver a hint regarding the number of rows to be retrieved from the database

The driver can actually apply or ignore the prompt. Some drivers ignore it, some drivers use it directly, some drivers require more options. The MySQL JDBC driver is in the latter category. If you check the MySQL JDBC documentation , you will see the following information (scroll about 2/3 to the ResultSet header):

To enable this functionality, you need to instantiate the Statement as follows:

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

Please read the entire section of the document, it also describes reservations about this approach. Here are the relevant quotes:

There are some caveats with this approach. You will need to read all the lines in the result set (or close it) before you can issue any other requests in the connection, or an exception is thrown.

(...)

If the operator is within the scope of the transaction, then the locks are released when the transaction ends (which implies that the operator must execute first). As in most other databases, the statements are not populated until all pending results have been read, or the active result set for the statement is closed.

If this does not fix OutOfMemoryError (not Exception ), the problem is that you are storing all the data in Java memory instead of quickly processing it as data becomes available. This will require more changes to your code, possibly a complete rewrite. I answered a similar question before here .

Answer 3

If someone has the same problem, I solved it using the LIMIT clause in my request.

This problem was reported by MySql as an error (find here http://bugs.mysql.com/bug.php?id=42929 ), which now has the status "no error", the most important part:

There is currently no way to close the Medium Stream result set

Since you need to read ALL lines, you will have to limit the results of the query using a WHERE or LIMIT clause. Also try the following:

 ResultSet rs = ... while(rs.next()) { ... if(bailOut == true) { break; } } while(rs.next()); // This will deplete the remaining rows on the stream rs.close(); 

It may not be perfect, but at least it is holding you back.

Answer 4

If you are using spring jdbc, you need to use the created creator in combination with SimpleJdbcTemplate to set fetchSize as Integer.MIN_VALUE. Described here is http://neopatel.blogspot.com/2012/02/mysql-jdbc-driver-and-streaming-large.html

Answer 5

This freezes because even if you stop listening, the request is still ongoing. To close ResultSet and Statement in the correct order, try calling statement.cancel () first:

 public void close() { try { statement.cancel(); if (resultSet != null) resultSet.close(); } catch (SQLException e) { // ignore errors on closing } finally { try { statement.close(); } catch (SQLException e) { // ignore errors on closing } finally { resultSet = null; statement = null; } } }