| Bug #60178 | Corruption of text variable inside trigger | ||
|---|---|---|---|
| Submitted: | 19 Feb 2011 9:58 | Modified: | 17 Jul 2013 19:57 |
| Reporter: | Maxim Fainberg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.5.9 | OS: | Windows (Windows 7) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | trigger | ||
[19 Feb 2011 12:15]
Valeriy Kravchuk
I do not see any problem like this with current mysql-5.5 from bzr:
macbook-pro:5.5 openxs$ bin/mysql -uroot 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 1
Server version: 5.5.11-debug 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> delimiter $
mysql> drop table if exists a1; $
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table a1(value text COLLATE utf8_unicode_ci); $
Query OK, 0 rows affected (0.08 sec)
mysql> insert into a1 values('12345'); $
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> drop trigger if exists a1_before_update; $
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create trigger a1_before_update before update on a1 for each row
-> begin
-> declare _value text;
-> set _value = '1234567';
-> set new.value = _value;
-> end;$
Query OK, 0 rows affected (0.29 sec)
mysql>
mysql> update a1 set value = '';$
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from a1;$
+---------+
| value |
+---------+
| 1234567 |
+---------+
1 row in set (0.00 sec)
mysql> show variables like 'char%';
-> $
+--------------------------+---------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /Users/openxs/dbs/5.5/share/charsets/ |
+--------------------------+---------------------------------------+
8 rows in set (0.00 sec)
Please, check what is different in your environment. I am interested in the output of SELECT you get and in the results of last SHOW command in your case.
[19 Feb 2011 14:31]
Maxim Fainberg
Result of last query: +---------+ | value | +---------+ | ▬ 34567 | +---------+ 1 row in set (0.00 sec) 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 | D:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ | +--------------------------+---------------------------------------------------------+ 8 rows in set (0.00 sec)
[19 Feb 2011 15:23]
Peter Laursen
Issue is not reproducible for me on Win7/64 with mySQL 5.5.9 either. I have same charset settings as Maxim
mysql> delimiter ;
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.5\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)
and get
mysql> select * from a1;$
+---------+
| value |
+---------+
| 1234567 |
+---------+
1 row in set (0.00 sec)
Peter
(not a MySQL person)
[19 Feb 2011 15:54]
Maxim Fainberg
I have Windows 7 - 32 bit. Issue is consistent for me. I do not have such problem with mysql 5.1.49 installed on the same host.
[21 Feb 2011 9:13]
Valeriy Kravchuk
I can not repeat this with 5.5.8 on 32-bit Windows XP.
[21 Feb 2011 9:16]
Valeriy Kravchuk
I can not repeat with 5.5.9 on 64-bit XP:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
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> delimiter $
mysql> drop table if exists a1; $
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> create table a1(value text COLLATE utf8_unicode_ci); $
Query OK, 0 rows affected (0.23 sec)
mysql> insert into a1 values('12345'); $
Query OK, 1 row affected (0.11 sec)
mysql>
mysql> drop trigger if exists a1_before_update; $
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> create trigger a1_before_update before update on a1 for each row
-> begin
-> declare _value text;
-> set _value = '1234567';
-> set new.value = _value;
-> end;$
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> update a1 set value = '';$
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from a1;$
+---------+
| value |
+---------+
| 1234567 |
+---------+
1 row in set (0.03 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.5\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.02 sec)
[21 Feb 2011 10:41]
Maxim Fainberg
Script to reproduce the bug
Attachment: bug.sql (application/octet-stream, text), 396 bytes.
[21 Feb 2011 10:42]
Maxim Fainberg
Valeriy, we still have different versions of mysql and OS. Please let me know if can supply some additional information or do more tests.
[21 Feb 2011 11:27]
Valeriy Kravchuk
I've checked with 5.5.9 in the last comment, so MySQL version was the same. As for Windows 7, I don't have it. Let's wait for somebody else to check.
[22 Feb 2011 22:30]
MySQL Verification Team
I couldn't repeat on Vista 64-bit with source server.
[25 Feb 2011 22:46]
Sveta Smirnova
Thank you for the feedback. Can you repeat the problem if use text file, not mysql command line client? I.e. mysql.exe dbname <bug.sql
[26 Mar 2011 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[2 Jul 2013 16:20]
MySQL Verification Team
This looks fixed in 5.5.13 Assignments to NEW.var_name within triggers, where var_name had a BLOB or TEXT type, were not properly handled and produced incorrect results. (Bug #12362125)
[17 Jul 2013 19:57]
MySQL Verification Team
Closing as fixed Noted in 5.5.13, 5.6.3 changelogs. Assignments to NEW.var_name within triggers, where var_name had a BLOB or TEXT type, were not properly handled and produced incorrect results.
Description: Variable of text datatype are corrupted inside trigger. I just set local variable set _value = '1234567' and then change new.value After that value is not equal '1234567'. Second symbol is zero byte. I can avoid the bug this way: set new.value = binary(_value); How to repeat: delimiter $ drop table if exists a1; $ create table a1(value text COLLATE utf8_unicode_ci); $ insert into a1 values('12345'); $ drop trigger if exists a1_before_update; $ create trigger a1_before_update before update on a1 for each row begin declare _value text; set _value = '1234567'; set new.value = _value; end;$ update a1 set value = '';$ select * from a1;