Using findIterate with MySQL streams the entire result set at once

[Ronnie76er]

Using findIterate streams the entire result set. If it's particularly large, this could cause a Java OutOfMemoryError when calling findIterate.

According to the MySQL JDBC documentation, it seems like the statement needs to be constructed like this for streaming to work correctly:

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

MySQL JDBC doc: http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-implementation-notes.html
Google Group Topic: https://groups.google.com/forum/#!topic/ebean/PaMDPEetbJI

[rbygrave]

I have pushed up a fix. On the DatabasePlatform there is a forwardOnlyHintOnFindIterate. This is set to true on the MySqlPlatform. For creating PreparedStatements for findIterate() and findVisit() this databasePlatform hint is checked and if set creates the statement with the appropriate hints.

So out of the box this fix is specific to MySql and findIterate() or findVisit().

If you can pull/build and confirm this fixes your specific case that would be great.

[Ronnie76er]

Thanks, I'll give it a shot this week and let you know how it goes.

[Ronnie76er]

Sorry for the delay. This seems to be working well now.

I tested this in my play application, first without it there, and then with it there. Without it, the memory after using findIterate, but before iterating through the values, had sharply risen.

Updating to use 3.2.5, the memory didn't start going down until after iterating through the list provided by findIterate.

[rbygrave]

Right so I'll close this issue as fixed then.

[Ronnie76er]

I apologize, but I think it may still be gathering the entire resultset in memory. Using a debugger on the code, it seems that the fetchSize is getting set to 0, instead of Integer.MIN_VALUE. I think the fetchSize getting set is important to indicate to the driver that the results should be streamed.

[rbygrave]

Yes, I have reproduced this and it is not fixed without

pstmt.setFetchSize(Integer.MIN_VALUE);

[rbygrave]

Fixed in 4.0.1