MySQL Bugs: #2139: PreparedStatement.setBlob implementation is broken

Bug #2139	PreparedStatement.setBlob implementation is broken
Submitted:	17 Dec 2003 4:12	Modified:	17 Dec 2003 5:56
Reporter:	Juergen Hoeller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.0.9	OS:	Windows (Windows 2000)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
PreparedStatement.setBlob invokes the escapeblockFast method with Integer.MAX_VALUE as size: escapeblockFast tries to iterate over the given byte array with the given size as upper limit. This can only work with Blob byte arrays of length Integer.MAX_VALUE... so obviously it does not work at all!

How to repeat:
Invoke PreparedStatement.setBlob with any java.sql.Blob instance, like as returned by Hibernate.createBlob (http://www.hibernate.org), or a custom implementation of it. setBlob obviously cannot work in any case because of its broken implementation.

Suggested fix:
Besides the upper limit bug, setBlob is generally implemented very inefficiently: It reads in the whole BLOB input stream as byte array, and copies it to an escaped byte array. In case of a large BLOB with, let's say, 20 MB, this means 40 MB of heap memory - while this should really get streamed to the database, with just a buffer in memory.

Connector/J 2.0.14 and 3.1.0 alpha both implement setBlob via delegating to setBinaryStream. This seems to be a good strategy: Why doesn't the 3.0.x series work that way too? For the time being, we'll use setBinaryStream directly -  which other databases like Oracle don't accept for large BLOBs, so we unfortunately have to resort to database-specific strategies.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

> PreparedStatement.setBlob invokes the escapeblockFast method with
> Integer.MAX_VALUE as size: escapeblockFast tries to iterate over the given
> byte array with the given size as upper limit. This can only work with
> Blob byte arrays of length Integer.MAX_VALUE... so obviously it does not
> work at all!

This is a known issue, which was reported earlier (and was related to setBlob() not 'holding' a value between execute()s, and has been fixed in the nightly snapshot builds since Sept. 12th.

> Suggested fix:
> Besides the upper limit bug, setBlob is generally implemented very
> inefficiently: It reads in the whole BLOB input stream as byte array, and copies
> it to an escaped byte array. In case of a large BLOB with, let's say, 20 MB,
> this means 40 MB of heap memory - while this should really get streamed to the
> database, with just a buffer in memory.

Sorry, but that's how the MySQL protocol (prior to MySQL 4.1.0) works. There is no way to 'stream' the BLOB to the database, and there is no way to send it to the database without escaping it first, _and_ the entire size of the query that contains the BLOB needs to be sent before the BLOB. Server-side PreparedStatements in 4.1 (supported in Connector/J 3.1.x) support 'chunking' a BLOB to the database.

> Connector/J 2.0.14 and 3.1.0 alpha both implement setBlob via delegating to
> setBinaryStream. This seems to be a good strategy: Why doesn't the 3.0.x series
> work that way too? For the time being, we'll use setBinaryStream directly - 
> which other databases like Oracle don't accept for large BLOBs, so we
> unfortunately have to resort to database-specific strategies.

3.1.x does this to be able to support the chunking, but unless you're using MySQL-4.1.0 or newer, you're going to be allocating the roughly the same amount of memory as before. (3.0.10 does this as well).

Please see if a nightly snapshot build of 3.0 solves this for you (from http://downloads.mysql.com/snapshots.php). 3.0.10 is scheduled to be released before the holidays.