| Bug #13139 | Procedure created in UTF-8 get data to long error | ||
|---|---|---|---|
| Submitted: | 13 Sep 2005 13:05 | Modified: | 13 Sep 2005 14:49 |
| Reporter: | Anders Karlsson | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.0.12 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[13 Sep 2005 13:09]
Anders Karlsson
For the problem to occur, one needs, as in the example I present, some UTF-8 data in the table. In this case an unlaut-o (UNICODE 00F6).
[13 Sep 2005 14:49]
Valeriy Kravchuk
I tried to repeat on today's 5.0.13-beta-BK build, and was unable: [openxs@Fedora 5.0]$ bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 5.0.13-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> call testtmp(); Query OK, 1 row affected (0,00 sec) mysql> status -------------- bin/mysql Ver 14.12 Distrib 5.0.13-beta, for redhat-linux-gnu (i686) using Edi tLine wrapper Connection id: 8 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.13-beta-debug Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 1 min 42 sec Threads: 1 Questions: 242 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 21 Queries per second avg: 0.065 -------------- mysql> call testtmp(); Query OK, 1 row affected (0,00 sec) mysql> call testtmp(); Query OK, 1 row affected (0,00 sec) mysql> select * from tab1; +------+ | col1 | +------+ | | | ö | +------+ 2 rows in set (0,00 sec) mysql> select version(); +-------------------+ | version() | +-------------------+ | 5.0.13-beta-debug | +-------------------+ 1 row in set (0,00 sec) So, I have your 'ö' in the table, and no set names were executed, but the procedure created just as you described. It even inserts (truncated) rows into tab2 upon each call. I think you should wait for 5.0.13 official release, and try it once more.
Description: When creating a procedure in using UTF-8, and working solely with UTF-8 data, then when referencing a procedure local variable gets a "data too long"-error, even when this is not the case when the procedure is called in a non-UTF8 session. How to repeat: Create the objects with this script: SET NAMES utf8; DROP TABLE IF EXISTS tab1; CREATE TABLE tab1(col1 VARCHAR(20)) DEFAULT CHARSET utf8; DROP TABLE IF EXISTS tab2; CREATE TABLE tab2(col1 VARCHAR(20)) DEFAULT CHARSET utf8; -- INSERT INTO tab1 VALUES('Förening'); INSERT INTO tab1 VALUES('ö'); DROP PROCEDURE IF EXISTS testtmp; delimiter // CREATE PROCEDURE testtmp() BEGIN DECLARE v_col1 VARCHAR(20); DECLARE cur1 CURSOR FOR SELECT * FROM tab1; OPEN cur1; FETCH cur1 INTO v_col1; INSERT INTO tab2 VALUES(v_col1); CLOSE cur1; END // delimiter ; Then from mysql commandprompt (note that a logout is from mysql is needed, as the script sets UTF-8, and with this set, there are no errors). $ mysql test Reading 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 44 to server version: 5.0.12-beta Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> call testtmp(); ERROR 1406 (22001): Data too long for column 'col1' at row 1 mysql> status -------------- mysql Ver 14.12 Distrib 5.0.12-beta, for pc-linux-gnu (i686) using readline 5.0 Connection id: 44 Current database: test Current user: karlsson@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.12-beta Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 47 min 50 sec Threads: 1 Questions: 2704 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 205 Queries per second avg: 0.418 -------------- mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> call testtmp(); Query OK, 1 row affected (0.00 sec) mysql> status -------------- mysql Ver 14.12 Distrib 5.0.12-beta, for pc-linux-gnu (i686) using readline 5.0 Connection id: 44 Current database: test Current user: karlsson@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.12-beta Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 48 min 4 sec Threads: 1 Questions: 2710 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 205 Queries per second avg: 0.418 -------------- mysql> It might well be that calling a UTF8 procedure from a non-UTF8 session is not allowed, although this would be a strage restriction. If that is the case, then the error returned is incorrect. The above is a database with default-charset=utf8. The client default-charset is still latin1 (as can be seen above).