Computer Programmer Shirt

Tuesday, February 23, 2016

The MySQL JDBC Driver Does It In Memory

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.

Of course one would expect the driver to use a server side cursor. It doesn't. There are ways of giving a JDBC driver hints about how to do the query. The driver I have ignores these and does whatever it feels like anyway. So the solution was to tell the MySQL driver in no uncertain terms, using driver specific settings, 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.