diff options
Diffstat (limited to 'mysql-test/main/trigger-trans.result')
| -rw-r--r-- | mysql-test/main/trigger-trans.result | 231 |
1 files changed, 231 insertions, 0 deletions
diff --git a/mysql-test/main/trigger-trans.result b/mysql-test/main/trigger-trans.result new file mode 100644 index 00000000000..c58c4230a40 --- /dev/null +++ b/mysql-test/main/trigger-trans.result @@ -0,0 +1,231 @@ +drop table if exists t1; +create table t1 (a varchar(16), b int) engine=innodb; +create trigger t1_bi before insert on t1 for each row +begin +set new.a := upper(new.a); +set new.b := new.b + 3; +end| +select trigger_schema, trigger_name, event_object_schema, +event_object_table, action_statement from information_schema.triggers +where event_object_schema = 'test' and event_object_table = 't1'; +trigger_schema trigger_name event_object_schema event_object_table action_statement +test t1_bi test t1 begin +set new.a := upper(new.a); +set new.b := new.b + 3; +end +insert into t1 values ('The Lion', 10); +select * from t1; +a b +THE LION 13 +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +select trigger_schema, trigger_name, event_object_schema, +event_object_table, action_statement from information_schema.triggers +where event_object_schema = 'test' and event_object_table = 't1'; +trigger_schema trigger_name event_object_schema event_object_table action_statement +test t1_bi test t1 begin +set new.a := upper(new.a); +set new.b := new.b + 3; +end +insert into t1 values ('The Unicorn', 20); +select * from t1; +a b +THE LION 13 +THE UNICORN 23 +alter table t1 add column c int default 0; +select trigger_schema, trigger_name, event_object_schema, +event_object_table, action_statement from information_schema.triggers +where event_object_schema = 'test' and event_object_table = 't1'; +trigger_schema trigger_name event_object_schema event_object_table action_statement +test t1_bi test t1 begin +set new.a := upper(new.a); +set new.b := new.b + 3; +end +insert into t1 values ('Alice', 30, 1); +select * from t1; +a b c +THE LION 13 0 +THE UNICORN 23 0 +ALICE 33 1 +alter table t1 rename to t1; +select trigger_schema, trigger_name, event_object_schema, +event_object_table, action_statement from information_schema.triggers +where event_object_schema = 'test' and event_object_table = 't1'; +trigger_schema trigger_name event_object_schema event_object_table action_statement +test t1_bi test t1 begin +set new.a := upper(new.a); +set new.b := new.b + 3; +end +insert into t1 values ('The Crown', 40, 1); +select * from t1; +a b c +THE LION 13 0 +THE UNICORN 23 0 +ALICE 33 1 +THE CROWN 43 1 +alter table t1 rename to t1, add column d int default 0; +select trigger_schema, trigger_name, event_object_schema, +event_object_table, action_statement from information_schema.triggers +where event_object_schema = 'test' and event_object_table = 't1'; +trigger_schema trigger_name event_object_schema event_object_table action_statement +test t1_bi test t1 begin +set new.a := upper(new.a); +set new.b := new.b + 3; +end +insert into t1 values ('The Pie', 50, 1, 1); +select * from t1; +a b c d +THE LION 13 0 0 +THE UNICORN 23 0 0 +ALICE 33 1 0 +THE CROWN 43 1 0 +THE PIE 53 1 1 +drop table t1; + +Bug#26141 mixing table types in trigger causes full +table lock on innodb table + +Ensure we do not open and lock tables for the triggers we do not +fire. + +drop table if exists t1, t2, t3; +drop trigger if exists trg_bug26141_au; +drop trigger if exists trg_bug26141_ai; +create table t1 (c int primary key) engine=innodb; +create table t2 (c int) engine=myisam; +create table t3 (c int) engine=myisam; +insert into t1 (c) values (1); +create trigger trg_bug26141_ai after insert on t1 +for each row +begin +insert into t2 (c) values (1); +# We need the 'sync' lock to synchronously wait in connection 2 till +# the moment when the trigger acquired all the locks. +select release_lock("lock_bug26141_sync") into @a; +# 1000 is time in seconds of lock wait timeout -- this is a way +# to cause a manageable sleep up to 1000 seconds +select get_lock("lock_bug26141_wait", 1000) into @a; +end| +create trigger trg_bug26141_au after update on t1 +for each row +begin +insert into t3 (c) values (1); +end| +connect connection_aux,localhost,root,,test,,; +connect connection_update,localhost,root,,test,,; +connection connection_aux; +select get_lock("lock_bug26141_wait", 0); +get_lock("lock_bug26141_wait", 0) +1 +connection default; +select get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0); +get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0) +1 +insert into t1 (c) values (2); +connection connection_update; +select get_lock("lock_bug26141_sync", 1000); +get_lock("lock_bug26141_sync", 1000) +1 +update t1 set c=3 where c=1; +select release_lock("lock_bug26141_sync"); +release_lock("lock_bug26141_sync") +1 +connection connection_aux; +select release_lock("lock_bug26141_wait"); +release_lock("lock_bug26141_wait") +1 +connection default; +select * from t1; +c +2 +3 +select * from t2; +c +1 +select * from t3; +c +1 +drop table t1, t2, t3; +disconnect connection_update; +disconnect connection_aux; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=innodb; +CREATE TABLE t2(b INT, FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=innodb; +INSERT INTO t1 VALUES (1); +CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW SET @a = 1; +CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW SET @b = 1; +SET @a = 0; +SET @b = 0; +TRUNCATE t1; +ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `test`.`t1` (`a`)) +SELECT @a, @b; +@a @b +0 0 +DELETE FROM t1; +SELECT @a, @b; +@a @b +1 1 +INSERT INTO t1 VALUES (1); +DELETE FROM t1; +SELECT @a, @b; +@a @b +1 1 +DROP TABLE t2, t1; +End of 5.0 tests +BUG#31612 +Trigger fired multiple times leads to gaps in auto_increment sequence +create table t1 (a int, val char(1)) engine=InnoDB; +create table t2 (b int auto_increment primary key, +val char(1)) engine=InnoDB; +create trigger t1_after_insert after +insert on t1 for each row insert into t2 set val=NEW.val; +insert into t1 values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'), +(123, 'd'), (123, 'e'), (123, 'f'), (123, 'g'); +insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'), +(654, 'd'), (654, 'e'), (654, 'f'), (654, 'g'); +select * from t2 order by b; +b val +1 a +2 b +3 c +4 d +5 e +6 f +7 g +8 a +9 b +10 c +11 d +12 e +13 f +14 g +drop trigger t1_after_insert; +drop table t1,t2; +# +#Bug#19683834 SOME INNODB ERRORS CAUSES STORED FUNCTION +# AND TRIGGER HANDLERS TO BE IGNORED +#Code fixed in Bug#16041903 +CREATE TABLE t1 (id int unsigned PRIMARY KEY, val int DEFAULT 0) +ENGINE=InnoDB; +INSERT INTO t1 (id) VALUES (1), (2); +CREATE TABLE t2 (id int PRIMARY KEY); +CREATE TABLE t3 LIKE t2; +CREATE TRIGGER bef_insert BEFORE INSERT ON t2 FOR EACH ROW +BEGIN +DECLARE CONTINUE HANDLER FOR 1062 BEGIN END; +INSERT INTO t3 (id) VALUES (NEW.id); +INSERT INTO t3 (id) VALUES (NEW.id); +END// +START TRANSACTION; +UPDATE t1 SET val = val + 1; +connect con2,localhost,root,,test,,; +SET SESSION innodb_lock_wait_timeout = 2; +UPDATE t1 SET val = val + 1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t2 (id) VALUES (1); +disconnect con2; +connection default; +DROP TABLE t3, t2, t1; |