MySQL Bugs: #15024: executeUpdate() closes InputStream set with setBinaryStream()

Bug #15024	executeUpdate() closes InputStream set with setBinaryStream()
Submitted:	17 Nov 2005 15:49	Modified:	22 Nov 2005 19:51
Reporter:	Per Lindberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.0	OS:	Windows (Windows XP SP2)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
The method executeUpdate CLOSES an associated InputStream set on a PreparedStatement with setBinaryStream.

It should not do that. The documentation for java.sql says nothing about closing the InputStream in this situation (or any other).

executeUpdate should of course read the InputStream until read() returns -1.
But that does not mean that EOF has been reached, and certainly not that it has the right to close the stream.

This is a complete show-stopper with no workaround.

For example, when the InputStream is a ZipInputStream (java.util.zip), it typically contains several files, each preceded with a ZipEntry. When executeUpate closes the InputStream, it is not possible to read the next ZipEntry and store the next file with another call to setBinaryStream and executeUpdate. And so on.

Of course, no other method (such as PreparedStatement.close) should close the InputStream, either.

How to repeat:
Create an application that

1. opens a ZipInputStream to a ZIP file containing multiple files
2. repeatedly calls
     - getZipEntry (to get the file name)
     - setBinaryStream (to a LONGBLOB field)
     - executeUpdate (to write the data)
   until getZipEntry returns null.

You will notice that the second call to getZipEntry will fail with an exception because the InputStream has been closed by executeUpdate.

Suggested fix:
Don't let excecuteUpdate (or any other method) close an associated InputStream. That's the job of the calling application.

Actually, there is a workaround (until I get feedback from the JDBC EG, as other vendors do the same as we do to see how we fix this), which is to wrap your InputStream, and override close() to not do anything.

Actually, there is a workaround (until I get feedback from the JDBC EG, as other vendors do the same as we do to see how we fix this), which is to wrap your InputStream, and override close() to not do anything.

Another problem is the third parameter to setBinaryStream(), which should, according to the Java Doc, be the "the number of bytes in the stream", but that is not always possible to calculate beforehand. For example, ZipInputStream.available() always returns 1 as long as EOF is not reached. (I guess that it's impossible for it to do anything else). Therefore, always passing in.available() as the third parameter is not possible.

It SEEMS to be possible to use the value -1 as "don't know", but that is undocumented. It seems to work for at least the MySQL JDBC driver, but who knows for which others.

I presume that this issue should be addressed the EG as well. The simplest soulution would probably be to standardize on -1 as "don't know", and update the standard Java Doc accordingly.

Oh, I should add that passing 1 (the return value from ZipInputStream.available()) as the third parameter to setBinaryStream() causes it to store only the first byte from the stream, and nothing more. Perhaps logical, but shows that in.available() can't be used to reliably calculate the number of bytes to store. And AFAIK there's no other way to calculate it, either. So a standardized and documented "don't know" value is definitely needed.

Unfortunately there are vendors that _require_ the number of bytes beforehand (DB2 with their DRDA protocol comes to mind), which is why this restriction exists in JDBC.

You can always use "useStreamLengthsInPrepStmts=false" in a MySQL JDBC URL and our driver will ignore the parameter and just read until the end of the stream.

Thanks for the information!

Can the vaule -1 as third parameter to setBinaryStream() be regarded as a reliable alternative to "useStreamLengthsInPrepStmts=false" in the MySQL JDBC URL ?

Fixed for 3.1.12 and 5.0.0. See nightly snapshot build at http://downloads.mysql.com/snapshots.php#connector-j after 00:00 GMT Nov 23 (i.e. after tonight's nightly build) or current subversion repository at http://svn.mysql.com/svnpublic/connector-j/branches/branch_3_1 for the fix.

Thanks for the bug report!

btw, "-1" should work as "ignore the length", at least it's been in the driver for quite a few years....

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32723