Bug #61705 trigger works improperly in mysql cluster.

• Submitted: 30 Jun 2011 8:45
• Modified: 18 Nov 2011 11:44
• Reporter: ws lee
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S1 (Critical)
• Version: mysql5.1.51-ndb7.1.10
• OS: Any (Solaris, CentOS)
• CPU Architecture: Any

[30 Jun 2011 8:45] ws lee

Description:
trigger works improperly in mysql cluster 5.1.51-ndb7.1.10.
this bug is big trouble at my service.

i have tested in solaris 2.10 and centos 5.5

How to repeat:
1. create table a
use test;
CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `no` int(11) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NULL DEFAULT NULL,
  `id2` smallint(6) NOT NULL,
  `id3` int(11) NOT NULL,
  `quota` int(11) NOT NULL,
  `mail` varchar(512) NOT NULL,
  `log` varchar(512) NOT NULL,
  `pass` varchar(64) NOT NULL,
  `cls` int(11) NOT NULL DEFAULT '0',
  `copy` tinyint(4) DEFAULT '1',
  UNIQUE KEY `id` (`id`),
  KEY `idx_created` (`created`),
  KEY `idx_id2` (`id2`),
  KEY `idx_id3` (`id3`),
  KEY `mail` (`mail`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8; 

2. create table a2
use test;
CREATE TABLE `a2` (
  `a2_id` int(11) NOT NULL AUTO_INCREMENT,
  `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id` int(11) NULL,
  `no` int(11) NULL,
  `created` timestamp NULL DEFAULT NULL,
  `modified` timestamp NULL DEFAULT NULL,
  `id2` smallint(6) NULL,
  `id3` int(11) NULL,
  `quota` int(11) NULL,
  `mail` varchar(512) NULL,
  `log` varchar(512) NULL,
  `pass` varchar(64) NULL,
  `cls` int(11) NULL,
  `copy` tinyint(4) NULL,
  PRIMARY KEY (a2_id),
  KEY `idx_id2` (`id2`),
  KEY `idx_mail` (`mail`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8; 

3. create trigger of table a
use test;
delimiter //
drop trigger trg1 //
CREATE TRIGGER trg1 AFTER UPDATE ON a
FOR EACH ROW
BEGIN
   INSERT INTO a2( id, no, created, modified, id2, id3, quota, mail, log, pass, cls, copy )
   VALUES( old.id, old.no, old.created, old.modified, old.id2, old.id3, old.quota, old.mail, old.log, old.pass, old.cls, old.copy );
END
//
delimiter ;

4. create table b
use test;
CREATE TABLE `b` (
  `id` int(11) NOT NULL,
  `id3` int(11) NOT NULL,
  `log` varchar(512) NOT NULL,
  UNIQUE KEY `idx_id` (`id`),
  KEY `idx_id3` (`id3`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 

5. data insert
INSERT INTO `a` VALUES (1,0,'2011-06-30 07:31:59',NULL,1001,10,50,'[e-mail]','[e-mail]','abcd',0,1);
INSERT INTO `b` VALUES (2,10,'[e-mail]');

4. confirm data table a;
mysql> select * from a; 
+----+----+---------------------+----------+------+-----+-------+------------+------------+------+-----+------+
| id | no | created             | modified | id2  | id3 | quota | mail       | log        | pass | cls | copy |
+----+----+---------------------+----------+------+-----+-------+------------+------------+------+-----+------+
|  1 |  0 | 2011-06-30 16:31:59 |     NULL | 1001 |  10 |    50 | [e-mail] | [e-mail] | abcd |   0 |    1 |
+----+----+---------------------+----------+------+-----+-------+------------+------------+------+-----+------+

5. mysql client exit
mysql> exit

6. after relogin in mysql, run update query like below.
firstly!!!! run update query below.
this update query use join between two table.
# mysql -uroot -p test 
mysql> 
UPDATE a, b 
SET a.log = '[e-mail]', a.mail = '[e-mail]', b.log = '[e-mail]' 
WHERE a.id3 = 10 AND a.id = 1 AND b.log = '[e-mail]' 
AND b.id3 = a.id3 AND a.log = b.log; 

7. and then, data check.
mysql> select * from a2;
+-------+---------------------+------+------+---------------------+----------+------+------+-------+------+------------+------+------+------+
| a2_id | inserted            | id   | no   | created             | modified | id2  | id3  | quota | mail | log        | pass | cls  | copy |
+-------+---------------------+------+------+---------------------+----------+------+------+-------+------+------------+------+------+------+
|     1 | 2011-06-30 17:34:01 |    1 |    0 | 0000-00-00 00:00:00 |     NULL |    0 |   10 |     0 |      | [e-mail] |      |    0 | NULL |
+-------+---------------------+------+------+---------------------+----------+------+------+-------+------+------------+------+------+------+

you will see amazing data mofied impropery.
old data of table a inerted to table a2 by trigger trg1.
but, table2 data is strange.

table a data is 
created: 2011-06-30 16:31:59 
id2: 1001
quota: 50
mail: [e-mail]
pass:abcd
copy: 1

if update table a data, before data is inserted to a2.
but, table a2 data is show shorkingly like upper result(select * from a2)

created:0000-00-00 00:00:00
id2: 0
qupta:0
mail:
pass:
copy: NULL

Suggested fix:
if using table join,
trigger seems to works improperly.

[30 Jun 2011 16:44] ws lee

for reference, I encounterd this bug after upgrade mysql5.1.32-ndb6.3.24 to mysql5.1.51-ndb7.1.10.

In previous mysql5.1.32-ndb6.3.24 no problem.

[2 Jul 2011 4:03] ws lee

Unbeliveable this trouble.
If you use mysql cluster 7.1 in production system, you will see horrible result.

I suggest to degrade mysql cluster 7.1 release status from Production release to developement release.

[3 Jul 2011 17:15] MySQL Verification Team

I verified this with 6.3.28 and up (7.1 and 7.2 too). 

Thanks for submitting the bug, we will work asap to fix this.

[4 Jul 2011 13:08] ws lee

To. Bogdan Kecman

Thanks for your reply.
I will look forward to wait for that patch.

[3 Aug 2011 1:26] ws lee

I have waiting patch over 1 month.
this patch not yet?

[7 Nov 2011 17:40] Jonas Oreland

your wait is "soon" over...we have finally got this fixed

[9 Nov 2011 0:14] ws lee

To. Jonas Oreland

thanks.
this bug must be a fatal bug. 
but I have very very very disappointed shorkingly very very very slow handle.
after oracle be owner, mysql cluster bug patch seems to  ruin.

[9 Nov 2011 0:14] ws lee

To. Jonas Oreland

thanks.
this bug must be a fatal bug. 
but I have very very very disappointed shorkingly very very very slow handle.
after oracle be owner, mysql cluster bug patch seems to  ruin.

[18 Nov 2011 11:44] Jonas Oreland

this has been fixed in not yet released 7.1.17

[13 Dec 2011 18:54] Jon Stephens

Documented fix in the NDB 6.3.47, 7.0.28, and 7.1.17 changelogs as follows:

        When deleting from multiple tables using a unique key in the
        WHERE condition, the wrong rows were deleted. In addition,
        UPDATE triggers failed when rows were changed by deleting from
        or updating multiple tables.

Closed.