Bug #101242 Cannot use ByteArrayInputStream as arguments in prepared statements anymore
Submitted: 20 Oct 2020 11:56 Modified: 12 Jan 2021 21:55
Reporter: Martynas Puronas Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[20 Oct 2020 11:56] Martynas Puronas
Description:
As of 8.0.22 it is no longer possible to call setObject method of PreparedStatement and pass ByteArrayInputStream, because the driver now throws java.sql.SQLException exception ("Invalid argument value: java.io.NotSerializableException"), whereas in the 8.0.21 driver setObject accepted ByteArrayInputStream argument.

This seems like a regression which I think was introduced in https://github.com/mysql/mysql-connector-j/commit/6f8287b2a393ef9b67889d2bb2385ef863a7e8df as that commit refactored the setObject method of AbstractQueryBindings class.

How to repeat:
This code works when using 8.0.21 version of the driver, but throws an exception with 8.0.22.

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Main {
    public static void main(String[] args) throws Exception {
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost/?user=root&password=my-secret-pw"
        );

        connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
        connection.createStatement().execute("USE test");
        connection.createStatement().execute("CREATE TABLE IF NOT EXISTS my_table (my_column blob NOT NULL)");
        connection.createStatement().execute("DELETE FROM my_table");

        PreparedStatement statement = connection.prepareStatement("INSERT INTO my_table(my_column) VALUES (?)");
        statement.setObject(1, new ByteArrayInputStream("test".getBytes()));
        statement.execute();

        ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM my_table");

        rs.next();

        System.out.println("Retrieved from database: " + new String(rs.getBytes("my_column")));
    }
}
[20 Oct 2020 12:06] MySQL Verification Team
Hello Martynas,

Thank you for the report and feedback.

regards,
Umesh
[27 Nov 2020 21:34] Greg Brown
I just ran into this myself with a FileInputStream. My code works with 8.0.15 but fails in 8.0.22 because of this issue.

The ticket is currently ranked as "non-critical"; however, I would consider it a higher priority since it will prevent anyone who uses BLOBs from upgrading to 8.0.22+.
[27 Nov 2020 21:39] Greg Brown
Specifically, the code fails on (what is currently) line 230:

MysqlType defaultMysqlType = DEFAULT_MYSQL_TYPES.get(parameterObj.getClass());

The previous version used instanceof instead of getClass(). As currently written, this code will always fail - since InputStream is abstract, the value returned by getClass() for parameterObj can never actually be InputStream and will never produce a valid lookup from the type map.
[12 Jan 2021 21:55] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.23 changelog: 

"Using the setObject() method to set a ByteArrayInputStream instance for a PreparedStatement resulted in a SQLException. "