Bug #53002 Failure to update a BLOB field using an Updatable ResultSet
Submitted: 20 Apr 2010 18:43 Modified: 6 May 2016 14:05
Reporter: Nicholas Daniels Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.12 OS:Microsoft Windows (Version 2002, Service Pack 3)
Assigned to: Filipe Silva CPU Architecture:Any
Tags: BLOB, CONCUR_UPDATABLE, ResultSet, updateBinaryStream()

[20 Apr 2010 18:43] Nicholas Daniels
Description:
A table is created with a PRIMARY KEY and a variety of field types, including a LONGBLOB.
The table is given a row entry.
A selection from the DB is made, selecing all fields, using a PreparedStatement.
The ResultSet obtained is then used to attempt an update of the LONGBLOB using the ResultSet.updateBinaryStream() method.
An exception is thrown on attempt.
The exception: 
"com.mysql.jdbc.NotUpdatable: Result Set not updatable.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
        at com.mysql.jdbc.JDBC4UpdatableResultSet.updateBinaryStream(JDBC4UpdatableResultSet.java:64)
        at com.mysql.jdbc.JDBC4UpdatableResultSet.updateBinaryStream(JDBC4UpdatableResultSet.java:141)
        at BlobUpdateTest.updateBLOB(BlobUpdateTest.java:21)
        at BlobUpdateTest.main(BlobUpdateTest.java:53)"

**************************************
The issue is that the ResultSet IS create as type ResultSet.CONCUR_UPDATABLE, DOES select from only one table, and DOES select ALL primary keys for that table.

