# Tests which involve triggers and NDB storage engine --source include/have_ndb.inc --source include/not_embedded.inc # # Test for bug#18437 "Wrong values inserted with a before update # trigger on NDB table". SQL-layer didn't properly inform handler # about fields which were read and set in triggers. In some cases # this resulted in incorrect (garbage) values of OLD variables and # lost changes to NEW variables. # You can find similar tests for ON INSERT triggers in federated.test # since this engine so far is the only engine in MySQL which cares # about field mark-up during handler::write_row() operation. # --disable_warnings drop table if exists t1, t2, t3, t4, t5; --enable_warnings create table t1 (id int primary key, a int not null, b decimal (63,30) default 0) engine=ndb; create table t2 (op char(1), a int not null, b decimal (63,30)) engine=ndb; create table t3 engine=ndb select 1 as i; create table t4 (a int not null primary key, b int) engine=ndb; create table t5 (a int not null primary key, b int) engine=ndb; delimiter //; create trigger t1_bu before update on t1 for each row begin insert into t2 values ("u", old.a, old.b); set new.b = old.b + 10; end;// create trigger t1_bd before delete on t1 for each row begin insert into t2 values ("d", old.a, old.b); end;// create trigger t4_au after update on t4 for each row begin update t5 set b = b+1; end; // create trigger t4_ad after delete on t4 for each row begin update t5 set b = b+1; end; // delimiter ;// insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05); insert into t4 values (1,1), (2,2), (3,3), (4, 4); insert into t5 values (1,0); # Check that usual update works as it should update t1 set a=5 where a != 3; select * from t1 order by id; select * from t2 order by op, a, b; delete from t2; # Check that everything works for multi-update update t1, t3 set a=6 where a = 5; select * from t1 order by id; select * from t2 order by op, a, b; delete from t2; # Check for delete delete from t1 where a != 3; select * from t1 order by id; select * from t2 order by op, a, b; delete from t2; # Check for multi-delete insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (4, 4, 4.05); delete t1 from t1, t3 where a != 3; select * from t1 order by id; select * from t2 order by op, a, b; delete from t2; # Check for insert ... on duplicate key update insert into t1 values (4, 4, 4.05); insert into t1 (id, a) values (4, 1), (3, 1) on duplicate key update a= a + 1; select * from t1 order by id; select * from t2 order by op, a, b; delete from t2; # Check for insert ... select ... on duplicate key update delete from t3; insert into t3 values (4), (3); insert into t1 (id, a) (select i, 1 from t3) on duplicate key update a= a + 1; select * from t1 order by id; select * from t2 order by op, a, b; delete from t2; # Check for replace replace into t1 (id, a) values (4, 1), (3, 1); select * from t1 order by id; select * from t2 order by op, a, b; delete from t1; delete from t2; # Check for replace ... select ... insert into t1 values (3, 1, 1.05), (4, 1, 2.05); replace into t1 (id, a) (select i, 2 from t3); select * from t1 order by id; select * from t2 order by op, a, b; delete from t1; delete from t2; # Check for load data replace insert into t1 values (3, 1, 1.05), (5, 2, 2.05); load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (id, a); select * from t1 order by id; select * from t2 order by op, a, b; update t4 set b = 10 where a = 1; select * from t5 order by a; update t5 set b = 0; delete from t4 where a = 1; select * from t5 order by a; drop trigger t4_au; drop trigger t4_ad; drop table t1, t2, t3, t4, t5; # Test for bug#26242 # Verify that AFTER UPDATE/DELETE triggers are executed # after the change has actually taken place CREATE TABLE t1 ( id INT NOT NULL PRIMARY KEY, xy INT ) ENGINE=ndbcluster; INSERT INTO t1 VALUES (1, 0); DELIMITER //; CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id = NEW.id; END // DELIMITER ;// CREATE TABLE t2 ( id INT NOT NULL PRIMARY KEY, xy INT ) ENGINE=ndbcluster; INSERT INTO t2 VALUES (2, 0); CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY) ENGINE=ndbcluster; INSERT INTO t3 VALUES (1); CREATE TABLE t4 LIKE t1; DELIMITER //; CREATE TRIGGER t4_update AFTER UPDATE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.id = NEW.id; END // DELIMITER ;// CREATE TABLE t5 LIKE t2; UPDATE t1 SET xy = 3 WHERE id = 1; SELECT xy FROM t1 where id = 1; SELECT xy FROM t2 where id = 1; UPDATE t1 SET xy = 4 WHERE id IN (SELECT id FROM t3 WHERE id = 1); SELECT xy FROM t1 where id = 1; SELECT xy FROM t2 where id = 1; INSERT INTO t4 SELECT * FROM t1; INSERT INTO t5 SELECT * FROM t2; UPDATE t1,t4 SET t1.xy = 3, t4.xy = 3 WHERE t1.id = 1 AND t4.id = 1; SELECT xy FROM t1 where id = 1; SELECT xy FROM t2 where id = 1; SELECT xy FROM t4 where id = 1; SELECT xy FROM t5 where id = 1; UPDATE t1,t4 SET t1.xy = 4, t4.xy = 4 WHERE t1.id IN (SELECT id FROM t3 WHERE id = 1) AND t4.id IN (SELECT id FROM t3 WHERE id = 1); SELECT xy FROM t1 where id = 1; SELECT xy FROM t2 where id = 1; SELECT xy FROM t4 where id = 1; SELECT xy FROM t5 where id = 1; INSERT INTO t1 VALUES (1,0) ON DUPLICATE KEY UPDATE xy = 5; SELECT xy FROM t1 where id = 1; SELECT xy FROM t2 where id = 1; DROP TRIGGER t1_update; DROP TRIGGER t4_update; DELIMITER //; CREATE TRIGGER t1_delete AFTER DELETE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id > 4; END // DELIMITER ;// DELIMITER //; CREATE TRIGGER t4_delete AFTER DELETE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.id > 4; END // DELIMITER ;// INSERT INTO t1 VALUES (5, 0),(6,0); INSERT INTO t2 VALUES (5, 1),(6,1); INSERT INTO t3 VALUES (5); SELECT * FROM t1 order by id; SELECT * FROM t2 order by id; DELETE FROM t1 WHERE id IN (SELECT id FROM t3 WHERE id = 5); SELECT * FROM t1 order by id; SELECT * FROM t2 order by id; INSERT INTO t1 VALUES (5,0); UPDATE t2 SET xy = 1 WHERE id = 6; TRUNCATE t4; INSERT INTO t4 SELECT * FROM t1; TRUNCATE t5; INSERT INTO t5 SELECT * FROM t2; SELECT * FROM t1 order by id; SELECT * FROM t2 order by id; SELECT * FROM t4 order by id; SELECT * FROM t5 order by id; DELETE FROM t1,t4 USING t1,t3,t4 WHERE t1.id IN (SELECT id FROM t3 WHERE id = 5) AND t4.id IN (SELECT id FROM t3 WHERE id = 5); SELECT * FROM t1 order by id; SELECT * FROM t2 order by id; SELECT * FROM t4 order by id; SELECT * FROM t5 order by id; INSERT INTO t1 VALUES (5, 0); REPLACE INTO t2 VALUES (6,1); SELECT * FROM t1 order by id; SELECT * FROM t2 order by id; REPLACE INTO t1 VALUES (5, 1); SELECT * FROM t1 order by id; SELECT * FROM t2 order by id; DROP TRIGGER t1_delete; DROP TRIGGER t4_delete; DROP TABLE t1, t2, t3, t4, t5; --echo End of 5.1 tests