diff options
Diffstat (limited to 'mysql-test/main/trigger.test')
| -rw-r--r-- | mysql-test/main/trigger.test | 2750 |
1 files changed, 2750 insertions, 0 deletions
diff --git a/mysql-test/main/trigger.test b/mysql-test/main/trigger.test new file mode 100644 index 00000000000..1557ef200e5 --- /dev/null +++ b/mysql-test/main/trigger.test @@ -0,0 +1,2750 @@ +# This test uses chmod, can't be run with root permissions +-- source include/not_as_root.inc + + +# +# Basic triggers test +# + +--disable_warnings +drop table if exists t1, t2, t3, t4; +drop view if exists v1; +drop database if exists mysqltest; +drop function if exists f1; +drop function if exists f2; +drop procedure if exists p1; +--enable_warnings + +# Create additional connections used through test +connect (addconroot1, localhost, root,,); +connect (addconroot2, localhost, root,,); +# Connection without current database set +connect (addconwithoutdb, localhost, root,,*NO-ONE*); +connection default; + +create table t1 (i int); + +# let us test some very simple trigger +create trigger trg before insert on t1 for each row set @a:=1; +set @a:=0; +select @a; +insert into t1 values (1); +select @a; +drop trigger trg; + +# let us test simple trigger reading some values +create trigger trg before insert on t1 for each row set @a:=new.i; +insert into t1 values (123); +select @a; +drop trigger trg; + +drop table t1; + +# Let us test before insert trigger +# Such triggers can be used for setting complex default values +create table t1 (i int not null, j int); +delimiter |; +create trigger trg before insert on t1 for each row +begin + if isnull(new.j) then + set new.j:= new.i * 10; + end if; +end| +insert into t1 (i) values (1)| +insert into t1 (i,j) values (2, 3)| +select * from t1| +drop trigger trg| +drop table t1| +delimiter ;| + +# After insert trigger +# Useful for aggregating data +create table t1 (i int not null primary key); +create trigger trg after insert on t1 for each row + set @a:= if(@a,concat(@a, ":", new.i), new.i); +set @a:=""; +insert into t1 values (2),(3),(4),(5); +select @a; +drop trigger trg; +drop table t1; + +# PS doesn't work with multi-row statements +--disable_ps_protocol +# Before update trigger +# (In future we will achieve this via proper error handling in triggers) +create table t1 (aid int not null primary key, balance int not null default 0); +insert into t1 values (1, 1000), (2,3000); +delimiter |; +create trigger trg before update on t1 for each row +begin + declare loc_err varchar(255); + if abs(new.balance - old.balance) > 1000 then + set new.balance:= old.balance; + set loc_err := concat("Too big change for aid = ", new.aid); + set @update_failed:= if(@update_failed, concat(@a, ":", loc_err), loc_err); + end if; +end| +set @update_failed:=""| +update t1 set balance=1500| +select @update_failed; +select * from t1| +drop trigger trg| +drop table t1| +delimiter ;| +--enable_ps_protocol + +# After update trigger +create table t1 (i int); +insert into t1 values (1),(2),(3),(4); +create trigger trg after update on t1 for each row + set @total_change:=@total_change + new.i - old.i; +set @total_change:=0; +update t1 set i=3; +select @total_change; +drop trigger trg; +drop table t1; + +# Before delete trigger +# This can be used for aggregation too :) +create table t1 (i int); +insert into t1 values (1),(2),(3),(4); +create trigger trg before delete on t1 for each row + set @del_sum:= @del_sum + old.i; +set @del_sum:= 0; +delete from t1 where i <= 3; +select @del_sum; +drop trigger trg; +drop table t1; + +# After delete trigger. +# Just run out of imagination. +create table t1 (i int); +insert into t1 values (1),(2),(3),(4); +create trigger trg after delete on t1 for each row set @del:= 1; +set @del:= 0; +delete from t1 where i <> 0; +select @del; +drop trigger trg; +drop table t1; + +# Several triggers on one table +create table t1 (i int, j int); + +delimiter |; +create trigger trg1 before insert on t1 for each row +begin + if new.j > 10 then + set new.j := 10; + end if; +end| +create trigger trg2 before update on t1 for each row +begin + if old.i % 2 = 0 then + set new.j := -1; + end if; +end| +create trigger trg3 after update on t1 for each row +begin + if new.j = -1 then + set @fired:= "Yes"; + end if; +end| +delimiter ;| +set @fired:=""; +insert into t1 values (1,2),(2,3),(3,14); +select @fired; +select * from t1; +update t1 set j= 20; +select @fired; +select * from t1; + +drop trigger trg1; +drop trigger trg2; +drop trigger trg3; +drop table t1; + + +# Let us test how triggers work for special forms of INSERT such as +# REPLACE and INSERT ... ON DUPLICATE KEY UPDATE +create table t1 (id int not null primary key, data int); +create trigger t1_bi before insert on t1 for each row + set @log:= concat(@log, "(BEFORE_INSERT: new=(id=", new.id, ", data=", new.data,"))"); +create trigger t1_ai after insert on t1 for each row + set @log:= concat(@log, "(AFTER_INSERT: new=(id=", new.id, ", data=", new.data,"))"); +create trigger t1_bu before update on t1 for each row + set @log:= concat(@log, "(BEFORE_UPDATE: old=(id=", old.id, ", data=", old.data, + ") new=(id=", new.id, ", data=", new.data,"))"); +create trigger t1_au after update on t1 for each row + set @log:= concat(@log, "(AFTER_UPDATE: old=(id=", old.id, ", data=", old.data, + ") new=(id=", new.id, ", data=", new.data,"))"); +create trigger t1_bd before delete on t1 for each row + set @log:= concat(@log, "(BEFORE_DELETE: old=(id=", old.id, ", data=", old.data,"))"); +create trigger t1_ad after delete on t1 for each row + set @log:= concat(@log, "(AFTER_DELETE: old=(id=", old.id, ", data=", old.data,"))"); +# Simple INSERT - both triggers should be called +set @log:= ""; +insert into t1 values (1, 1); +select @log; +# INSERT IGNORE for already existing key - only before trigger should fire +set @log:= ""; +insert ignore t1 values (1, 2); +select @log; +# INSERT ... ON DUPLICATE KEY UPDATE ... +set @log:= ""; +insert into t1 (id, data) values (1, 3), (2, 2) on duplicate key update data= data + 1; +select @log; +# REPLACE (also test for bug#13479 "REPLACE activates UPDATE trigger, +# not the DELETE and INSERT triggers") +# We define REPLACE as INSERT which DELETEs old rows which conflict with +# row being inserted. So for the first record in statement below we will +# call before insert trigger, then delete will be executed (and both delete +# triggers should fire). Finally after insert trigger will be called. +# For the second record we will just call both on insert triggers. +set @log:= ""; +replace t1 values (1, 4), (3, 3); +select @log; +# Now we will drop ON DELETE triggers to test REPLACE which is internally +# executed via update +drop trigger t1_bd; +drop trigger t1_ad; +set @log:= ""; +replace t1 values (1, 5); +select @log; + +# This also drops associated triggers +drop table t1; + + +# +# Let us test triggers which access other tables. +# +# Trivial trigger which inserts data into another table +create table t1 (id int primary key, data varchar(10), fk int); +create table t2 (event varchar(100)); +create table t3 (id int primary key); +create trigger t1_ai after insert on t1 for each row + insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "'")); +insert into t1 (id, data) values (1, "one"), (2, "two"); +select * from t1; +select * from t2; +drop trigger t1_ai; +# Trigger which uses couple of tables (and partially emulates FK constraint) +delimiter |; +create trigger t1_bi before insert on t1 for each row +begin + if exists (select id from t3 where id=new.fk) then + insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "' fk=", new.fk)); + else + insert into t2 values (concat("INSERT INTO t1 FAILED id=", new.id, " data='", new.data, "' fk=", new.fk)); + set new.id= NULL; + end if; +end| +delimiter ;| +insert into t3 values (1); +insert into t1 values (4, "four", 1), (5, "five", 2); +select * from t1; +select * from t2; +drop table t1, t2, t3; +# Trigger which invokes function +create table t1 (id int primary key, data varchar(10)); +create table t2 (seq int); +insert into t2 values (10); +create function f1 () returns int return (select max(seq) from t2); +delimiter |; +create trigger t1_bi before insert on t1 for each row +begin + if new.id > f1() then + set new.id:= f1(); + end if; +end| +delimiter ;| +insert into t1 values (1, "first"); +insert into t1 values (f1(), "max"); +select * from t1; +drop table t1, t2; +drop function f1; +# Trigger which forces invocation of another trigger +# (emulation of FK on delete cascade policy) +create table t1 (id int primary key, fk_t2 int); +create table t2 (id int primary key, fk_t3 int); +create table t3 (id int primary key); +insert into t1 values (1,1), (2,1), (3,2); +insert into t2 values (1,1), (2,2); +insert into t3 values (1), (2); +create trigger t3_ad after delete on t3 for each row + delete from t2 where fk_t3=old.id; +create trigger t2_ad after delete on t2 for each row + delete from t1 where fk_t2=old.id; +delete from t3 where id = 1; +select * from t1 left join (t2 left join t3 on t2.fk_t3 = t3.id) on t1.fk_t2 = t2.id; +drop table t1, t2, t3; +# Trigger which assigns value selected from table to field of row +# being inserted/updated. +create table t1 (id int primary key, copy int); +create table t2 (id int primary key, data int); +insert into t2 values (1,1), (2,2); +create trigger t1_bi before insert on t1 for each row + set new.copy= (select data from t2 where id = new.id); +create trigger t1_bu before update on t1 for each row + set new.copy= (select data from t2 where id = new.id); +insert into t1 values (1,3), (2,4), (3,3); +update t1 set copy= 1 where id = 2; +select * from t1; +drop table t1, t2; + +# +# Test of wrong column specifiers in triggers +# +create table t1 (i int); +create table t3 (i int); + +--error ER_TRG_NO_SUCH_ROW_IN_TRG +create trigger trg before insert on t1 for each row set @a:= old.i; +--error ER_TRG_NO_SUCH_ROW_IN_TRG +create trigger trg before delete on t1 for each row set @a:= new.i; +--error ER_TRG_CANT_CHANGE_ROW +create trigger trg before update on t1 for each row set old.i:=1; +--error ER_TRG_NO_SUCH_ROW_IN_TRG +create trigger trg before delete on t1 for each row set new.i:=1; +--error ER_TRG_CANT_CHANGE_ROW +create trigger trg after update on t1 for each row set new.i:=1; +--error ER_BAD_FIELD_ERROR +create trigger trg before update on t1 for each row set new.j:=1; +--error ER_BAD_FIELD_ERROR +create trigger trg before update on t1 for each row set @a:=old.j; + + +# +# Let us test various trigger creation errors +# Also quickly test table namespace (bug#5892/6182) +# +--error ER_NO_SUCH_TABLE +create trigger trg before insert on t2 for each row set @a:=1; + +create trigger trg before insert on t1 for each row set @a:=1; +--error ER_TRG_ALREADY_EXISTS +create trigger trg after insert on t1 for each row set @a:=1; +create trigger trg2 before insert on t1 for each row set @a:=1; +drop trigger trg2; +--error ER_TRG_ALREADY_EXISTS +create trigger trg before insert on t3 for each row set @a:=1; +create trigger trg2 before insert on t3 for each row set @a:=1; +drop trigger trg2; +drop trigger trg; + +--error ER_TRG_DOES_NOT_EXIST +drop trigger trg; + +create view v1 as select * from t1; +--error ER_WRONG_OBJECT +create trigger trg before insert on v1 for each row set @a:=1; +drop view v1; + +drop table t1; +drop table t3; + +create temporary table t1 (i int); +--error ER_TRG_ON_VIEW_OR_TEMP_TABLE +create trigger trg before insert on t1 for each row set @a:=1; +drop table t1; + + + +# +# Tests for various trigger-related bugs +# + +# Test for bug #5887 "Triggers with string literals cause errors". +# New .FRM parser was not handling escaped strings properly. +create table t1 (x1col char); +create trigger tx1 before insert on t1 for each row set new.x1col = 'x'; +insert into t1 values ('y'); +drop trigger tx1; +drop table t1; + +# +# Test for bug #5890 "Triggers fail for DELETE without WHERE". +# If we are going to delete all rows in table but DELETE triggers exist +# we should perform row-by-row deletion instead of using optimized +# delete_all_rows() method. +# +create table t1 (i int) engine=myisam; +insert into t1 values (1), (2); +create trigger trg1 before delete on t1 for each row set @del_before:= @del_before + old.i; +create trigger trg2 after delete on t1 for each row set @del_after:= @del_after + old.i; +set @del_before:=0, @del_after:= 0; +delete from t1; +select @del_before, @del_after; +drop trigger trg1; +drop trigger trg2; +drop table t1; + +# Test for bug #5859 "DROP TABLE does not drop triggers". Trigger should not +# magically reappear when we recreate dropped table. +create table t1 (a int); +create trigger trg1 before insert on t1 for each row set new.a= 10; +drop table t1; +create table t1 (a int); +insert into t1 values (); +select * from t1; +drop table t1; + +# Test for bug #6559 "DROP DATABASE forgets to drop triggers". +create database mysqltest; +use mysqltest; +create table t1 (i int); +create trigger trg1 before insert on t1 for each row set @a:= 1; +# This should succeed +drop database mysqltest; +use test; + +# Test for bug #8791 +# "Triggers: Allowed to create triggers on a subject table in a different DB". +create database mysqltest; +create table mysqltest.t1 (i int); +--error ER_TRG_IN_WRONG_SCHEMA +create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1; +use mysqltest; +--error ER_NO_SUCH_TABLE +create trigger test.trg1 before insert on t1 for each row set @a:= 1; +drop database mysqltest; +use test; + + +# Test for bug #5860 "Multi-table UPDATE does not activate update triggers" +# We will also test how delete triggers wor for multi-table DELETE. +create table t1 (i int, j int default 10, k int not null, key (k)); +create table t2 (i int); +insert into t1 (i, k) values (1, 1); +insert into t2 values (1); +create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j; +create trigger trg2 after update on t1 for each row set @b:= "Fired"; +set @a:= 0, @b:= ""; +# Check that trigger works in case of update on the fly +update t1, t2 set j = j + 10 where t1.i = t2.i; +select @a, @b; +insert into t1 values (2, 13, 2); +insert into t2 values (2); +set @a:= 0, @b:= ""; +# And now let us check that triggers work in case of multi-update which +# is done through temporary tables... +update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2; +select @a, @b; +# Let us test delete triggers for multi-delete now. +# We create triggers for both tables because we want test how they +# work in both on-the-fly and via-temp-tables cases. +create trigger trg3 before delete on t1 for each row set @c:= @c + old.j; +create trigger trg4 before delete on t2 for each row set @d:= @d + old.i; +create trigger trg5 after delete on t1 for each row set @e:= "After delete t1 fired"; +create trigger trg6 after delete on t2 for each row set @f:= "After delete t2 fired"; +set @c:= 0, @d:= 0, @e:= "", @f:= ""; +delete t1, t2 from t1, t2 where t1.i = t2.i; +select @c, @d, @e, @f; +# This also will drop triggers +drop table t1, t2; + +# Test for bug #6812 "Triggers are not activated for INSERT ... SELECT". +# (We also check the fact that trigger modifies some field does not affect +# value of next record inserted). +delimiter |; +create table t1 (i int, j int default 10)| +create table t2 (i int)| +insert into t2 values (1), (2)| +create trigger trg1 before insert on t1 for each row +begin + if new.i = 1 then + set new.j := 1; + end if; +end| +create trigger trg2 after insert on t1 for each row set @a:= 1| +set @a:= 0| +insert into t1 (i) select * from t2| +select * from t1| +select @a| +# This also will drop triggers +drop table t1, t2| +delimiter ;| + +# Test for bug #8755 "Trigger is not activated by LOAD DATA" +create table t1 (i int, j int, k int); +create trigger trg1 before insert on t1 for each row set new.k = new.i; +create trigger trg2 after insert on t1 for each row set @b:= "Fired"; +set @b:=""; +# Test triggers with file with separators +load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, i); +select *, @b from t1; +set @b:=""; +# Test triggers with fixed size row file +load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j); +select *, @b from t1; +# This also will drop triggers +drop table t1; + +# Test for bug #5894 "Triggers with altered tables cause corrupt databases" +# Also tests basic error handling for various kinds of triggers. +create table t1 (i int, at int, k int, key(k)) engine=myisam; +create table t2 (i int); +insert into t1 values (1, 1, 1); +# We need at least 3 elements in t2 to test multi-update properly +insert into t2 values (1), (2), (3); +# Create and then break "after" triggers +create trigger ai after insert on t1 for each row set @a:= new.at; +create trigger au after update on t1 for each row set @a:= new.at; +create trigger ad after delete on t1 for each row set @a:= old.at; +alter table t1 drop column at; +# We still should be able select data from tables. +select * from t1; +# The following statements changing t1 should fail, but still cause +# their main effect. This is because operation on the table row is +# executed before "after" trigger and its effect cannot be rolled back +# when whole statement fails, because t1 is MyISAM table. +--error ER_BAD_FIELD_ERROR +insert into t1 values (2, 1); +select * from t1; +--error ER_BAD_FIELD_ERROR +update t1 set k = 2 where i = 2; +select * from t1; +--error ER_BAD_FIELD_ERROR +delete from t1 where i = 2; +select * from t1; +# Should fail and insert only 1 row +--error ER_BAD_FIELD_ERROR +load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k); +select * from t1; +--error ER_BAD_FIELD_ERROR +insert into t1 select 3, 3; +select * from t1; +# Multi-update working on the fly, again it will update only +# one row even if more matches +--error ER_BAD_FIELD_ERROR +update t1, t2 set k = k + 10 where t1.i = t2.i; +select * from t1; +# The same for multi-update via temp table +--error ER_BAD_FIELD_ERROR +update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3; +select * from t1; +# Multi-delete on the fly +--error ER_BAD_FIELD_ERROR +delete t1, t2 from t1 straight_join t2 where t1.i = t2.i; +select * from t1; +# And via temporary storage +--error ER_BAD_FIELD_ERROR +delete t2, t1 from t2 straight_join t1 where t1.i = t2.i; +select * from t1; +# Prepare table for testing of REPLACE and INSERT ... ON DUPLICATE KEY UPDATE +alter table t1 add primary key (i); +--error ER_BAD_FIELD_ERROR +insert into t1 values (3, 4) on duplicate key update k= k + 10; +select * from t1; +# The following statement will delete old row and won't +# insert new one since after delete trigger will fail. +--error ER_BAD_FIELD_ERROR +replace into t1 values (3, 3); +select * from t1; +# Also drops all triggers +drop table t1, t2; + +create table t1 (i int, bt int, k int, key(k)) engine=myisam; +create table t2 (i int); +insert into t1 values (1, 1, 1), (2, 2, 2); +insert into t2 values (1), (2), (3); +# Create and then break "before" triggers +create trigger bi before insert on t1 for each row set @a:= new.bt; +create trigger bu before update on t1 for each row set @a:= new.bt; +create trigger bd before delete on t1 for each row set @a:= old.bt; +alter table t1 drop column bt; +# The following statements changing t1 should fail and should not +# cause any effect on table, since "before" trigger is executed +# before operation on the table row. +--error ER_BAD_FIELD_ERROR +insert into t1 values (3, 3); +select * from t1; +--error ER_BAD_FIELD_ERROR +update t1 set i = 2; +select * from t1; +--error ER_BAD_FIELD_ERROR +delete from t1; +select * from t1; +--error ER_BAD_FIELD_ERROR +load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k); +select * from t1; +--error ER_BAD_FIELD_ERROR +insert into t1 select 3, 3; +select * from t1; +# Both types of multi-update (on the fly and via temp table) +--error ER_BAD_FIELD_ERROR +update t1, t2 set k = k + 10 where t1.i = t2.i; +select * from t1; +--error ER_BAD_FIELD_ERROR +update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2; +select * from t1; +# Both types of multi-delete +--error ER_BAD_FIELD_ERROR +delete t1, t2 from t1 straight_join t2 where t1.i = t2.i; +select * from t1; +--error ER_BAD_FIELD_ERROR +delete t2, t1 from t2 straight_join t1 where t1.i = t2.i; +select * from t1; +# Let us test REPLACE/INSERT ... ON DUPLICATE KEY UPDATE. +# To test properly code-paths different from those that are used +# in ordinary INSERT we need to drop "before insert" trigger. +alter table t1 add primary key (i); +drop trigger bi; +--error ER_BAD_FIELD_ERROR +insert into t1 values (2, 4) on duplicate key update k= k + 10; +select * from t1; +--error ER_BAD_FIELD_ERROR +replace into t1 values (2, 4); +select * from t1; +# Also drops all triggers +drop table t1, t2; + +# Test for bug #5893 "Triggers with dropped functions cause crashes" +# Appropriate error should be reported instead of crash. +# Also test for bug #11889 "Server crashes when dropping trigger +# using stored routine". +--disable_warnings +drop function if exists bug5893; +--enable_warnings +create table t1 (col1 int, col2 int); +insert into t1 values (1, 2); +create function bug5893 () returns int return 5; +create trigger t1_bu before update on t1 for each row set new.col1= bug5893(); +drop function bug5893; +--error ER_SP_DOES_NOT_EXIST +update t1 set col2 = 4; +# This should not crash server too. +drop trigger t1_bu; +drop table t1; + +# +# storing and restoring parsing modes for triggers (BUG#5891) +# +set sql_mode='ansi'; +create table t1 ("t1 column" int); +create trigger t1_bi before insert on t1 for each row set new."t1 column" = 5; +set sql_mode=""; +insert into t1 values (0); +# create trigger with different sql_mode +create trigger t1_af after insert on t1 for each row set @a=10; +insert into t1 values (0); +select * from t1; +select @a; +--replace_column 6 # +show triggers; +drop table t1; +# check that rigger preserve sql_mode during execution +set sql_mode="traditional"; +create table t1 (a date); +-- error 1292 +insert into t1 values ('2004-01-00'); +set sql_mode=""; +create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00'; +set sql_mode="traditional"; +insert into t1 values ('2004-01-01'); +select * from t1; +set sql_mode=default; +show create table t1; +--replace_column 6 # +show triggers; +drop table t1; + +# Test for bug #12280 "Triggers: crash if flush tables" +# FLUSH TABLES and FLUSH PRIVILEGES should be disallowed inside +# of functions and triggers. +create table t1 (id int); +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row reset query cache; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row reset master; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row reset slave; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush hosts; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush tables with read lock; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush logs; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush status; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush slave; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush master; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush des_key_file; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush user_resources; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush tables; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger t1_ai after insert on t1 for each row flush privileges; +--disable_warnings +drop procedure if exists p1; +--enable_warnings + +create trigger t1_ai after insert on t1 for each row call p1(); +create procedure p1() flush tables; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() reset query cache; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() reset master; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() reset slave; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush hosts; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush privileges; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush tables with read lock; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush tables; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush logs; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush status; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush slave; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush master; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush des_key_file; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +create procedure p1() flush user_resources; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 values (0); + +drop procedure p1; +drop table t1; + +# Test for bug #11973 "SELECT .. INTO var_name; in trigger cause +# crash on update" + +create table t1 (id int, data int, username varchar(16)); +insert into t1 (id, data) values (1, 0); +delimiter |; +create trigger t1_whoupdated before update on t1 for each row +begin + declare user varchar(32); + declare i int; + select user() into user; + set NEW.username = user; + select count(*) from ((select 1) union (select 2)) as d1 into i; +end| +delimiter ;| +update t1 set data = 1; + +connection addconroot1; +update t1 set data = 2; + +connection default; +drop table t1; + +# +# #11587 Trigger causes lost connection error +# + +create table t1 (c1 int, c2 datetime); +delimiter |; +--error ER_SP_NO_RETSET +create trigger tr1 before insert on t1 for each row +begin + set new.c2= '2004-04-01'; + select 'hello'; +end| +delimiter ;| + +insert into t1 (c1) values (1),(2),(3); +select * from t1; + +--disable_warnings +drop procedure if exists bug11587; +--enable_warnings + +delimiter |; +create procedure bug11587(x char(16)) +begin + select "hello"; + select "hello again"; +end| + +create trigger tr1 before insert on t1 for each row +begin + call bug11587(); + set new.c2= '2004-04-02'; +end| +delimiter ;| + +--error ER_SP_NO_RETSET +insert into t1 (c1) values (4),(5),(6); +select * from t1; + +drop procedure bug11587; +drop table t1; + +# Test for bug #11896 "Partial locking in case of recursive trigger +# definitions". Recursion in triggers should not be allowed. +# We also should not allow to change tables which are used in +# statements invoking this trigger. +create table t1 (f1 integer); +create table t2 (f2 integer); +create trigger t1_ai after insert on t1 + for each row insert into t2 values (new.f1+1); +create trigger t2_ai after insert on t2 + for each row insert into t1 values (new.f2+1); +# Allow SP resursion to be show that it has not influence here +set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth; +set @@max_sp_recursion_depth=100; +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +insert into t1 values (1); +set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS; +select * from t1; +select * from t2; +drop trigger t1_ai; +drop trigger t2_ai; +create trigger t1_bu before update on t1 + for each row insert into t1 values (2); +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +update t1 set f1= 10; +select * from t1; +drop trigger t1_bu; +create trigger t1_bu before update on t1 + for each row delete from t1 where f1=new.f1; +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +update t1 set f1= 10; +select * from t1; +drop trigger t1_bu; +# This should work tough +create trigger t1_bi before insert on t1 + for each row set new.f1=(select sum(f1) from t1); +insert into t1 values (3); +select * from t1; +drop trigger t1_bi; +drop tables t1, t2; + +# Tests for bug #12704 "Server crashes during trigger execution". +# If we run DML statements and CREATE TRIGGER statements concurrently +# it may happen that trigger will be created while DML statement is +# waiting for table lock. In this case we have to reopen tables and +# recalculate prelocking set. +# Unfortunately these tests rely on the order in which tables are locked +# by statement so they are non determenistic and are disabled. +--disable_parsing +create table t1 (id int); +create table t2 (id int); +create table t3 (id int); +create function f1() returns int return (select max(id)+2 from t2); +create view v1 as select f1() as f; + +# Let us check that we notice trigger at all +connection addconroot1; +lock tables t2 write; +connection default; +send insert into t1 values ((select max(id) from t2)), (2); +--sleep 1 +connection addconroot2; +create trigger t1_trg before insert on t1 for each row set NEW.id:= 1; +connection addconroot1; +unlock tables; +connection default; +reap; +select * from t1; + +# Check that we properly calculate new prelocking set +insert into t2 values (3); +connection addconroot1; +lock tables t2 write; +connection default; +send insert into t1 values ((select max(id) from t2)), (4); +--sleep 1 +connection addconroot2; +drop trigger t1_trg; +create trigger t1_trg before insert on t1 for each row + insert into t3 values (new.id); +connection addconroot1; +unlock tables; +connection default; +reap; +select * from t1; +select * from t3; + +# We should be able to do this even if fancy views are involved +connection addconroot1; +lock tables t2 write; +connection default; +send insert into t1 values ((select max(f) from v1)), (6); +--sleep 1 +connection addconroot2; +drop trigger t1_trg; +create trigger t1_trg before insert on t1 for each row + insert into t3 values (new.id + 100); +connection addconroot1; +unlock tables; +connection default; +reap; +select * from t1; +select * from t3; + +# This also should work for multi-update +# Let us drop trigger to demonstrate that prelocking set is really +# rebuilt +drop trigger t1_trg; +connection addconroot1; +lock tables t2 write; +connection default; +send update t1, t2 set t1.id=10 where t1.id=t2.id; +--sleep 1 +connection addconroot2; +create trigger t1_trg before update on t1 for each row + insert into t3 values (new.id); +connection addconroot1; +unlock tables; +connection default; +reap; +select * from t1; +select * from t3; + +# And even for multi-update converted from ordinary update thanks to view +drop view v1; +drop trigger t1_trg; +create view v1 as select t1.id as id1 from t1, t2 where t1.id= t2.id; +insert into t2 values (10); +connection addconroot1; +lock tables t2 write; +connection default; +send update v1 set id1= 11; +--sleep 1 +connection addconroot2; +create trigger t1_trg before update on t1 for each row + insert into t3 values (new.id + 100); +connection addconroot1; +unlock tables; +connection default; +reap; +select * from t1; +select * from t3; + +drop function f1; +drop view v1; +drop table t1, t2, t3; +--enable_parsing + +# +# Test for bug #13399 "Crash when executing PS/SP which should activate +# trigger which is now dropped". See also test for similar bug for stored +# routines in sp-error.test (#12329). +create table t1 (id int); +create table t2 (id int); +create trigger t1_bi before insert on t1 for each row insert into t2 values (new.id); +prepare stmt1 from "insert into t1 values (10)"; +create procedure p1() insert into t1 values (10); +call p1(); +# Actually it is enough to do FLUSH TABLES instead of DROP TRIGGER +drop trigger t1_bi; +# Server should not crash on these two statements +execute stmt1; +call p1(); +deallocate prepare stmt1; +drop procedure p1; + +# Let us test more complex situation when we alter trigger in such way that +# it uses different set of tables (or simply add new trigger). +create table t3 (id int); +create trigger t1_bi after insert on t1 for each row insert into t2 values (new.id); +prepare stmt1 from "insert into t1 values (10)"; +create procedure p1() insert into t1 values (10); +call p1(); +# Altering trigger forcing it use different set of tables +drop trigger t1_bi; +create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id); +execute stmt1; +call p1(); +deallocate prepare stmt1; +drop procedure p1; +drop table t1, t2, t3; + +# +# BUG#13549 "Server crash with nested stored procedures". +# Server should not crash when during execution of stored procedure +# we have to parse trigger/function definition and this new trigger/ +# function has more local variables declared than invoking stored +# procedure and last of these variables is used in argument of NOT +# operator. +# +create table t1 (a int); +DELIMITER //; +CREATE PROCEDURE `p1`() +begin + insert into t1 values (1); +end// +create trigger trg before insert on t1 for each row +begin + declare done int default 0; + set done= not done; +end// +DELIMITER ;// +CALL p1(); +drop procedure p1; +drop table t1; + +# +# Test for bug #14863 "Triggers: crash if create and there is no current +# database". We should not crash and give proper error when database for +# trigger or its table is not specified and there is no current database. +# +connection addconwithoutdb; +--error ER_NO_DB_ERROR +create trigger t1_bi before insert on test.t1 for each row set @a:=0; +--error ER_NO_SUCH_TABLE +create trigger test.t1_bi before insert on t1 for each row set @a:=0; +--error ER_NO_DB_ERROR +drop trigger t1_bi; +connection default; + +# +# Tests for bug #13525 "Rename table does not keep info of triggers" +# and bug #17866 "Problem with renaming table with triggers with fully +# qualified subject table". +# +create table t1 (id int); +create trigger t1_bi before insert on t1 for each row set @a:=new.id; +create trigger t1_ai after insert on test.t1 for each row set @b:=new.id; +insert into t1 values (101); +select @a, @b; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test'; +rename table t1 to t2; +# Trigger should work after rename +insert into t2 values (102); +select @a, @b; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test'; +# Let us check that the same works for simple ALTER TABLE ... RENAME +alter table t2 rename to t3; +insert into t3 values (103); +select @a, @b; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test'; +# And for more complex ALTER TABLE +alter table t3 rename to t4, add column val int default 0; +insert into t4 values (104, 1); +select @a, @b; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test'; +# .TRN file should be updated with new table name +drop trigger t1_bi; +drop trigger t1_ai; +drop table t4; +# Rename between different databases if triggers exist should fail +create database mysqltest; +use mysqltest; +create table t1 (id int); +create trigger t1_bi before insert on t1 for each row set @a:=new.id; +insert into t1 values (101); +select @a; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test' or event_object_schema = 'mysqltest'; +--error ER_TRG_IN_WRONG_SCHEMA +rename table t1 to test.t2; +insert into t1 values (102); +select @a; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test' or event_object_schema = 'mysqltest'; +# There should be no fantom .TRN files +--error ER_TRG_DOES_NOT_EXIST +drop trigger test.t1_bi; +# Let us also check handling of this restriction in ALTER TABLE ... RENAME +--error ER_TRG_IN_WRONG_SCHEMA +alter table t1 rename to test.t1; +insert into t1 values (103); +select @a; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test' or event_object_schema = 'mysqltest'; +# Again there should be no fantom .TRN files +--error ER_TRG_DOES_NOT_EXIST +drop trigger test.t1_bi; +--error ER_TRG_IN_WRONG_SCHEMA +alter table t1 rename to test.t1, add column val int default 0; +insert into t1 values (104); +select @a; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test' or event_object_schema = 'mysqltest'; +# Table definition should not change +show create table t1; +# And once again check for fantom .TRN files +--error ER_TRG_DOES_NOT_EXIST +drop trigger test.t1_bi; +drop trigger t1_bi; +drop table t1; +drop database mysqltest; +use test; +# And now let us check that the properly handle rename if there is some +# error during it (that we rollback such renames completely). +create table t1 (id int); +create trigger t1_bi before insert on t1 for each row set @a:=new.id; +create trigger t1_ai after insert on t1 for each row set @b:=new.id; +insert into t1 values (101); +select @a, @b; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test'; +# Trick which makes update of second .TRN file impossible +let $MYSQLD_DATADIR= `select @@datadir`; +write_file $MYSQLD_DATADIR/test/t1_ai.TRN~; +dummy +EOF +chmod 0000 $MYSQLD_DATADIR/test/t1_ai.TRN~; +# Normalize the datadir path; the embedded server doesn't chdir to datadir +--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' +--error 1 +rename table t1 to t2; +# 't1' should be still there and triggers should work correctly +insert into t1 values (102); +select @a, @b; +select trigger_schema, trigger_name, event_object_schema, + event_object_table, action_statement from information_schema.triggers + where event_object_schema = 'test'; +chmod 0600 $MYSQLD_DATADIR/test/t1_ai.TRN~; +remove_file $MYSQLD_DATADIR/test/t1_ai.TRN~; +# Let us check that updates to .TRN files were rolled back too +drop trigger t1_bi; +drop trigger t1_ai; +drop table t1; + +# Test for bug #16829 "Firing trigger with RETURN crashes the server" +# RETURN is not supposed to be used anywhere except functions, so error +# should be returned when one attempts to create trigger with RETURN. +create table t1 (i int); +--error ER_SP_BADRETURN +create trigger t1_bi before insert on t1 for each row return 0; +insert into t1 values (1); +drop table t1; + +# Test for bug #17764 "Trigger crashes MyISAM table" +# +# Table was reported as crashed when it was subject table of trigger invoked +# by insert statement which was executed with enabled bulk insert mode (which +# is actually set of optimizations enabled by handler::start_bulk_insert()) +# and this trigger also explicitly referenced it. +# The same problem arose when table to which bulk insert was done was also +# referenced in function called by insert statement. +create table t1 (a varchar(64), b int); +create table t2 like t1; +create trigger t1_ai after insert on t1 for each row + set @a:= (select max(a) from t1); +insert into t1 (a) values + ("Twas"),("brillig"),("and"),("the"),("slithy"),("toves"), + ("Did"),("gyre"),("and"),("gimble"),("in"),("the"),("wabe"); +create trigger t2_ai after insert on t2 for each row + set @a:= (select max(a) from t2); +insert into t2 select * from t1; +load data infile '../../std_data/words.dat' into table t1 (a); +drop trigger t1_ai; +drop trigger t2_ai; +# Test that the problem for functions is fixed as well +create function f1() returns int return (select max(b) from t1); +insert into t1 values + ("All",f1()),("mimsy",f1()),("were",f1()),("the",f1()),("borogoves",f1()), + ("And",f1()),("the",f1()),("mome", f1()),("raths",f1()),("outgrabe",f1()); +create function f2() returns int return (select max(b) from t2); +insert into t2 select a, f2() from t1; +load data infile '../../std_data/words.dat' into table t1 (a) set b:= f1(); +drop function f1; +drop function f2; +drop table t1, t2; + +# +# Test for bug #16021 "Wrong index given to function in trigger" which +# was caused by the same bulk insert optimization as bug #17764 but had +# slightly different symptoms (instead of reporting table as crashed +# storage engine reported error number 124) +# +create table t1(i int not null, j int not null, n numeric(15,2), primary key(i,j)); +create table t2(i int not null, n numeric(15,2), primary key(i)); +delimiter |; +create trigger t1_ai after insert on t1 for each row +begin + declare sn numeric(15,2); + select sum(n) into sn from t1 where i=new.i; + replace into t2 values(new.i, sn); +end| +delimiter ;| +insert into t1 values + (1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00), + (1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00), + (1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00); +select * from t1; +select * from t2; +drop tables t1, t2; + +# +# Test for Bug #16461 connection_id() does not work properly inside trigger +# +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 ( + conn_id INT, + trigger_conn_id INT +); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW + SET NEW.trigger_conn_id = CONNECTION_ID(); + +INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1); + +connect (con1,localhost,root,,); +INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1); +connection default; +disconnect con1; + +SELECT * FROM t1 WHERE conn_id != trigger_conn_id; + +DROP TRIGGER t1_bi; +DROP TABLE t1; + + +# +# Bug#6951: Triggers/Traditional: SET @ result wrong +# +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (i1 INT); + +SET @save_sql_mode=@@sql_mode; + +SET SQL_MODE=''; + +CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW + SET @x = 5/0; + +SET SQL_MODE='traditional'; + +CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW + SET @x = 5/0; + +SET @x=1; +INSERT INTO t1 VALUES (@x); +SELECT @x; + +SET @x=2; +UPDATE t1 SET i1 = @x; +SELECT @x; + +SET SQL_MODE=''; + +SET @x=3; +INSERT INTO t1 VALUES (@x); +SELECT @x; + +SET @x=4; +UPDATE t1 SET i1 = @x; +SELECT @x; + +SET @@sql_mode=@save_sql_mode; + +DROP TRIGGER t1_ai; +DROP TRIGGER t1_au; +DROP TABLE t1; + + +# +# Test for bug #14635 Accept NEW.x as INOUT parameters to stored +# procedures from within triggers +# +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +CREATE TABLE t1 (i1 INT); + +# Check that NEW.x pseudo variable is accepted as INOUT and OUT +# parameter to stored routine. +INSERT INTO t1 VALUES (3); +CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5; +CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7; +delimiter //; +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW +BEGIN + CALL p1(NEW.i1); + CALL p2(NEW.i1); +END// +delimiter ;// +UPDATE t1 SET i1 = 11 WHERE i1 = 3; +DROP TRIGGER t1_bu; +DROP PROCEDURE p2; +DROP PROCEDURE p1; + +# Check that OLD.x pseudo variable is not accepted as INOUT and OUT +# parameter to stored routine. +INSERT INTO t1 VALUES (13); +CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17; +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW + CALL p1(OLD.i1); +--error ER_SP_NOT_VAR_ARG +UPDATE t1 SET i1 = 19 WHERE i1 = 13; +DROP TRIGGER t1_bu; +DROP PROCEDURE p1; + +INSERT INTO t1 VALUES (23); +CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29; +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW + CALL p1(OLD.i1); +--error ER_SP_NOT_VAR_ARG +UPDATE t1 SET i1 = 31 WHERE i1 = 23; +DROP TRIGGER t1_bu; +DROP PROCEDURE p1; + +# Check that NEW.x pseudo variable is read-only in the AFTER TRIGGER. +INSERT INTO t1 VALUES (37); +CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41; +CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW + CALL p1(NEW.i1); +--error ER_SP_NOT_VAR_ARG +UPDATE t1 SET i1 = 43 WHERE i1 = 37; +DROP TRIGGER t1_au; +DROP PROCEDURE p1; + +INSERT INTO t1 VALUES (47); +CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49; +CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW + CALL p1(NEW.i1); +--error ER_SP_NOT_VAR_ARG +UPDATE t1 SET i1 = 51 WHERE i1 = 47; +DROP TRIGGER t1_au; +DROP PROCEDURE p1; + +# Post requisite. +SELECT * FROM t1; + +DROP TABLE t1; + +# +# Bug #18005: Creating a trigger on mysql.event leads to server crash on +# scheduler startup +# +# Bug #18361: Triggers on mysql.user table cause server crash +# +# We don't allow triggers on the mysql schema +delimiter |; +--error ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA +create trigger wont_work after update on mysql.user for each row +begin + set @a:= 1; +end| +# Try when we're already using the mysql schema +use mysql| +--error ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA +create trigger wont_work after update on event for each row +begin + set @a:= 1; +end| +use test| +delimiter ;| + + +# +# Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause. +# + +# Prepare. + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings + +CREATE TABLE t1(c INT); +CREATE TABLE t2(c INT); + +--error ER_WRONG_STRING_LENGTH +CREATE DEFINER=longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost + TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; + +--error ER_WRONG_STRING_LENGTH +CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY + TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW SET @a = 2; + +# Cleanup. + +DROP TABLE t1; +DROP TABLE t2; + +# +# Bug#20028 Function with select return no data +# + +--disable_warnings +drop table if exists t1; +drop table if exists t2; +drop table if exists t3; +drop table if exists t4; +--enable_warnings + +SET @save_sql_mode=@@sql_mode; + +delimiter |; +SET sql_mode='TRADITIONAL'| +create table t1 (id int(10) not null primary key, v int(10) )| +create table t2 (id int(10) not null primary key, v int(10) )| +create table t3 (id int(10) not null primary key, v int(10) )| +create table t4 (c int)| + +create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1| +create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1| + +insert into t1 values(10, 10)| +set @a:=1/0| +select 1/0 from t1| + +create trigger t1_bi before insert on t1 for each row set @a:=1/0| + +insert into t1 values(20, 20)| + +drop trigger t1_bi| +create trigger t1_bi before insert on t1 for each row +begin + insert into t2 values (new.id, new.v); + update t2 set v=v+1 where id= new.id; + replace t3 values (new.id, 0); + update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id; + create temporary table t5 select * from t1; + delete from t5; + insert into t5 select * from t1; + insert into t4 values (0); + set @check= (select count(*) from t5); + update t4 set c= @check; + drop temporary table t5; + + set @a:=1/0; +end| + +set @check=0, @t4_bi_called=0, @t4_bu_called=0| +insert into t1 values(30, 30)| +select @check, @t4_bi_called, @t4_bu_called| + +delimiter ;| + +SET @@sql_mode=@save_sql_mode; + +drop table t1; +drop table t2; +drop table t3; +drop table t4; + +# +# Bug#20670 "UPDATE using key and invoking trigger that modifies +# this key does not stop" +# + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (i int, j int key); +insert into t1 values (1,1), (2,2), (3,3); +create trigger t1_bu before update on t1 for each row + set new.j = new.j + 10; +# This should not work indefinitely and should cause +# expected result +update t1 set i= i+ 10 where j > 2; +select * from t1; +drop table t1; + +# +# Bug#23556 TRUNCATE TABLE still maps to DELETE +# +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); + +CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW + INSERT INTO t2 VALUES (OLD.a); + +FLUSH STATUS; +TRUNCATE t1; +SHOW STATUS LIKE 'handler_delete'; +SELECT COUNT(*) FROM t2; + +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +DELETE FROM t2; + +FLUSH STATUS; +DELETE FROM t1; +SHOW STATUS LIKE 'handler_delete'; +SELECT COUNT(*) FROM t2; + +DROP TRIGGER trg_t1; +DROP TABLE t1,t2; + +# +# Bug #23651 "Server crashes when trigger which uses stored function +# invoked from different connections". +# +--disable_warnings +drop table if exists t1; +drop function if exists f1; +--enable_warnings +create table t1 (i int); +create function f1() returns int return 10; +create trigger t1_bi before insert on t1 for each row set @a:= f1() + 10; +insert into t1 values (); +select @a; +connection addconroot1; +insert into t1 values (); +select @a; +connection default; +drop table t1; +drop function f1; + +# +# Bug#23703: DROP TRIGGER needs an IF EXISTS +# + +--disable_warnings +drop table if exists t1; +--enable_warnings + +create table t1(a int, b varchar(50)); + +-- error ER_TRG_DOES_NOT_EXIST +drop trigger not_a_trigger; + +drop trigger if exists not_a_trigger; + +create trigger t1_bi before insert on t1 +for each row set NEW.b := "In trigger t1_bi"; + +insert into t1 values (1, "a"); +drop trigger if exists t1_bi; +insert into t1 values (2, "b"); +drop trigger if exists t1_bi; +insert into t1 values (3, "c"); + +select * from t1; + +drop table t1; + +# +# Bug#25398: crash when a trigger contains a SELECT with +# trigger fields in the select list under DISTINCT +# + +CREATE TABLE t1 ( + id int NOT NULL DEFAULT '0', + a varchar(10) NOT NULL, + b varchar(10), + c varchar(10), + d timestamp NOT NULL, + PRIMARY KEY (id, a) +); + +CREATE TABLE t2 ( + fubar_id int unsigned NOT NULL DEFAULT '0', + last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + PRIMARY KEY (fubar_id) +); + +DELIMITER |; + +CREATE TRIGGER fubar_change + AFTER UPDATE ON t1 + FOR EACH ROW + BEGIN + INSERT INTO t2 (fubar_id, last_change_time) + SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time + FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c) + ON DUPLICATE KEY UPDATE + last_change_time = + IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time); + END +| + +DELIMITER ;| + +INSERT INTO t1 (id,a, b,c,d) VALUES + (1,'a','b','c',now()),(2,'a','b','c',now()); + +UPDATE t1 SET c='Bang!' WHERE id=1; + +SELECT fubar_id FROM t2; + +DROP TABLE t1,t2; + +# +# Bug#21285 (Incorrect message error deleting records in a table with a +# trigger for inserting) +# + +--disable_warnings +DROP TABLE IF EXISTS bug21825_A; +DROP TABLE IF EXISTS bug21825_B; +--enable_warnings + +CREATE TABLE bug21825_A (id int(10)); +CREATE TABLE bug21825_B (id int(10)); + +delimiter //; + +CREATE TRIGGER trgA AFTER INSERT ON bug21825_A +FOR EACH ROW +BEGIN + INSERT INTO bug21825_B (id) values (1); +END// +delimiter ;// + +INSERT INTO bug21825_A (id) VALUES (10); +INSERT INTO bug21825_A (id) VALUES (20); + +DROP TABLE bug21825_B; + +# Must pass, the missing table in the insert trigger should not matter. +DELETE FROM bug21825_A WHERE id = 20; + +DROP TABLE bug21825_A; + +# +# Bug#22580 (DROP TABLE in nested stored procedure causes strange dependancy +# error) +# + +--disable_warnings +DROP TABLE IF EXISTS bug22580_t1; +DROP PROCEDURE IF EXISTS bug22580_proc_1; +DROP PROCEDURE IF EXISTS bug22580_proc_2; +--enable_warnings + +CREATE TABLE bug22580_t1 (a INT, b INT); + +DELIMITER ||; + +CREATE PROCEDURE bug22580_proc_2() +BEGIN + DROP TABLE IF EXISTS bug22580_tmp; + CREATE TEMPORARY TABLE bug22580_tmp (a INT); + DROP TABLE bug22580_tmp; +END|| + +CREATE PROCEDURE bug22580_proc_1() +BEGIN + CALL bug22580_proc_2(); +END|| + +CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1 +FOR EACH ROW +BEGIN + CALL bug22580_proc_1(); +END|| + +DELIMITER ;|| + +# Must pass, the actions of the update trigger should not matter +INSERT INTO bug22580_t1 VALUES (1,1); + +DROP TABLE bug22580_t1; +DROP PROCEDURE bug22580_proc_1; +DROP PROCEDURE bug22580_proc_2; + +# +# Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE +# +--disable_warnings +DROP TRIGGER IF EXISTS trg27006_a_update; +DROP TRIGGER IF EXISTS trg27006_a_insert; +--enable_warnings + +CREATE TABLE t1 ( + `id` int(10) unsigned NOT NULL auto_increment, + `val` varchar(10) NOT NULL, + PRIMARY KEY (`id`) +); +CREATE TABLE t2 like t1; +DELIMITER |; + +CREATE TRIGGER trg27006_a_insert AFTER INSERT ON t1 FOR EACH ROW +BEGIN + insert into t2 values (NULL,new.val); +END | +CREATE TRIGGER trg27006_a_update AFTER UPDATE ON t1 FOR EACH ROW +BEGIN + insert into t2 values (NULL,new.val); +END | +DELIMITER ;| + +INSERT INTO t1(val) VALUES ('test1'),('test2'); +SELECT * FROM t1; +SELECT * FROM t2; +INSERT INTO t1 VALUES (2,'test2') ON DUPLICATE KEY UPDATE val=VALUES(val); +INSERT INTO t1 VALUES (2,'test3') ON DUPLICATE KEY UPDATE val=VALUES(val); +INSERT INTO t1 VALUES (3,'test4') ON DUPLICATE KEY UPDATE val=VALUES(val); +SELECT * FROM t1; +SELECT * FROM t2; +DROP TRIGGER trg27006_a_insert; +DROP TRIGGER trg27006_a_update; +drop table t1,t2; + +# +# Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers" +# + +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings +create table t1 (i int); +create trigger t1_bi before insert on t1 for each row set new.i = 7; +create trigger t1_ai after insert on t1 for each row set @a := 7; +create table t2 (j int); +insert into t2 values (1), (2); +set @a:=""; +insert into t1 select * from t2; +select * from t1; +select @a; +# Let us check that trigger that involves table also works ok. +drop trigger t1_bi; +drop trigger t1_ai; +create table t3 (isave int); +create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i); +insert into t1 select * from t2; +select * from t1; +select * from t3; +drop table t1, t2, t3; + +disconnect addconroot1; +disconnect addconroot2; +disconnect addconwithoutdb; +--echo +--echo Bug#28502 Triggers that update another innodb table will block +--echo on X lock unnecessarily +--echo +--echo Ensure we do not open and lock tables for triggers we do not fire. +--echo +--disable_warnings +drop table if exists t1, t2; +drop trigger if exists trg_bug28502_au; +--enable_warnings + +create table t1 (id int, count int); +create table t2 (id int); +delimiter |; + +create trigger trg_bug28502_au before update on t2 +for each row +begin + if (new.id is not null) then + update t1 set count= count + 1 where id = old.id; + end if; +end| + +delimiter ;| +insert into t1 (id, count) values (1, 0); + +lock table t1 write; + +--connect (connection_insert, localhost, root, , test, , ) +connection connection_insert; +# Is expected to pass. +insert into t2 set id=1; +connection default; +unlock tables; +update t2 set id=1 where id=1; +select * from t1; +select * from t2; +# Will drop the trigger +drop table t1, t2; +disconnect connection_insert; +--echo +--echo Additionally, provide test coverage for triggers and +--echo all MySQL data changing commands. +--echo +--disable_warnings +drop table if exists t1, t2, t1_op_log; +drop view if exists v1; +drop trigger if exists trg_bug28502_bi; +drop trigger if exists trg_bug28502_ai; +drop trigger if exists trg_bug28502_bu; +drop trigger if exists trg_bug28502_au; +drop trigger if exists trg_bug28502_bd; +drop trigger if exists trg_bug28502_ad; +--enable_warnings +create table t1 (id int primary key auto_increment, operation varchar(255)); +create table t2 (id int primary key); +create table t1_op_log(operation varchar(255)); +create view v1 as select * from t1; +create trigger trg_bug28502_bi before insert on t1 +for each row + insert into t1_op_log (operation) + values (concat("Before INSERT, new=", new.operation)); + +create trigger trg_bug28502_ai after insert on t1 +for each row + insert into t1_op_log (operation) + values (concat("After INSERT, new=", new.operation)); + +create trigger trg_bug28502_bu before update on t1 +for each row + insert into t1_op_log (operation) + values (concat("Before UPDATE, new=", new.operation, + ", old=", old.operation)); + +create trigger trg_bug28502_au after update on t1 +for each row + insert into t1_op_log (operation) + values (concat("After UPDATE, new=", new.operation, + ", old=", old.operation)); + +create trigger trg_bug28502_bd before delete on t1 +for each row + insert into t1_op_log (operation) + values (concat("Before DELETE, old=", old.operation)); + +create trigger trg_bug28502_ad after delete on t1 +for each row + insert into t1_op_log (operation) + values (concat("After DELETE, old=", old.operation)); + +insert into t1 (operation) values ("INSERT"); + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +update t1 set operation="UPDATE" where id=@id; + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +delete from t1 where id=@id; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +insert into t1 (id, operation) values +(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key") +on duplicate key update id=NULL, operation="Should never happen"; + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +insert into t1 (id, operation) values +(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same") +on duplicate key update id=NULL, +operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate"; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +replace into t1 values (NULL, "REPLACE, inserting a new key"); + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +replace into t1 values (@id, "REPLACE, deleting the duplicate"); + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +insert into t1 +select NULL, "CREATE TABLE ... SELECT, inserting a new key"; + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +replace into t1 +select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key"; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +insert into t1 (id, operation) +select NULL, "INSERT ... SELECT, inserting a new key"; + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +insert into t1 (id, operation) +select @id, +"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate" +on duplicate key update id=NULL, +operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +replace into t1 (id, operation) +select NULL, "REPLACE ... SELECT, inserting a new key"; + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +replace into t1 (id, operation) +select @id, "REPLACE ... SELECT, deleting a duplicate"; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +insert into t1 (id, operation) values (1, "INSERT for multi-DELETE"); +insert into t2 (id) values (1); + +delete t1.*, t2.* from t1, t2 where t1.id=1; + +select * from t1; +select * from t2; +select * from t1_op_log; +truncate t1; +truncate t2; +truncate t1_op_log; + +insert into t1 (id, operation) values (1, "INSERT for multi-UPDATE"); +insert into t2 (id) values (1); +update t1, t2 set t1.id=2, operation="multi-UPDATE" where t1.id=1; +update t1, t2 +set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where t1.id=2; + +select * from t1; +select * from t2; +select * from t1_op_log; +truncate table t1; +truncate table t2; +truncate table t1_op_log; + +--echo +--echo Now do the same but use a view instead of the base table. +--echo + +insert into v1 (operation) values ("INSERT"); + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +update v1 set operation="UPDATE" where id=@id; + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +delete from v1 where id=@id; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +insert into v1 (id, operation) values +(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key") +on duplicate key update id=NULL, operation="Should never happen"; + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +insert into v1 (id, operation) values +(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same") +on duplicate key update id=NULL, +operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate"; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +replace into v1 values (NULL, "REPLACE, inserting a new key"); + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +replace into v1 values (@id, "REPLACE, deleting the duplicate"); + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +insert into v1 +select NULL, "CREATE TABLE ... SELECT, inserting a new key"; + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +replace into v1 +select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key"; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +insert into v1 (id, operation) +select NULL, "INSERT ... SELECT, inserting a new key"; + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +insert into v1 (id, operation) +select @id, +"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate" +on duplicate key update id=NULL, +operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +replace into v1 (id, operation) +select NULL, "REPLACE ... SELECT, inserting a new key"; + +set @id=last_insert_id(); + +select * from t1; +select * from t1_op_log; +truncate t1_op_log; + +replace into v1 (id, operation) +select @id, "REPLACE ... SELECT, deleting a duplicate"; + +select * from t1; +select * from t1_op_log; +truncate t1; +truncate t1_op_log; + +insert into v1 (id, operation) values (1, "INSERT for multi-DELETE"); +insert into t2 (id) values (1); + +delete v1.*, t2.* from v1, t2 where v1.id=1; + +select * from t1; +select * from t2; +select * from t1_op_log; +truncate t1; +truncate t2; +truncate t1_op_log; + +insert into v1 (id, operation) values (1, "INSERT for multi-UPDATE"); +insert into t2 (id) values (1); +update v1, t2 set v1.id=2, operation="multi-UPDATE" where v1.id=1; +update v1, t2 +set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where v1.id=2; + +select * from t1; +select * from t2; +select * from t1_op_log; + +drop view v1; +drop table t1, t2, t1_op_log; + +# +# TODO: test LOAD DATA INFILE +# +--echo +--echo Bug#27248 Triggers: error if insert affects temporary table +--echo +--echo The bug was fixed by the fix for Bug#26141 +--echo +--disable_warnings +drop table if exists t1; +drop temporary table if exists t2; +--enable_warnings +create table t1 (s1 int); +create temporary table t2 (s1 int); +create trigger t1_bi before insert on t1 for each row insert into t2 values (0); +create trigger t1_bd before delete on t1 for each row delete from t2; +insert into t1 values (0); +insert into t1 values (0); +select * from t1; +select * from t2; +delete from t1; +select * from t1; +select * from t2; +drop table t1; +drop temporary table t2; + +--echo #------------------------------------------------------------------------ +--echo # Bug#39953 Triggers are not working properly with multi table updates +--echo #------------------------------------------------------------------------ + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TRIGGER IF EXISTS t_insert; +DROP TABLE IF EXISTS t2; +--enable_warnings + +CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a)); +INSERT INTO t1 (a) VALUES (2),(5); +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +DELIMITER |; +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END | +DELIMITER ;| +INSERT INTO t2 (a,b) VALUES (1,2); + +DROP TRIGGER t_insert; + +DELIMITER |; +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END | +DELIMITER ;| +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO t2 (a,b) VALUES (3,5); + +DROP TABLE t1; +DROP TRIGGER t_insert; +DROP TABLE t2; + +--echo End of 5.0 tests + +# +# Bug#25411 (trigger code truncated) +# + +--disable_warnings +drop table if exists table_25411_a; +drop table if exists table_25411_b; +--enable_warnings + +create table table_25411_a(a int); +create table table_25411_b(b int); + +create trigger trg_25411a_ai after insert on table_25411_a +for each row + insert into table_25411_b select new.*; + +select * from table_25411_a; + +--error ER_BAD_TABLE_ERROR +insert into table_25411_a values (1); + +select * from table_25411_a; + +drop table table_25411_a; +drop table table_25411_b; + +# +# Bug #31866: MySQL Server crashes on SHOW CREATE TRIGGER statement +# + +--disable_warnings +DROP TRIGGER IF EXISTS trg; +--enable_warnings + +--error ER_TRG_DOES_NOT_EXIST +SHOW CREATE TRIGGER trg; + +# +# Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock +# +# Test of trigger creation and removal under LOCK TABLES +# + +--disable_warnings +drop table if exists t1; +--enable_warnings + +create table t1 (i int, j int); + +create trigger t1_bi before insert on t1 for each row begin end; +--error ER_TRG_ALREADY_EXISTS +create trigger t1_bi before insert on t1 for each row begin end; +create trigger t1_bi2 before insert on t1 for each row begin end; +drop trigger t1_bi; +drop trigger t1_bi2; +--error ER_TRG_DOES_NOT_EXIST +drop trigger t1_bi; + +lock tables t1 read; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +create trigger t1_bi before insert on t1 for each row begin end; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +create trigger t1_bi before insert on t1 for each row begin end; +--error ER_TRG_DOES_NOT_EXIST +drop trigger t1_bi; +unlock tables; + +create trigger t1_bi before insert on t1 for each row begin end; +lock tables t1 read; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +create trigger t1_bi before insert on t1 for each row begin end; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +drop trigger t1_bi; +unlock tables; +drop trigger t1_bi; + +lock tables t1 write; +create trigger b1_bi before insert on t1 for each row set new.i = new.i + 10; +insert into t1 values (10, 10); +drop trigger b1_bi; +insert into t1 values (10, 10); +select * from t1; +unlock tables; + +drop table t1; + +# +# Bug#23771 AFTER UPDATE trigger not invoked when there are no changes of the data +# + +--disable_warnings +drop table if exists t1, t2; +drop trigger if exists trg1; +drop trigger if exists trg2; +--enable_warnings +create table t1 (a int); +create table t2 (b int); +create trigger trg1 after update on t1 for each row set @a= @a+1; +create trigger trg2 after update on t2 for each row set @b= @b+1; +insert into t1 values (1), (2), (3); +insert into t2 values (1), (2), (3); +set @a= 0; +set @b= 0; +update t1, t2 set t1.a= t1.a, t2.b= t2.b; +select @a, @b; +update t1, t2 set t1.a= t2.b, t2.b= t1.a; +select @a, @b; +update t1 set a= a; +select @a, @b; +update t2 set b= b; +select @a, @b; +update t1 set a= 1; +select @a, @b; +update t2 set b= 1; +select @a, @b; +drop trigger trg1; +drop trigger trg2; +drop table t1, t2; + +# +# Bug#44653: Server crash noticed when executing random queries with partitions. +# +CREATE TABLE t1 ( a INT, b INT ); +CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY, b INT ); + +INSERT INTO t1 (a) VALUES (1); + +delimiter //; +CREATE TRIGGER tr1 +BEFORE INSERT ON t2 +FOR EACH ROW +BEGIN + UPDATE a_nonextisting_table SET a = 1; +END// +delimiter ;// + +--disable_abort_on_error +CREATE TABLE IF NOT EXISTS t2 ( a INT, b INT ) SELECT a, b FROM t1; +--enable_abort_on_error + +# Caused failed assertion +SELECT * FROM t2; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug#51650 crash with user variables and triggers +--echo # + +--disable_warnings +DROP TRIGGER IF EXISTS trg1; +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (b VARCHAR(50) NOT NULL); +CREATE TABLE t2 (a VARCHAR(10) NOT NULL DEFAULT ''); + +delimiter //; +CREATE TRIGGER trg1 AFTER INSERT ON t2 +FOR EACH ROW BEGIN + SELECT 1 FROM t1 c WHERE + (@bug51650 IS NULL OR @bug51650 != c.b) AND c.b = NEW.a LIMIT 1 INTO @foo; +END// +delimiter ;// + +SET @bug51650 = 1; +INSERT IGNORE INTO t2 VALUES(); +INSERT IGNORE INTO t1 SET b = '777'; +INSERT IGNORE INTO t2 SET a = '111'; +SET @bug51650 = 1; +INSERT IGNORE INTO t2 SET a = '777'; + +DROP TRIGGER trg1; +DROP TABLE t1, t2; + +# +# Bug #48525: trigger changes "Column 'id' cannot be null" behaviour +# +CREATE TABLE t1 (id INT NOT NULL); +CREATE TABLE t2 (id INT NOT NULL); +INSERT t1 VALUES (1),(2),(3); +UPDATE IGNORE t1 SET id=NULL; +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW + INSERT INTO t2 VALUES (3); +UPDATE t1 SET id=NULL; +DROP TRIGGER t1_bu; +DROP TABLE t1,t2; + +--echo # +--echo # Bug#50755: Crash if stored routine def contains version comments +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +DROP TRIGGER IF EXISTS trg1; +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE DATABASE db1; +USE db1; + +CREATE TABLE t1 (b INT); +CREATE TABLE t2 (a INT); + +CREATE TRIGGER trg1 BEFORE INSERT ON t2 FOR EACH ROW INSERT/*!INTO*/t1 VALUES (1); +--echo # Used to crash +--replace_column 6 # +SHOW TRIGGERS IN db1; +--error ER_PARSE_ERROR +INSERT INTO t2 VALUES (1); +SELECT * FROM t1; + +--echo # Work around Bug#45235 +let $MYSQLD_DATADIR = `select @@datadir`; +--remove_file $MYSQLD_DATADIR/db1/t2.TRG +--remove_file $MYSQLD_DATADIR/db1/trg1.TRN + +DROP DATABASE db1; +USE test; + +--echo End of 5.1 tests. + +# +# Test that using a trigger will not open mysql.proc +# +create table t1 (i int); +create table t2 (i int); +flush tables; +flush status; +delimiter //; +CREATE DEFINER=`root`@`localhost` TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW BEGIN DELETE FROM t2 WHERE t2.i = OLD.i; END // +delimiter ;// +insert into t1 values (1),(2); +insert into t2 values (1),(2); +delete from t1 where i=1; +# +# If mysql.proc would be used we would have 4 here. 3 is the correct number. +# (CREATE TRIGGER will open t1 and then flush it) +# +show status like 'Opened_tables'; +select * from t1; +select * from t2; +drop table t1,t2; + +--echo End of 5.2 tests. + +--echo # +--echo # Bug#34453 Can't change size of file (Errcode: 1224) +--echo # + +--disable_warnings +DROP TRIGGER IF EXISTS t1_bi; +DROP TRIGGER IF EXISTS t1_bd; +DROP TABLE IF EXISTS t1; +DROP TEMPORARY TABLE IF EXISTS t2; +--enable_warnings + +CREATE TABLE t1 (s1 INT); +CREATE TEMPORARY TABLE t2 (s1 INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (0); +CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t2; +INSERT INTO t1 VALUES (0); +INSERT INTO t1 VALUES (0); +SELECT * FROM t1; +SELECT * FROM t2; +-- echo # Reported to give ERROR 14 (HY000): +-- echo # Can't change size of file (Errcode: 1224) +-- echo # on Windows +DELETE FROM t1; + +DROP TABLE t1; +DROP TEMPORARY TABLE t2; + +# +# Bug#36649: Condition area is not properly cleaned up after stored routine invocation +# + +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; +--disable_warnings +DROP TRIGGER IF EXISTS trg1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); + +delimiter |; +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE a CHAR; + SELECT 'ab' INTO a; + SELECT 'ab' INTO a; + SELECT 'a' INTO a; +END| +delimiter ;| + +INSERT INTO t1 VALUES (1); + +DROP TRIGGER trg1; +DROP TABLE t1; + +# +# Successive trigger actuations +# + +--disable_warnings +DROP TRIGGER IF EXISTS trg1; +DROP TRIGGER IF EXISTS trg2; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); + +delimiter |; + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE trg1 CHAR; + SELECT 'ab' INTO trg1; +END| + +CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE trg2 CHAR; + SELECT 'ab' INTO trg2; +END| + +delimiter ;| +SET sql_mode = DEFAULT; + +INSERT INTO t1 VALUES (0); +SELECT * FROM t1; +SHOW WARNINGS; +INSERT INTO t1 VALUES (1),(2); + +DROP TRIGGER trg1; +DROP TRIGGER trg2; +DROP TABLE t1; + + +--echo # +--echo # Bug #46747 "Crash in MDL_ticket::upgrade_shared_lock_to_exclusive +--echo # on TRIGGER + TEMP table". +--echo # + +--disable_warnings +drop trigger if exists t1_bi; +drop temporary table if exists t1; +drop table if exists t1; +--enable_warnings + +create table t1 (i int); +create trigger t1_bi before insert on t1 for each row set @a:=1; +--echo # Create temporary table which shadows base table with trigger. +create temporary table t1 (j int); +--echo # Dropping of trigger should succeed. +drop trigger t1_bi; +select trigger_name from information_schema.triggers + where event_object_schema = 'test' and event_object_table = 't1'; +--echo # Clean-up. +drop temporary table t1; +drop table t1; + +--echo # +--echo # Bug #12362125: SP INOUT HANDLING IS BROKEN FOR TEXT TYPE. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(c TEXT); + +delimiter |; +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE v TEXT; + SET v = 'aaa'; + SET NEW.c = v; +END| +delimiter ;| + +INSERT INTO t1 VALUES('qazwsxedc'); + +SELECT c FROM t1; + +DROP TABLE t1; + +--echo +--echo End of 5.5 tests. + +--echo # +--echo # BUG #910083: materialized subquery in a trigger +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='materialization=on'; + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); + +CREATE TRIGGER tr AFTER UPDATE ON t1 FOR EACH ROW + UPDATE t2 SET b = (SELECT COUNT(a) FROM t1); + +INSERT INTO t1 + VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); + +INSERT INTO t2 + VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0); + +send + UPDATE t1 SET a = 3; + +connect(con1,localhost,root,,); + SELECT COUNT(*) FROM t1; +disconnect con1; + +connection default; +reap; +SELECT * FROM t2; +UPDATE t1 SET a = 2; +SELECT * FROM t2; + +SET optimizer_switch=@save_optimizer_switch; + +DROP TRIGGER tr; +DROP TABLE t1, t2; + +--echo End of 5.3 tests. + +# +# MDEV-4829 BEFORE INSERT triggers dont issue 1406 error +# Also check timestamp for trigger +# + +set time_zone="+00:00"; +SET TIMESTAMP=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +SET @@session.sql_mode = 'STRICT_ALL_TABLES,STRICT_TRANS_TABLES'; +CREATE TABLE t1 (c CHAR(1) NOT NULL); +DELIMITER |; +CREATE TRIGGER t1_bi + BEFORE INSERT + ON t1 + FOR EACH ROW + BEGIN + SET NEW.c = 'www'; + END; +| +DELIMITER ;| +SET @@session.sql_mode = default; +--error ER_DATA_TOO_LONG +INSERT INTO t1 VALUES ('a'); +show create trigger t1_bi; +DROP TRIGGER t1_bi; +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; +set time_zone= @@global.time_zone; + +--echo # +--echo # MDEV-13936: Server crashes in Time_and_counter_tracker::incr_loops +--echo # + +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1 WHERE RAND() > 0.5; +CREATE TABLE t2 (a int); +CREATE TABLE t3 (a int); + +create trigger trg after insert on t2 for each row + INSERT INTO t3 SELECT MAX(i) FROM v1 UNION SELECT MAX(i) FROM v1; + +drop table t1; + +--error ER_NO_SUCH_TABLE +insert into t2 value (2); +CREATE TABLE t1 (i INT); +insert into t2 value (2); + +DROP VIEW v1; +DROP TABLE t1,t2,t3; + + +--echo End of 10.1 tests. + +# +# MDEV-10915 Count number of executed triggers +# + +create table t1 (i int); +create trigger tr1 after insert on t1 for each row set @a=@a+1; +create trigger tr2 after insert on t1 for each row set @a=@a+1; +create trigger tr3 after insert on t1 for each row set @a=@a+1; +flush status; +show status like 'Executed_triggers'; +set @a=0; +insert into t1 values (1); +show status like 'Executed_triggers'; +select @a; +drop table t1; + +# +# MDEV-10916 In trigger's CREATED time microseconds are misinterpreted +# + +create table t1 (i int); +set time_zone="+0:00"; +SET TIMESTAMP=UNIX_TIMESTAMP('2016-01-01 10:10:10.33'); +select now(2); +create or replace trigger tr1 after insert on t1 for each row set @a=@a+1; +SET TIMESTAMP=UNIX_TIMESTAMP('2016-01-01 10:10:10.99'); +select now(2); +create or replace trigger tr2 after insert on t1 for each row set @a=@a+1; +select now(2); +select trigger_name, action_order, created from information_schema.triggers + where event_object_table = 't1' and trigger_schema='test'; +drop table t1; +set time_zone= @@global.time_zone; + +--echo # MDEV-12992: Increasing memory consumption +--echo with each invocation of trigger +--echo # + +--let $n= 20000 + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (b INT); +CREATE TRIGGER tr + AFTER UPDATE ON t1 FOR EACH ROW SELECT (SELECT b FROM t2) INTO @x; + +--disable_query_log +--echo # Running $n queries +while ($n) +{ + UPDATE t1 SET a = 2; + --dec $n +} +--enable_query_log + +DROP TABLE t1,t2; + + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # + +CREATE TABLE t1 (a INT, b INT, total INT); +DELIMITER $$; +CREATE TRIGGER tr1 BEFORE INSERT ON t1 +FOR EACH ROW +BEGIN + DECLARE va TYPE OF t1.a DEFAULT NEW.a; + DECLARE vb TYPE OF t1.b DEFAULT NEW.b; + SET NEW.total:= va + vb; +END; +$$ +DELIMITER ;$$ +INSERT INTO t1 (a,b) VALUES (10, 20); +SELECT * FROM t1; +DROP TABLE t1; |