Bug #61705 trigger works improperly in mysql cluster.

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,'a1@bug.com','a1@bug.com','abcd',0,1);
INSERT INTO `b` VALUES (2,10,'a1@bug.com');

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 | a1@bug.com | a1@bug.com | 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 = 'a1@bug.com', a.mail = 'a1@bug.com', b.log = 'a1@bug.com' 
WHERE a.id3 = 10 AND a.id = 1 AND b.log = 'a1@bug.com' 
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 |      | a1@bug.com |      |    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: a1@bug.com
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.

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.

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.

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.

To. Bogdan Kecman

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

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

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

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.

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.

this has been fixed in not yet released 7.1.17

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.