MySQL Bugs: #53002: Failure to update a BLOB field using an Updatable ResultSet

Bug #53002	Failure to update a BLOB field using an Updatable ResultSet
Submitted:	20 Apr 2010 18:43	Modified:	25 Feb 2022 11:02
Reporter:	Nicholas Daniels	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.12	OS:	Windows (Version 2002, Service Pack 3)
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	BLOB, CONCUR_UPDATABLE, ResultSet, updateBinaryStream()

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

Using MySQL server 5.1

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.

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.

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.

Also, what is
      PreparedStatement
prepStat=connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

for? Statement is not preparable for sure...

Demonstrates the described bug.

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

I have uploaded code that performs as you requested.

I changed from PreparedStatement to Statement.  Bug remains as before.

Thanks Nicholas. I think I need to determine why regression test works and yours fail first and then we'll see.

Test case for our test framework. Tested with JDK 1.5.

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

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".

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

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".

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.

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.

Posted by developer:
 
This bug is not reproducible with the latest Connector/J 8.0.

Connector/J 5.1 series came to EOL on Feb 9th, 2021, see https://www.mysql.com/support/eol-notice.html, so this bug will not be fixed there.