Bug #113536 A character set error is reported when a stored procedure is used.

• Submitted: 2 Jan 2024 11:55
• Modified: 3 Jan 2024 12:41
• Reporter: Chi Zhang
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.35
• OS: Any
• CPU Architecture: Any
• Tags: character sets

[2 Jan 2024 11:55] Chi Zhang

Description:
A stored procedure is called repeatedly, and a character set error is reported.

How to repeat:
1.Create a stored procedure.
use test;
set names utf8mb4;
delimiter //
CREATE PROCEDURE `proc`(
IN selec_code varchar(20), 
OUT p_parameter_value VARCHAR(5000))
BEGIN
DECLARE msg_config TEXT(20000);
DECLARE rtn_sqlstr TEXT(20000);
SET @parameter_value = '';

IF selec_code = 'A' THEN
    SET msg_config =  "select curdate() INTO @parameter_value";
END IF;
IF selec_code = 'B' THEN
    SET msg_config = "select '中国时间' INTO @parameter_value";
END IF;

SET @rtn_sqlstr = IFNULL(msg_config, '');

PREPARE stmt FROM @rtn_sqlstr; 
  EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET p_parameter_value = cast(@parameter_value AS CHAR(5000));

END //
delimiter ;

2.Call this stored procedure.
mysql> CALL `proc`('A', @result);
Query OK, 0 rows affected (0.00 sec)

# An error occurs !!!
mysql> CALL `proc`('B', @result);
ERROR 1300 (HY000): Invalid latin1 character string: '\xE4\xB8\xAD\xE5\x9B\xBD...'

mysql> drop procedure `proc`;
Query OK, 0 rows affected (0.02 sec)

[3 Jan 2024 1:15] Chi Zhang

If we swap the order in which the stored procedures are called in step 2, no error will be reported.

mysql> CALL `proc`('B', @result);
Query OK, 0 rows affected (0.01 sec)

mysql> CALL `proc`('A', @result);
Query OK, 0 rows affected (0.00 sec)

[3 Jan 2024 12:41] MySQL Verification Team

Hello Chi Zhang,

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

Regards,
Ashwini Patil