You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
55 lines
1.4 KiB
55 lines
1.4 KiB
|
|
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.
|
|
|
|
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|
|
|
select get_lock("lock_bug26141_wait", 0);
|
|
get_lock("lock_bug26141_wait", 0)
|
|
1
|
|
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);
|
|
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
|
|
select release_lock("lock_bug26141_wait");
|
|
release_lock("lock_bug26141_wait")
|
|
1
|
|
select * from t1;
|
|
c
|
|
2
|
|
3
|
|
select * from t2;
|
|
c
|
|
1
|
|
select * from t3;
|
|
c
|
|
1
|
|
drop table t1, t2, t3;
|
|
|