MySQL Issue Caused by Query That Returns a Large Result

I was getting an odd exception thrown from some EJBs that came, in my code, originally from PreparedStatement.executeQuery().

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Exceptions came from trying to free the connection after it had become invalid. The true root of the trouble was running out of memory, then abruptly closing the connection. Even though the exception was handled and closed connection returned to the pool, there was memory not freed someplace; in the JDBC driver it seems. That was the real problem.

The queries were unusual in that the result set would contain a relatively large number of rows. This is not a good practice of course, but I only have a handful of these cases, for good reasons.

One would expect the driver to use a server side cursor. It doesn't. So the solution was to tell the MySQL driver that the results would be scanned forward only, etc. so as to conserve memory.

PreparedStatement stmt;
...
stmt = conn.prepareStatement("select * from MyLargeTable",
java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = stmt.executeQuery();

Problem solved.