MySQL Bugs: #113518: character set with Chinese character,using storage procedure it report 1300 err

Bug #113518	character set with Chinese character,using storage procedure it report 1300 err
Submitted:	27 Dec 2023 3:41	Modified:	3 Jan 2024 2:40
Reporter:	z yz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	8.0.32, 8.0.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
when the storage include the Chinese character, it will report the 1300 error

you can refer the below repeat procedure

How to repeat:
create storage procedure using below statement :

CREATE PROCEDURE `testproc1`(
IN p_dyn_para_code varchar(20),
OUT p_parameter_value VARCHAR(5000))
BEGIN

DECLARE p_msg_config VARCHAR(10000);
SET p_parameter_value = '';
SET @parameter_value = '';

IF p_dyn_para_code = 'A' THEN
SET p_msg_config = 'select curdate() INTO @parameter_value';
END IF;

IF p_dyn_para_code = 'B' THEN
SET p_msg_config = 'select "中文" INTO @parameter_value';
END IF;

SET p_parameter_value = IFNULL(p_msg_config, '');

SET @parameter_value = '';

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

PREPARE exec_stmt_2_0 FROM @rtn_sqlstr;
EXECUTE exec_stmt_2_0;
DEALLOCATE PREPARE exec_stmt_2_0;

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

END //

execute CALL `testproc1`('A', @parameter_value);
then execute CALL `testproc1`('B', @parameter_value);

it will report the error like below :

mysql> use cslc
Database changed
mysql> CALL `testproc1`('A', @parameter_value);
Query OK, 0 rows affected (0.00 sec)

mysql> CALL `testproc1`('B', @parameter_value);
ERROR 1300 (HY000): Invalid latin1 character string: '\xE4\xB8\xAD\xE6\x96\x87'
mysql>

but all the character set is utf8mb4, not using the latin

mysql> \s
--------------
./mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          10
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.32 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /tmp/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 7 min 7 sec

Threads: 2  Questions: 24  Slow queries: 0  Opens: 155  Flush tables: 3  Open tables: 71  Queries per second avg: 0.056
--------------

Hello,

Thank you for the bug report.
Could you please try adding character set to the stored procedure as below

CREATE PROCEDURE `testproc1`(IN p_dyn_para_code varchar(20) character set gb2312, OUT p_parameter_value VARCHAR(5000))

For more info about MySQL Chinese character set, please see https://dev.mysql.com/doc/refman/8.0/en/faqs-cjk.html

Regards,
Ashwini Patil

hi

i have add the character set when create procedure

i re-create the procedure like :
mysql> delimiter //
mysql> CREATE PROCEDURE `testproc1`(
    -> IN p_dyn_para_code varchar(20) character set gb2312,
    -> OUT p_parameter_value VARCHAR(5000))
    -> BEGIN
    ->
    -> DECLARE p_msg_config VARCHAR(10000);
    -> SET p_parameter_value = '';

but it also meet the error :
mysql> CALL `testproc1`('A', @parameter_value);
Query OK, 0 rows affected (0.00 sec)

mysql> CALL `testproc1`('B', @parameter_value);
ERROR 1300 (HY000): Invalid latin1 character string: '\xE4\xB8\xAD\xE6\x96\x87'
mysql>

Hello!

Thank you for the details.
I tried to reproduce your issue using given procedure with char set gb2312 added to the IN parameter, but I am not seeing any issues at my end. 

Try to add char set that is appropriate to your system from the link below
https://dev.mysql.com/doc/refman/8.0/en/faqs-cjk.html 

Let us know if you are still facing the issue. Thanks.

Regards,
Ashwini Patil

hello Ashwini Patil

are you using the latin1 character set ?

if using the default utf8 character set and using mysql 8.0.32 and above version, this issue can reproduce

Hello!

are you using the latin1 character set ?
--> No, I used gb2312 and other Chinese char set that are mentioned in https://dev.mysql.com/doc/refman/8.0/en/faqs-cjk.html 
Also, tried with utf8mb4, not seeing any issues at my end.

`testproc1`(IN p_dyn_para_code varchar(20) character set utf8mb4, OUT p_parameter_value VARCHAR(5000))

Regards,
Ashwini Patil

Bug #113536 marked as duplicate of this one.