Create 'main' test directory and move 't' and 'r' there

1 files changed, 235 insertions, 0 deletions

diff --git a/mysql-test/main/trigger-trans.test b/mysql-test/main/trigger-trans.test
new file mode 100644
index 00000000000..17656c3516e
--- /dev/null
+++ b/mysql-test/main/trigger-trans.test
@@ -0,0 +1,235 @@
+# 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