trigger-trans.test 7 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238
# Tests which involve triggers and transactions
# (or just InnoDB storage engine)
--source include/have_innodb.inc

# Save the initial number of concurrent sessions
--source include/count_sessions.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;

--error ER_TRUNCATE_ILLEGAL_FK
TRUNCATE t1;

SELECT @a, @b;

DELETE FROM 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

--echo BUG#31612
--echo 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;
drop trigger t1_after_insert;
drop table t1,t2;

--echo #
--echo #Bug#19683834 SOME INNODB ERRORS CAUSES STORED FUNCTION
--echo #             AND TRIGGER HANDLERS TO BE IGNORED

--echo #Code fixed in Bug#16041903
--enable_connect_log

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;

# Trigger with continue handler for ER_DUP_ENTRY(1062)
DELIMITER //;
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//
DELIMITER ;//

# Transaction 1: Grab locks on t1
START TRANSACTION;
UPDATE t1 SET val = val + 1;

# Transaction 2:
--connect (con2,localhost,root,,test,,)
SET SESSION innodb_lock_wait_timeout = 2;
# Trigger lock timeout (1205)
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t1 SET val = val + 1;

# This insert should go through, as the continue handler should
# handle ER_DUP_ENTRY, even after ER_LOCK_WAIT_TIMEOUT (Bug#16041903)
INSERT INTO t2 (id) VALUES (1);

# Cleanup
disconnect con2;
--source include/wait_until_disconnected.inc
connection default;

DROP TABLE t3, t2, t1;

--disable_connect_log

# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc