# Tests which involve triggers and transactions # (or just InnoDB storage engine) --source include/have_innodb.inc --disable_warnings drop table if exists t1; --enable_warnings # Test for bug #18153 "OPTIMIZE/ALTER on transactional tables corrupt # triggers/triggers are lost". create table t1 (a varchar(16), b int) engine=innodb; delimiter |; 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| delimiter ;| 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'; insert into t1 values ('The Lion', 10); select * from t1; optimize table 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'; insert into t1 values ('The Unicorn', 20); select * from t1; 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'; insert into t1 values ('Alice', 30, 1); select * from t1; # Special tricky cases allowed by ALTER TABLE ... RENAME 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'; insert into t1 values ('The Crown', 40, 1); select * from t1; 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'; insert into t1 values ('The Pie', 50, 1, 1); select * from t1; drop table t1; --echo --echo Bug#26141 mixing table types in trigger causes full --echo table lock on innodb table --echo --echo Ensure we do not open and lock tables for the triggers we do not --echo fire. --echo --disable_warnings drop table if exists t1, t2, t3; drop trigger if exists trg_bug26141_au; drop trigger if exists trg_bug26141_ai; --enable_warnings # Note, for InnoDB to allow concurrent UPDATE and INSERT the # table must have a unique key. 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); delimiter |; 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| delimiter ;| # Establish an alternative connection. --connect (connection_aux,localhost,root,,test,,) --connect (connection_update,localhost,root,,test,,) connection connection_aux; # Lock the wait lock, it must not be locked, so specify zero timeout. select get_lock("lock_bug26141_wait", 0); # connection default; # # Run the trigger synchronously # select get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0); # Will acquire the table level locks, perform the insert into t2, # release the sync lock and block on the wait lock. send insert into t1 (c) values (2); connection connection_update; # Wait for the trigger to acquire its locks and unlock the sync lock. select get_lock("lock_bug26141_sync", 1000); # # This must continue: after the fix for the bug, we do not # open tables for t2, and with c=4 innobase allows the update # to run concurrently with insert. update t1 set c=3 where c=1; select release_lock("lock_bug26141_sync"); connection connection_aux; select release_lock("lock_bug26141_wait"); connection default; reap; select * from t1; select * from t2; select * from t3; # Drops the trigger as well. drop table t1, t2, t3; disconnect connection_update; disconnect connection_aux; # # Bug#34643: TRUNCATE crash if trigger and foreign key. # --disable_warnings DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; --enable_warnings 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; SELECT @a, @b; INSERT INTO t1 VALUES (1); DELETE FROM t1; SELECT @a, @b; DROP TABLE t2, t1; --echo End of 5.0 tests