Hit OutOfMemoryError when load large blob column from mysql

Hit OutOfMemoryError when load large blob column from mysql

One of my table contains one blob column, and sometimes the size of the
column value would be more than 500M, which is larger than the defalut max
heap size, e.g. -XMX is setting as 512M in my Application Server. In my
code, I have set the fetch size of PreparedSTatement to Integer.MIN_VALUE
to utilize the Mysql Stream support. Also, I add "useServerPrepStmts=true"
into the database url. With these settings, I can insert record with large
column (>500M) successfully, but when I trying to get the record, I always
hit OutOfMemoryError when calling ResultSet.next(). Note: execute query
can pass, checked with the heap size change, execute seems don't load any
real record data. ResultRet rs = Prestatement.execute(...). rs.next();
The mysql driver I'm using is 5.1.17.
Below is the exception stack. It looks like the driver tries to load the
whole column value into memory, which cause OutOfMemoryError here.
13-09-18 13:18:00,169 ERROR [STDERR] Caused by:
java.lang.OutOfMemoryError: Java heap space
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.Buffer.ensureCapacity(Buffer.java:156)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.Buffer.writeBytesNoNull(Buffer.java:514)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.MysqlIO.readRemainingMultiPackets(MysqlIO.java:3219)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3077)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3520)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1433)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:416)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:395)
2013-09-18 13:18:00,169 ERROR [STDERR] at
com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7165)
2013-09-18 13:18:00,169 ERROR [STDERR] at
org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
2013-09-18 13:18:00,169 ERROR [STDERR] at
org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
2013-09-18 13:18:00,169 ERROR [STDERR] at
sun.reflect.GeneratedMethodAccessor86.invoke(Unknown Source)
2013-09-18 13:18:00,169 ERROR [STDERR] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
2013-09-18 13:18:00,169 ERROR [STDERR] at
java.lang.reflect.Method.invoke(Method.java:597)
2013-09-18 13:18:00,169 ERROR [STDERR] at
org.apache.jackrabbit.core.util.db.ResultSetWrapper.invoke(ResultSetWrapper.java:66)
2013-09-18 13:18:00,169 ERROR [STDERR] at $Proxy68.next(Unknown Source)
2013-09-18 13:18:00,169 ERROR [STDERR] at
org.apache.jackrabbit.core.data.db.DbDataStore.openStream(DbDataStore.java:542)
2013-09-18 13:18:00,169 ERROR [STDERR] at
org.apache.jackrabbit.core.data.db.DbInputStream.openStream(DbInputStream.java:68)
2013-09-18 13:18:00,169 ERROR [STDERR] at
org.apache.jackrabbit.core.data.db.DbInputStream.read(DbInputStream.java:110)