MySQL Bugs: #113536: A character set error is reported when a stored procedure is used.

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	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.35	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	character sets

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)

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)

Hello Chi Zhang,

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

Regards,
Ashwini Patil

Follow-up to Bug #113518 (Duplicate #113536)
Affected Versions: MySQL 8.0.32, 8.0.35 (verified); 8.0.x ≥8.0.28 likely affected
OS: All
Request: Reopen - Issue is 100% reproducible in pure utf8mb4 environment (kernel charset defect, not user config).
1. Core Phenomenon
Full utf8mb4 config (db/connection/table) → stored procedure with dynamic SQL throws 1300 Invalid latin1 character string only in specific call order:
Error: CALL proc('NO_MB', @res) → CALL proc('WITH_MB', @res) (UTF8 multi-byte chars)
No error: Reverse call order (WITH_MB first → NO_MB)
2. Minimal Reproducible Test Case
sql
SET NAMES utf8mb4;
DELIMITER //
CREATE PROCEDURE `charset_err_demo`(IN p_branch VARCHAR(20), OUT p_res VARCHAR(5000))
BEGIN
  DECLARE v_sql VARCHAR(20000); SET @inner_var = '';
  IF p_branch = 'NO_MB' THEN SET v_sql = "SELECT CURDATE() INTO @inner_var"; END IF;
  IF p_branch = 'WITH_MB' THEN SET v_sql = "SELECT '中文' INTO @inner_var"; END IF;
  PREPARE stmt FROM IFNULL(v_sql, ''); EXECUTE stmt; DEALLOCATE PREPARE stmt;
  SET p_res = CAST(@inner_var AS CHAR(5000));
END //
DELIMITER ;

-- Trigger 1300 error
CALL `charset_err_demo`('NO_MB', @res);
CALL `charset_err_demo`('WITH_MB', @res);

-- Cleanup
DROP PROCEDURE IF EXISTS `charset_err_demo`;
3. Root Cause (Kernel Defect)
Session user-defined variables (@inner_var) are hardcoded initialized to latin1 when first used in dynamic SQL with no multi-byte chars—even if session default is utf8mb4. This latin1 attribute persists in the session; subsequent multi-byte dynamic SQL attempts to parse UTF8 bytes with latin1, triggering the error.
4. Key Verification
Reproduced in unmodified open-source MySQL (no vendor customizations).
No latin1 config anywhere—all charset variables set to utf8mb4.
5. Fix Recommendations (Kernel-Level)
Inherit session default charset (utf8mb4) for user-defined variables (not hardcoded latin1).
Reset variable charset context between stored procedure/dynamic SQL executions.
Explicitly attach session charset to dynamic SQL preparation/execution.
6. Temporary Mitigation (User-Side)
Explicitly specify utf8mb4 in dynamic SQL:
SELECT CAST(CURDATE() AS CHAR CHARACTER SET utf8mb4) INTO @inner_var
This is intrusive and only mitigates—not a permanent fix.
7. Core Request
Re-verify with the provided test case (follow call order), reclassify to Verified, and implement kernel-level fixes for UTF8 multi-byte language compatibility.

Thanks