mysql-test/main/trigger-trans.test

# 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

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;

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

--echo #
--echo # MDEV-25738 Assertion `ticket->m_duration == MDL_EXPLICIT' failed in
--echo # void MDL_context::release_lock(MDL_ticket*)
--echo #

CREATE TABLE t1 (id int(11)) ENGINE=InnoDB;
LOCK TABLES t1 WRITE;
SET max_statement_time= 0.001;
--error 0,1969,2013
--disable_warnings
CREATE TRIGGER tr16 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t1 VALUES (1);
--enable_warnings
SET max_statement_time= default;
--disable_warnings
DROP TRIGGER IF EXISTS trg16;
--enable_warnings
DROP TABLE t1;

--echo #
--echo # End of 10.5 tests
--echo #