Bug #114833 Issue with JSON via JDBC
Submitted: 1 May 2024 9:38 Modified: 10 Jul 2024 13:27
Reporter: Allen Kawanzaruwa Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[1 May 2024 9:38] Allen Kawanzaruwa
Description:
# Issue with JSON via JDBC

I have a table that I want to store json

```sql
CREATE TABLE `schemas_dev`.`article` (`id` BIGINT AUTO_INCREMENT NOT NULL, `content` JSON NULL, CONSTRAINT `PK_ARTICLE` PRIMARY KEY (`id`));
```

Then running the following causes the following. 

```java
try(
                InputStream JSON = new ClassPathResource("data.json").getInputStream();
                Connection connection = datasource.getConnection();
            PreparedStatement statement = connection.prepareStatement("insert into article(content) values(?)", Statement.RETURN_GENERATED_KEYS )) {
            statement.setObject(1, JSON);
            statement.executeUpdate();

            try (ResultSet keys = statement.getGeneratedKeys()) {
                while (keys.next()) {
                    System.out.println(keys.getLong(1));
                }
            }
        }
        catch (SQLException e) {
            throw new IllegalArgumentException(e);
        }
```

I get the following error

```
: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
	at JDBCJSON.main(JDBCJSON.java:86)
Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
```

However using column type `LONGTEXT` or `LONGBLOB` doesn't raise this error. 

Please allow `JSON` to accept `InputStream` via `setObject`  to allow large json objects in prepared statements?

How to repeat:
import com.mysql.cj.MysqlType;
import com.mysql.cj.jdbc.MysqlDataSource;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;

public class JSONJDBCSetParameter {
    public static void main(String[] args) {

        final MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setPassword("");
        dataSource.setUser("root");
        dataSource.setUrl("jdbc:mysql://localhost:3306/db");

        try(final Connection connection = dataSource.getConnection();
            final Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS `article`;");
            statement.execute("CREATE TABLE `article` (`id` BIGINT AUTO_INCREMENT NOT NULL, `content` JSON NULL, CONSTRAINT `PK_ARTICLE` PRIMARY KEY (`id`));");

        }
        catch(SQLException e) {
            throw new IllegalArgumentException(e);
        }

        try(
                final InputStream inputStream = JSONJDBCSetParameter.class.getResourceAsStream("/dbzium.json");
                final Connection connection = dataSource.getConnection();
                final PreparedStatement statement = connection.prepareStatement("INSERT INTO `article`( `content`) VALUES (?)", Statement.RETURN_GENERATED_KEYS )) {
            statement.setObject(1, inputStream, MysqlType.JSON);
            statement.executeUpdate();

            try (ResultSet keys = statement.getGeneratedKeys()) {
                while (keys.next()) {
                    System.out.println(keys.getLong(1));
                }
            }
        }
        catch(SQLException | IOException e) {
            throw new IllegalArgumentException(e);
        }
    }
}
[10 Jul 2024 13:27] MySQL Verification Team
Hello Allen Kawanzaruwa,

Thank you for the bug report.
Imho this is duplicate of Bug #114524, please see Bug #114524.

Regards,
Ashwini Patil