Bug #103303 java.lang.ClassCastException when inserting blob with server prepared statement

• Submitted: 13 Apr 2021 12:53
• Modified: 10 Jun 2021 21:43
• Reporter: Andrey Tarashevskiy
• Status: Closed
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 8.0.23
• OS: Any
• CPU Architecture: Any

[13 Apr 2021 12:53] Andrey Tarashevskiy

Description:
It's impossible to set blobs when used connected with `useServerPrepStmts=true&rewriteBatchedStatements=true` parameters as it fails with ClassCastException:
```
java.lang.ClassCastException: com.mysql.cj.jdbc.Blob cannot be cast to java.io.Reader
at com.mysql.cj.jdbc.ServerPreparedStatement.setOneBatchedParameterSet(ServerPreparedStatement.java:729)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:712)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:426)
at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:794)
```

How to repeat:
1. Connect to mysql with `useServerPrepStmts=true&rewriteBatchedStatements=true` in connection url

2. Create simple table with: 
``` CREATE TABLE t1 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  blob1 MEDIUMBLOB
)
```

3. Execute provided code
```
public class BlobTest {

    public void testBlob(Connection connection) throws SQLException {
        PreparedStatement statement = connection.prepareStatement("INSERT INTO t1(blob1) VALUES(?);", PreparedStatement.RETURN_GENERATED_KEYS);
        Blob blob = new SerialBlob("test".getBytes());
        statement.setBlob(1, blob);
        statement.addBatch();
        statement.setBlob(1, blob);
        statement.executeBatch();
    }
}
```

[14 Apr 2021 5:15] Alexander Soklakov

Hi Andrey,

Thanks for the report!

[10 Jun 2021 21:43] Daniel So

Posted by developer:
 
Added the following entry to the Connector/J 8.0.26 changelog: 

"Inserting a BLOB into a table using a server-side PreparedStatement resulted in a ClassCastException."

[28 Mar 2023 11:09] Stefan Heisl

This is a bug report related to an issue encountered when trying to set blobs with useServerPrepStmts=true&rewriteBatchedStatements=true parameters. The bug results in a ClassCastException when executing a batch insert statement. The bug has been acknowledged and resolved in the Connector/J 8.0.26 version, as stated in the changelog. More information about this error: https://andersenlab.com/industries/logistics