How to repeat:
1. Create the table:
CREATE TABLE  `test_me`.`test_table` (
  `Rec` bigint(20) NOT NULL AUTO_INCREMENT,
  `Date` datetime DEFAULT NULL,
  `Set_Test` set('A','B','C') DEFAULT NULL,
  `Enum_Test` enum('a','b','c') DEFAULT NULL,
  `Small_Text` varchar(10) DEFAULT NULL,
  `Text` varchar(120) DEFAULT NULL,
  `big_blob` longblob,
  `big_text` longtext,
  `var_text` varchar(145) DEFAULT NULL,
  PRIMARY KEY (`Rec`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Give the database a row entry:
INSERT INTO `test_me`.`test_table` VALUES()

3. Run the code (set BINARY_FILE_NAME with an actual binary file name):
import java.io.*;
import java.sql.*;

public class BlobUpdateTest {

  public static final String BINARY_FILE_NAME="<File name here>"; //replace with any binary type file name
  
  public static void updateBLOB(ResultSet rs) {
    try {
      File file=new File(BINARY_FILE_NAME);
      FileInputStream fileIn=new FileInputStream(file);
      BufferedInputStream bufIn=new BufferedInputStream(fileIn);
      rs.updateBinaryStream("big_blob", bufIn); //<<<<<<<<<<<<<<<<< FAIL POINT
      bufIn.close();
      fileIn.close();
    }
    catch(FileNotFoundException e) {
      e.printStackTrace();
    }
    catch(IOException e) {
      e.printStackTrace();
    }
    catch(SQLException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) {
    try {
      Class.forName("com.mysql.jdbc.Driver");
    }
    catch(ClassNotFoundException e) {
      e.printStackTrace();
    }
    try {
      String url="jdbc:mysql://127.0.0.1"; //the database URL
      String username="root";              //the username
      String password="password";          //the password
      Connection connection=DriverManager.getConnection(url,username,password);
      String sql="SELECT * FROM test_me.test_table";
      PreparedStatement prepStat=connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet rs=prepStat.executeQuery();
      rs.first();
      updateBLOB(rs); //FAILS
      rs.updateRow();
      rs.close();
      prepStat.close();
      connection.close();
    }
    catch(SQLException e) {
      e.printStackTrace();
    }
  }
}

*****************************
IN ADDITION: This same problem exists when attempting to update a LONGTEXT field with the ResultSet.updateCharacterStream() method.

Suggested fix:
No suggestion
[20 Apr 2010 19:01] Nicholas Daniels
Using MySQL server 5.1
[26 Apr 2010 11:11] Tonci Grgin
Hi Nicholas and thanks for your report.

Afais, you are missing stream size parameter. Ie.
 rs.updateBinaryStream("big_blob", bufIn); //<<<<<<<<<<<<<<<<< FAIL POINT
should be
 rs.updateBinaryStream("big_blob", bufIn, "size of buff in");

There is also a BlobRegressionTest.java in our testsuite.regression dealing with exact problem. You might want to check on this and get back to me.
[26 Apr 2010 18:20] Nicholas Daniels
Hi Tonci,

Thank you for your response.

I ran the testsuite.regression.BlobRegressionTest and it passes 100%.

I also added the extra parameter as you suggested (I added file.size()).  The problem persists.

None of the routines within the testsuite.regression.BlobRegressionTest test the problem that I am experiencing: I am creating a ResultSet from a SELECT sql statement.  That same (updatable) ResultSet is then used to attempt an upload of data to the database.  All field-types work 100% except when streaming data into LONGBLOB and LONGCLOB fields.  All the tests in your test-case perform an update from a statement made with either an UPDATE or INSERT sql statement.

I hope I have made the issue clearer.
[5 May 2010 12:42] Tonci Grgin
Nicholas, it is more clear now. I'd like you to attach small but complete test case (incl. DML/DDL, BLOB data etc) so I can run it in my environment.
[5 May 2010 12:43] Tonci Grgin
Also, what is
      PreparedStatement
prepStat=connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

for? Statement is not preparable for sure...
[24 May 2010 17:59] Nicholas Daniels
Demonstrates the described bug.

Attachment: BlobUpdateTest.java (application/octet-stream, text), 2.90 KiB.

[24 May 2010 18:01] Nicholas Daniels
I have uploaded code that performs as you requested.

I changed from PreparedStatement to Statement.  Bug remains as before.
[25 May 2010 6:27] Tonci Grgin
Thanks Nicholas. I think I need to determine why regression test works and yours fail first and then we'll see.
[25 May 2010 8:32] Tonci Grgin
Test case for our test framework. Tested with JDK 1.5.

Attachment: TestBug53002.java (text/x-java), 2.46 KiB.

[25 May 2010 8:36] Tonci Grgin
Nicholas, your sample should work according to specs. What could pose a problem is max_allowed_packet value.

Attached is my code that works as expected (I created the BLOB file using classes from testsuite.BaseTestCase).
The full Eclipse URL is:
-Xmx1024M -XX:+UseParallelGC -Dcom.mysql.jdbc.java6.javac=C:\jvms\jdk1.6.0\bin\javac.exe -Dcom.mysql.jdbc.java6.rtjar=C:\jvms\jdk1.6.0\jre\lib\rt.jar -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://remote_server:xxxx/test?user=root&password=xxx&autoReconnect=false&connectTimeout=5000&socketTimeout=30000&useInformationSchema=true&useServerPrepStmts=true&useAffectedRows=false&useUnicode=true&characterSetResults=utf8&nullCatalogMeansCurrent=false

Test case output is:
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.1.31-log
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os.name                 : Windows Server 2008
os.version              : null
sun.management.compiler : HotSpot Client Compiler
com.mysql.jdbc.UpdatableResultSet@12bb7e0

Time: 1,474

OK (1 test)

So, for starters, you should check the general query log from MySQL server to see what actually happened. After that, if it still fails after common problems from log are fixed, I'd test with "&useServerPrepStmts=true".
[25 May 2010 9:03] Tonci Grgin
There's also few words on this from Mark in http://objectmix.com/jdbc-java/41414-blob-update-problem-mysql.html:
If you want to _replace_ the data, you need to call Blob.truncate() first (which we don't yet implement), as the idea behind Blob.setBinaryStream() is to write data _into_ the existing blob.
However, if you want to just outright replace a blob, why not just use ResultSet.updateBinaryStream()?

I'm not sure why you'd want to use an UpdatableResultSet to update a blob anyway, it's a _verrrry_ heavyweight way to do things and will waste a lot of resources.

-Mark
[25 Jun 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 May 2016 14:05] Filipe Silva
The reporter was calling File.length() in order to get the input stream size. As such, the ResultSet method being called was 'updateBinaryStream(int columnIndex, InputStream x, long length)' as opposed to 'updateBinaryStream(int columnIndex, InputStream x, int length)'.

The method ResultSet.updateBinaryStream(int, InputStream, long), introduced by JDBC4 specs, isn't yet implemented in Connector/J.
[22 Feb 14:56] Alexander Soklakov
Methods ResultSet.updateBinaryStream(int, InputStream) and ResultSet.updateBinaryStream(int, InputStream, long) are implemented in c/J 8.0.

This bug affects only c/J 5.1.