| 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 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>
Description: When inserting data to a column of type TEXT using a before-insert-trigger, the data is corrupted if the value is prepared in a variable of type TEXT. The test case below is reduced code to the absolute minimum to show the bug. How to repeat: 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 t1 (id integer, info text); Query OK, 0 rows affected (0.05 sec) mysql> delimiter // mysql> 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.05 sec) mysql> delimiter ; mysql> insert into t1 (id) values (1); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+-------------+ | id | info | +------+-------------+ | 1 | p+‼Fo World | +------+-------------+ 1 row in set (0.00 sec) mysql> Here some settings in mysql.ini which may be relevant: [mysqld] character-set-server=latin1 collation-server=latin1_german2_ci lc_time_names="de_DE" default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI"