Bug #60300 | Corrupted values using variable of type TEXT in trigger or stored procedure | ||
---|---|---|---|
Submitted: | 2 Mar 2011 1:30 | Modified: | 3 Mar 2011 9:03 |
Reporter: | Roland Volkmann | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
Version: | 5.5.9 | OS: | Windows (XP Prof.) |
Assigned to: | CPU Architecture: | Any | |
Tags: | stored procedure, text, trigger, variable |
[2 Mar 2011 1:30]
Roland Volkmann
[2 Mar 2011 2:08]
MySQL Verification Team
Thank you for the bug report. C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.11 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.5 >use test Database changed mysql 5.5 >create table t1 (id integer, info text); Query OK, 0 rows affected (0.10 sec) mysql 5.5 >delimiter // mysql 5.5 >create trigger tr_t1_bi before insert on t1 for each row begin -> declare $row text; -> set $row := 'Hello World'; -> set new.info := $row; -> end;// Query OK, 0 rows affected (0.10 sec) mysql 5.5 >delimiter ; mysql 5.5 >insert into t1 (id) values (1); Query OK, 1 row affected (0.05 sec) mysql 5.5 >select * from t1; +------+-------------+ | id | info | +------+-------------+ | 1 | . llo World | +------+-------------+ 1 row in set (0.00 sec) mysql 5.5 >
[3 Mar 2011 9:03]
Roland Volkmann
You get the same error using stored procedures instead of triggers: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.5.9 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> create table t2 (id integer, info text); Query OK, 0 rows affected (0.05 sec) mysql> delimiter // mysql> create procedure p_test (in ipar integer) -> begin -> declare $row text; -> set $row := 'Hello World'; -> insert into t2 (id, info) values (ipar, $row); -> end;// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call p_test(2); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +------+-------------+ | id | info | +------+-------------+ | 2 | êz♠Fo World | +------+-------------+ 1 row in set (0.00 sec) mysql>