Bug #18909 Data curruption in stored procedure when using japanese characters and utf8
Submitted: 8 Apr 2006 16:06 Modified: 19 Apr 2006 7:18
Reporter: Frederik Sørensen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.21-BK, 5.0.18 OS:Linux (Linux Debian)
Assigned to: Alexander Barkov CPU Architecture:Any

[8 Apr 2006 16:06] Frederik Sørensen
Description:
When i insert japanese text into a database and try to use it within a stored procedure i get only ? characters back. That is if i try to return the data from the stored procedure or as in the example code insert it back in to the table.

If i select the data direct with a select * from table it shows correct. 

How to repeat:
CREATE DATABASE `testdb2` ;

CREATE TABLE `test` (
`dataField` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) TYPE = MYISAM ;

insert into test values(char(0xE4BC8A using utf8));

DELIMITER $$

DROP PROCEDURE IF EXISTS `testdb2`.`testProc` $$
CREATE PROCEDURE `testdb2`.`testProc` ()
BEGIN
  declare var TEXT;
  SELECT `dataField` into var from test limit 1;
  insert into test(`dataField`) values(var);
END $$

DELIMITER ;

CALL testProc();

SELECT * FROM `test`;
[10 Apr 2006 13:11] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.21-BK on Linux:

mysql> call testProc();
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'var' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `test`;
+-----------+
| dataField |
+-----------+
| д??       |
| ?         |
+-----------+
2 rows in set (0.00 sec)

This difference in data (and warning) should be explained and/or explicitely documented.
[19 Apr 2006 7:18] Alexander Barkov
This is not a bug. Variables are created using character_set_server by default.
Most likely your server is running latin1 as default character set.
You have two options:
1. Start mysqld with any character set supporting character
CHAR(0xE4BC8A USING utf8).  ucs2, utf8, sjis and ujis will do.
For example:
mysqld --default-character-set=ujis

2. Declare the  variable specifying character set explicitely
with any of the above character sets. For example:

   DECLARE var TEXT CHARACTER SET ujis;

3. Declare the variable using this way:

   DECLARE var TEXT UNICODE;

(which is a shorhand for DECLARE var TEXT CHARACTER SET ucs2)

I suggest to use either 2 or 3, not to depend on mysqld startup options.