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:
None 
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
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"
[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>