Bug #117808 Unexpected JSON column DML error with prepare statement

• Submitted: 27 Mar 7:21
• Modified: 27 Mar 10:00
• Reporter: Xiaocong Ding (OCA)
• Status: Verified
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• Version: 8.0.41, 8.4.2
• OS: Any
• CPU Architecture: Any
• Tags: JSON ;CHARACTER SET;

[27 Mar 7:21] Xiaocong Ding

Description:
When using a prepared statement to insert/update a JSON column, an unexpected error occurs if the variable type changes between executions. The second execution with a valid JSON string variable fails with ERROR 3144 (22032), even though the session character set is explicitly set to utf8mb4.

How to repeat:
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;

Suggested fix:
Each time update/insert a JSON column via a prepare statement, it's necessary to ensure that the Item->m_data_type is correct

[27 Mar 10:01] MySQL Verification Team

Hello Xiaocong Ding,

Thank you for the report and feedback.

regards,
Umesh