MySQL Bugs: #114524: INSERT with JSON column error

Bug #114524	INSERT with JSON column error
Submitted:	1 Apr 2024 12:36	Modified:	2 Apr 2024 9:17
Reporter:	Evgeniy Devyatykh	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.3.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL Connector/J 8.3.0
MySQL Server 8.0.36
useServerPrepStmts=true
cachePrepStmts=true

INSERT row with JSON column fails with exception

Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'

I have investigated this issue and found next:
to reproduce we need useServerPrepStmts=true&cachePrepStmts=true
first INSERT with NULL value for JSON column + any other non-string parameter(!) -> second INSERT with server prepared statement from cache will fail.

On the server 8.0.35 buf is not reproduced.

How to repeat:
String url = "jdbc:mysql://host/test?useServerPrepStmts=true&cachePrepStmts=true&user=&password=";
        try (Connection connection = DriverManager.getConnection(url)) {
            connection.createStatement().execute("DROP TABLE IF EXISTS bugtest");
            connection.createStatement().execute("CREATE TABLE bugtest (id bigint auto_increment primary key, s varchar(255), j JSON NULL) ");
            String sql = "INSERT INTO bugtest(j, s) VALUES(?, ?)";
            PreparedStatement ps = connection.prepareStatement(sql);

            ps.setNull(1, Types.NULL);
            ps.setInt(2, 0); //second INSERT will fail with exception Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
            //ps.setString(2, "0"); // but this works!
            ps.execute();
            ps.close();
            ps = connection.prepareStatement(sql);
            ps.setString(1, "{}");
            ps.setInt(2, 0);
            ps.execute(); //fails
            ps.close();
        }

Hello Evgeniy Devyatykh,

Thank you for the report and feedback.

regards,
Umesh

I forget to say that this issue reproduced for Connector/J 8.2.0, 8.1.0, 8.0.33 and for server 8.0.36 (but not for 8.0.35) that is why I have choose "Server" category but not "Connector/J".

Bug #114833 marked as duplicate of this one.

Adding some additional context around this bug, I was also seeing the above error for the use-case mentioned above, but I was using the node packages sequelize & mysql2 to connect to our mysql8 database. 

*Details:* 
MySQL Server - 8.0.36
sequelize - 6.4.0
mysql2 - 3.10.1

Additionally, I found another use-case to reproduce this issue:

1. Insert a new row into a table with a JSON column.
2. Update the JSON column to a null value.
3. Update a JSON column to a JSON value. This second update will fail.

*Code to Reproduce:*

const sequelize = new Sequelize('test', 'username', 'pw', { host: 'host', dialect: 'mysql' });

const BugTest = sequelize.define('bugtest', { 
  s: { type: Sequelize.STRING },
  j: { type: Sequelize.JSON }
});

const newRow = await BugTest.create({
        s: '1',
        j: {}
      });

      await BugTest.update({
        j: null
      }, { where: { id: newRow.id } });

      // this 2nd update will fail
      await BugTest.update({
        j: {}
      }, { where: { id: newRow.id } });

Thanks for updating this bug Morgan Cook.

We also use sequelize for this, here's our package versions using Node:

*Details:* 
MySQL Server - 8.0.36
sequelize - 6.35.1
mysql2 - 3.11.3

This bug looks to pop up when trying to update an entry that has its JSON column set to 'null' to a non-null JSON object. This is even simpler than Morgan's case:

Additionally, I found another use-case to reproduce this issue:

1. Insert a new row into a table with a JSON column. The JSON column is set to NULL.
2. Update the JSON column to a non-null value.

Posting here on this issue, but it looks like this case actually works when posting raw SQL.

This error is also triggered when you insert a json string in binary format into a json column, e.g.
This insert produces the error:
INSERT INTO `mydb`.`mytbl` (`id`,`MyJson`)
VALUES
(1, x'7b22726f77223a207b2274797065223a202250222c202276616c7565223a20357d7d');
This insert runs:
INSERT INTO `mydb`.`mytbl` (`id`,`MyJson`)
VALUES
(1, '{"row": {"type": "P", "value": 5}}');

Hello,
this bug also affects me.

Database version: 8.0.36

As a workaround, I had to disable these parameters from my JDBC connection string:

cachePrepStmts=true
useServerPrepStmts=true
prepStmtCacheSize=256
prepStmtCacheSqlLimit=2048
reWriteBatchedInserts=true

It looks like this issue is fixed by upgrading to MySQL 8.0.39.

8.0.41 and 8.4.2 still have similar problem. It can be repeated by followsing SQL:

set names utf8mb4;
create table t1 (id int auto_increment primary key, c1 varchar(255), c2 json null);
prepare it from 'insert into t1(c2, c1) values(?, ?)';
set @a=0;
set @b=2;
execute it using @b, @a;
set @b='{}';

# Unexpectedly fails with ERROR 3144
execute it using @b, @a;

If any, this is an issue within the MySQL Server, not Connector/J.

Re-classified as MySQL Server: Optimizer.