| Bug #17872 | Ascii code 228 causes "data too long" error with utf8 and stored procedure. | ||
|---|---|---|---|
| Submitted: | 2 Mar 2006 19:00 | Modified: | 29 Apr 2009 17:44 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.0.19, 5.0.18 | OS: | Windows (Windows) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | stored procedure | ||
[3 Mar 2006 9:40]
[ name withheld ]
I've just found a few open bug reports (with v 5.x) with people reporting similar problems with japanese chars and more german umlauts. I think my test case is simplier and hopefully easier to trace in the code. It does not happen after SET NAMES cp1252; or other charset. Only breaks with utf8.
[7 Mar 2006 8:20]
Hartmut Holzgraefe
can't reproduce this on linux, seems to be a windows only problem? i tried both latin and utf encoding of 'ä' in the PHP source but both times the procedure call worked without errors
[7 Mar 2006 11:04]
[ name withheld ]
PHP and MySQL server info...
Attachment: info.txt (text/plain), 16.18 KiB.
[7 Mar 2006 11:07]
[ name withheld ]
I've attached info about php and mysql... Please assist. The bug is holding my app developement, as I can't substitute utf8 support at app level forever.
[8 Mar 2006 8:53]
[ name withheld ]
What a nice button up there ;-) Find similar bugs... I recomend viewing, probably related: http://bugs.mysql.com/bug.php?id=16676 http://bugs.mysql.com/bug.php?id=13139 http://bugs.mysql.com/bug.php?id=16209
[14 Mar 2006 11:05]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.19 on Windows XP SP2: mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> CALL new_proc (x'99'); ERROR 1406 (22001): Data too long for column 'param' at row 1 mysql> CALL new_proc (x'80'); ERROR 1406 (22001): Data too long for column 'param' at row 1 mysql> CALL new_proc (x'7F'); +------+ | zoop | +------+ | zoop | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'char%'; +--------------------------+---------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ | +--------------------------+---------------------------------------------------------+ 8 rows in set (0.02 sec)
[12 Apr 2006 18:02]
[ name withheld ]
Mysql is more unstable with utf8 than I've anticipated.
Now also breaks with other chars ... like with inserting the following string (properly escaped, doesn't break with latin2).
array (
'decimal_point' => ',',
'thousands_sep' => '',
'int_curr_symbol' => 'PLN ',
'currency_symbol' => 'Zł',
'mon_decimal_point' => ',',
'mon_thousands_sep' => '.',
'positive_sign' => '',
'negative_sign' => '-',
'int_frac_digits' => 2,
'frac_digits' => 2,
'p_cs_precedes' => 1,
'p_sep_by_space' => 0,
'n_cs_precedes' => 1,
'n_sep_by_space' => 0,
'p_sign_posn' => 1,
'n_sign_posn' => 1,
'grouping' =>
array (
),
'mon_grouping' =>
array (
0 => 3,
1 => 3,
),
)
[18 Aug 2006 9:06]
Mikael FS
hello, this bug still appears on the recent 5.0.22. my test environment is windows xp running sp2. i tried both utf8 and latin1 swedish collation and still got the error 1406
[29 Nov 2006 16:57]
Holy Deer
This problem comes from UTF-8 encoding = for some languages and special symbols the system uses 2 chars instead of 1. Therefore if you have this error, check the settings of Columns and if, for example, you want to store 3 russian letters, be sure to provide at least VARCHAR(6)
[10 Apr 2009 7:18]
rancpine cui
I test on mysql 5.1.32, no more errors, but warnings instead :-P
mysql> delimiter |
mysql> CREATE PROCEDURE `new_proc`(param TINYTEXT)
-> NOT DETERMINISTIC
-> SQL SECURITY INVOKER
-> BEGIN
-> SELECT 'zoop';
-> END|
delimiter ;
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;
mysql> CALL new_proc (x'99');
+------+
| zoop |
+------+
| zoop |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\x99' for column 'param' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> CALL new_proc (x'7F');
+------+
| zoop |
+------+
| zoop |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
[29 Apr 2009 17:44]
Valeriy Kravchuk
In current versions, indeed, there is no problem with utf8. Look:
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot --host=127.0.0.1 testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.34-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'char%';
+--------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /Users/openxs/dbs/5.1/share/mysql/charsets/ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)
mysql> create database utf2;
Query OK, 1 row affected (0.00 sec)
mysql> use utf2;
Database changed
mysql> show variables like 'char%';
+--------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /Users/openxs/dbs/5.1/share/mysql/charsets/ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE `new_proc`(param TINYTEXT) NOT DETERMINISTIC SQL SECURITY INVOKER BEGIN SELECT 'zoop'; END;//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
Invalid utf8 characters (octets) are not accepted with warning:
mysql> CALL new_proc (x'99');
+------+
| zoop |
+------+
| zoop |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1366
Message: Incorrect string value: '\x99' for column 'param' at row 1
1 row in set (0.00 sec)
While valid are accepted:
mysql> CALL new_proc ("1.8-5V längs/longitudinal");
+------+
| zoop |
+------+
| zoop |
+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> CALL new_proc (x'7F');
+------+
| zoop |
+------+
| zoop |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
See http://www.cl.cam.ac.uk/~mgk25/ucs/ISO-10646-UTF-8.html, for example, for reference.
Description: When database is set to use utf8, calling a procedure with tinytext parameter containing ascii code 228 causes error 'data too long'. How to repeat: Database set to utf8, default charset utf8 Stored procedure: CREATE PROCEDURE `new_proc`(param TINYTEXT) NOT DETERMINISTIC SQL SECURITY INVOKER BEGIN SELECT 'zoop'; END; PHP 5 code with client library 5.0.18 <? $c = new mysqli('127.0.0.1','root','','test'); $c -> query('SET NAMES utf8'); $c -> multi_query('CALL new_proc ("1.8-5V längs/longitudinal")'); echo $c->sqlstate.'-'.$c->error; ?> Result: 001-ta too long for column 'param' at row 1 Suggested fix: No idea.