1# This test uses chmod, can't be run with root permissions 2-- source include/not_as_root.inc 3--source include/default_charset.inc 4 5# 6# Basic triggers test 7# 8 9--disable_warnings 10drop table if exists t1, t2, t3, t4; 11drop view if exists v1; 12drop database if exists mysqltest; 13drop function if exists f1; 14drop function if exists f2; 15drop procedure if exists p1; 16--enable_warnings 17 18# Create additional connections used through test 19connect (addconroot1, localhost, root,,); 20connect (addconroot2, localhost, root,,); 21# Connection without current database set 22connect (addconwithoutdb, localhost, root,,*NO-ONE*); 23connection default; 24 25create table t1 (i int); 26 27# let us test some very simple trigger 28create trigger trg before insert on t1 for each row set @a:=1; 29set @a:=0; 30select @a; 31insert into t1 values (1); 32select @a; 33drop trigger trg; 34 35# let us test simple trigger reading some values 36create trigger trg before insert on t1 for each row set @a:=new.i; 37insert into t1 values (123); 38select @a; 39drop trigger trg; 40 41drop table t1; 42 43# Let us test before insert trigger 44# Such triggers can be used for setting complex default values 45create table t1 (i int not null, j int); 46delimiter |; 47create trigger trg before insert on t1 for each row 48begin 49 if isnull(new.j) then 50 set new.j:= new.i * 10; 51 end if; 52end| 53insert into t1 (i) values (1)| 54insert into t1 (i,j) values (2, 3)| 55select * from t1| 56drop trigger trg| 57drop table t1| 58delimiter ;| 59 60# After insert trigger 61# Useful for aggregating data 62create table t1 (i int not null primary key); 63create trigger trg after insert on t1 for each row 64 set @a:= if(@a,concat(@a, ":", new.i), new.i); 65set @a:=""; 66insert into t1 values (2),(3),(4),(5); 67select @a; 68drop trigger trg; 69drop table t1; 70 71# PS doesn't work with multi-row statements 72--disable_ps_protocol 73# Before update trigger 74# (In future we will achieve this via proper error handling in triggers) 75create table t1 (aid int not null primary key, balance int not null default 0); 76insert into t1 values (1, 1000), (2,3000); 77delimiter |; 78create trigger trg before update on t1 for each row 79begin 80 declare loc_err varchar(255); 81 if abs(new.balance - old.balance) > 1000 then 82 set new.balance:= old.balance; 83 set loc_err := concat("Too big change for aid = ", new.aid); 84 set @update_failed:= if(@update_failed, concat(@a, ":", loc_err), loc_err); 85 end if; 86end| 87set @update_failed:=""| 88update t1 set balance=1500| 89select @update_failed; 90select * from t1| 91drop trigger trg| 92drop table t1| 93delimiter ;| 94--enable_ps_protocol 95 96# After update trigger 97create table t1 (i int); 98insert into t1 values (1),(2),(3),(4); 99create trigger trg after update on t1 for each row 100 set @total_change:=@total_change + new.i - old.i; 101set @total_change:=0; 102update t1 set i=3; 103select @total_change; 104drop trigger trg; 105drop table t1; 106 107# Before delete trigger 108# This can be used for aggregation too :) 109create table t1 (i int); 110insert into t1 values (1),(2),(3),(4); 111create trigger trg before delete on t1 for each row 112 set @del_sum:= @del_sum + old.i; 113set @del_sum:= 0; 114delete from t1 where i <= 3; 115select @del_sum; 116drop trigger trg; 117drop table t1; 118 119# After delete trigger. 120# Just run out of imagination. 121create table t1 (i int); 122insert into t1 values (1),(2),(3),(4); 123create trigger trg after delete on t1 for each row set @del:= 1; 124set @del:= 0; 125delete from t1 where i <> 0; 126select @del; 127drop trigger trg; 128drop table t1; 129 130# Several triggers on one table 131create table t1 (i int, j int); 132 133delimiter |; 134create trigger trg1 before insert on t1 for each row 135begin 136 if new.j > 10 then 137 set new.j := 10; 138 end if; 139end| 140create trigger trg2 before update on t1 for each row 141begin 142 if old.i % 2 = 0 then 143 set new.j := -1; 144 end if; 145end| 146create trigger trg3 after update on t1 for each row 147begin 148 if new.j = -1 then 149 set @fired:= "Yes"; 150 end if; 151end| 152delimiter ;| 153set @fired:=""; 154insert into t1 values (1,2),(2,3),(3,14); 155select @fired; 156select * from t1; 157update t1 set j= 20; 158select @fired; 159select * from t1; 160 161drop trigger trg1; 162drop trigger trg2; 163drop trigger trg3; 164drop table t1; 165 166 167# Let us test how triggers work for special forms of INSERT such as 168# REPLACE and INSERT ... ON DUPLICATE KEY UPDATE 169create table t1 (id int not null primary key, data int); 170create trigger t1_bi before insert on t1 for each row 171 set @log:= concat(@log, "(BEFORE_INSERT: new=(id=", new.id, ", data=", new.data,"))"); 172create trigger t1_ai after insert on t1 for each row 173 set @log:= concat(@log, "(AFTER_INSERT: new=(id=", new.id, ", data=", new.data,"))"); 174create trigger t1_bu before update on t1 for each row 175 set @log:= concat(@log, "(BEFORE_UPDATE: old=(id=", old.id, ", data=", old.data, 176 ") new=(id=", new.id, ", data=", new.data,"))"); 177create trigger t1_au after update on t1 for each row 178 set @log:= concat(@log, "(AFTER_UPDATE: old=(id=", old.id, ", data=", old.data, 179 ") new=(id=", new.id, ", data=", new.data,"))"); 180create trigger t1_bd before delete on t1 for each row 181 set @log:= concat(@log, "(BEFORE_DELETE: old=(id=", old.id, ", data=", old.data,"))"); 182create trigger t1_ad after delete on t1 for each row 183 set @log:= concat(@log, "(AFTER_DELETE: old=(id=", old.id, ", data=", old.data,"))"); 184# Simple INSERT - both triggers should be called 185set @log:= ""; 186insert into t1 values (1, 1); 187select @log; 188# INSERT IGNORE for already existing key - only before trigger should fire 189set @log:= ""; 190insert ignore t1 values (1, 2); 191select @log; 192# INSERT ... ON DUPLICATE KEY UPDATE ... 193set @log:= ""; 194insert into t1 (id, data) values (1, 3), (2, 2) on duplicate key update data= data + 1; 195select @log; 196# REPLACE (also test for bug#13479 "REPLACE activates UPDATE trigger, 197# not the DELETE and INSERT triggers") 198# We define REPLACE as INSERT which DELETEs old rows which conflict with 199# row being inserted. So for the first record in statement below we will 200# call before insert trigger, then delete will be executed (and both delete 201# triggers should fire). Finally after insert trigger will be called. 202# For the second record we will just call both on insert triggers. 203set @log:= ""; 204replace t1 values (1, 4), (3, 3); 205select @log; 206# Now we will drop ON DELETE triggers to test REPLACE which is internally 207# executed via update 208drop trigger t1_bd; 209drop trigger t1_ad; 210set @log:= ""; 211replace t1 values (1, 5); 212select @log; 213 214# This also drops associated triggers 215drop table t1; 216 217 218# 219# Let us test triggers which access other tables. 220# 221# Trivial trigger which inserts data into another table 222create table t1 (id int primary key, data varchar(10), fk int); 223create table t2 (event varchar(100)); 224create table t3 (id int primary key); 225create trigger t1_ai after insert on t1 for each row 226 insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "'")); 227insert into t1 (id, data) values (1, "one"), (2, "two"); 228select * from t1; 229select * from t2; 230drop trigger t1_ai; 231# Trigger which uses couple of tables (and partially emulates FK constraint) 232delimiter |; 233create trigger t1_bi before insert on t1 for each row 234begin 235 if exists (select id from t3 where id=new.fk) then 236 insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "' fk=", new.fk)); 237 else 238 insert into t2 values (concat("INSERT INTO t1 FAILED id=", new.id, " data='", new.data, "' fk=", new.fk)); 239 set new.id= NULL; 240 end if; 241end| 242delimiter ;| 243insert into t3 values (1); 244insert into t1 values (4, "four", 1), (5, "five", 2); 245select * from t1; 246select * from t2; 247drop table t1, t2, t3; 248# Trigger which invokes function 249create table t1 (id int primary key, data varchar(10)); 250create table t2 (seq int); 251insert into t2 values (10); 252create function f1 () returns int return (select max(seq) from t2); 253delimiter |; 254create trigger t1_bi before insert on t1 for each row 255begin 256 if new.id > f1() then 257 set new.id:= f1(); 258 end if; 259end| 260delimiter ;| 261insert into t1 values (1, "first"); 262insert into t1 values (f1(), "max"); 263select * from t1; 264drop table t1, t2; 265drop function f1; 266# Trigger which forces invocation of another trigger 267# (emulation of FK on delete cascade policy) 268create table t1 (id int primary key, fk_t2 int); 269create table t2 (id int primary key, fk_t3 int); 270create table t3 (id int primary key); 271insert into t1 values (1,1), (2,1), (3,2); 272insert into t2 values (1,1), (2,2); 273insert into t3 values (1), (2); 274create trigger t3_ad after delete on t3 for each row 275 delete from t2 where fk_t3=old.id; 276create trigger t2_ad after delete on t2 for each row 277 delete from t1 where fk_t2=old.id; 278delete from t3 where id = 1; 279select * from t1 left join (t2 left join t3 on t2.fk_t3 = t3.id) on t1.fk_t2 = t2.id; 280drop table t1, t2, t3; 281# Trigger which assigns value selected from table to field of row 282# being inserted/updated. 283create table t1 (id int primary key, copy int); 284create table t2 (id int primary key, data int); 285insert into t2 values (1,1), (2,2); 286create trigger t1_bi before insert on t1 for each row 287 set new.copy= (select data from t2 where id = new.id); 288create trigger t1_bu before update on t1 for each row 289 set new.copy= (select data from t2 where id = new.id); 290insert into t1 values (1,3), (2,4), (3,3); 291update t1 set copy= 1 where id = 2; 292select * from t1; 293drop table t1, t2; 294 295# 296# Test of wrong column specifiers in triggers 297# 298create table t1 (i int); 299create table t3 (i int); 300 301--error ER_TRG_NO_SUCH_ROW_IN_TRG 302create trigger trg before insert on t1 for each row set @a:= old.i; 303--error ER_TRG_NO_SUCH_ROW_IN_TRG 304create trigger trg before delete on t1 for each row set @a:= new.i; 305--error ER_TRG_CANT_CHANGE_ROW 306create trigger trg before update on t1 for each row set old.i:=1; 307--error ER_TRG_NO_SUCH_ROW_IN_TRG 308create trigger trg before delete on t1 for each row set new.i:=1; 309--error ER_TRG_CANT_CHANGE_ROW 310create trigger trg after update on t1 for each row set new.i:=1; 311--error ER_BAD_FIELD_ERROR 312create trigger trg before update on t1 for each row set new.j:=1; 313--error ER_BAD_FIELD_ERROR 314create trigger trg before update on t1 for each row set @a:=old.j; 315 316 317# 318# Let us test various trigger creation errors 319# Also quickly test table namespace (bug#5892/6182) 320# 321--error ER_NO_SUCH_TABLE 322create trigger trg before insert on t2 for each row set @a:=1; 323 324create trigger trg before insert on t1 for each row set @a:=1; 325--error ER_TRG_ALREADY_EXISTS 326create trigger trg after insert on t1 for each row set @a:=1; 327create trigger trg2 before insert on t1 for each row set @a:=1; 328drop trigger trg2; 329--error ER_TRG_ALREADY_EXISTS 330create trigger trg before insert on t3 for each row set @a:=1; 331create trigger trg2 before insert on t3 for each row set @a:=1; 332drop trigger trg2; 333drop trigger trg; 334 335--error ER_TRG_DOES_NOT_EXIST 336drop trigger trg; 337 338create view v1 as select * from t1; 339--error ER_WRONG_OBJECT 340create trigger trg before insert on v1 for each row set @a:=1; 341drop view v1; 342 343drop table t1; 344drop table t3; 345 346create temporary table t1 (i int); 347--error ER_TRG_ON_VIEW_OR_TEMP_TABLE 348create trigger trg before insert on t1 for each row set @a:=1; 349drop table t1; 350 351 352 353# 354# Tests for various trigger-related bugs 355# 356 357# Test for bug #5887 "Triggers with string literals cause errors". 358# New .FRM parser was not handling escaped strings properly. 359create table t1 (x1col char); 360create trigger tx1 before insert on t1 for each row set new.x1col = 'x'; 361insert into t1 values ('y'); 362drop trigger tx1; 363drop table t1; 364 365# 366# Test for bug #5890 "Triggers fail for DELETE without WHERE". 367# If we are going to delete all rows in table but DELETE triggers exist 368# we should perform row-by-row deletion instead of using optimized 369# delete_all_rows() method. 370# 371create table t1 (i int) engine=myisam; 372insert into t1 values (1), (2); 373create trigger trg1 before delete on t1 for each row set @del_before:= @del_before + old.i; 374create trigger trg2 after delete on t1 for each row set @del_after:= @del_after + old.i; 375set @del_before:=0, @del_after:= 0; 376delete from t1; 377select @del_before, @del_after; 378drop trigger trg1; 379drop trigger trg2; 380drop table t1; 381 382# Test for bug #5859 "DROP TABLE does not drop triggers". Trigger should not 383# magically reappear when we recreate dropped table. 384create table t1 (a int); 385create trigger trg1 before insert on t1 for each row set new.a= 10; 386drop table t1; 387create table t1 (a int); 388insert into t1 values (); 389select * from t1; 390drop table t1; 391 392# Test for bug #6559 "DROP DATABASE forgets to drop triggers". 393create database mysqltest; 394use mysqltest; 395create table t1 (i int); 396create trigger trg1 before insert on t1 for each row set @a:= 1; 397# This should succeed 398drop database mysqltest; 399use test; 400 401# Test for bug #8791 402# "Triggers: Allowed to create triggers on a subject table in a different DB". 403create database mysqltest; 404create table mysqltest.t1 (i int); 405--error ER_TRG_IN_WRONG_SCHEMA 406create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1; 407use mysqltest; 408--error ER_NO_SUCH_TABLE 409create trigger test.trg1 before insert on t1 for each row set @a:= 1; 410drop database mysqltest; 411use test; 412 413 414# Test for bug #5860 "Multi-table UPDATE does not activate update triggers" 415# We will also test how delete triggers wor for multi-table DELETE. 416create table t1 (i int, j int default 10, k int not null, key (k)); 417create table t2 (i int); 418insert into t1 (i, k) values (1, 1); 419insert into t2 values (1); 420create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j; 421create trigger trg2 after update on t1 for each row set @b:= "Fired"; 422set @a:= 0, @b:= ""; 423# Check that trigger works in case of update on the fly 424update t1, t2 set j = j + 10 where t1.i = t2.i; 425select @a, @b; 426insert into t1 values (2, 13, 2); 427insert into t2 values (2); 428set @a:= 0, @b:= ""; 429# And now let us check that triggers work in case of multi-update which 430# is done through temporary tables... 431update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2; 432select @a, @b; 433# Let us test delete triggers for multi-delete now. 434# We create triggers for both tables because we want test how they 435# work in both on-the-fly and via-temp-tables cases. 436create trigger trg3 before delete on t1 for each row set @c:= @c + old.j; 437create trigger trg4 before delete on t2 for each row set @d:= @d + old.i; 438create trigger trg5 after delete on t1 for each row set @e:= "After delete t1 fired"; 439create trigger trg6 after delete on t2 for each row set @f:= "After delete t2 fired"; 440set @c:= 0, @d:= 0, @e:= "", @f:= ""; 441delete t1, t2 from t1, t2 where t1.i = t2.i; 442select @c, @d, @e, @f; 443# This also will drop triggers 444drop table t1, t2; 445 446# Test for bug #6812 "Triggers are not activated for INSERT ... SELECT". 447# (We also check the fact that trigger modifies some field does not affect 448# value of next record inserted). 449delimiter |; 450create table t1 (i int, j int default 10)| 451create table t2 (i int)| 452insert into t2 values (1), (2)| 453create trigger trg1 before insert on t1 for each row 454begin 455 if new.i = 1 then 456 set new.j := 1; 457 end if; 458end| 459create trigger trg2 after insert on t1 for each row set @a:= 1| 460set @a:= 0| 461insert into t1 (i) select * from t2| 462select * from t1| 463select @a| 464# This also will drop triggers 465drop table t1, t2| 466delimiter ;| 467 468# Test for bug #8755 "Trigger is not activated by LOAD DATA" 469create table t1 (i int, j int, k int); 470create trigger trg1 before insert on t1 for each row set new.k = new.i; 471create trigger trg2 after insert on t1 for each row set @b:= "Fired"; 472set @b:=""; 473# Test triggers with file with separators 474load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, i); 475select *, @b from t1; 476set @b:=""; 477# Test triggers with fixed size row file 478load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j); 479select *, @b from t1; 480# This also will drop triggers 481drop table t1; 482 483# Test for bug #5894 "Triggers with altered tables cause corrupt databases" 484# Also tests basic error handling for various kinds of triggers. 485create table t1 (i int, at int, k int, key(k)) engine=myisam; 486create table t2 (i int); 487insert into t1 values (1, 1, 1); 488# We need at least 3 elements in t2 to test multi-update properly 489insert into t2 values (1), (2), (3); 490# Create and then break "after" triggers 491create trigger ai after insert on t1 for each row set @a:= new.at; 492create trigger au after update on t1 for each row set @a:= new.at; 493create trigger ad after delete on t1 for each row set @a:= old.at; 494alter table t1 drop column at; 495# We still should be able select data from tables. 496select * from t1; 497# The following statements changing t1 should fail, but still cause 498# their main effect. This is because operation on the table row is 499# executed before "after" trigger and its effect cannot be rolled back 500# when whole statement fails, because t1 is MyISAM table. 501--error ER_BAD_FIELD_ERROR 502insert into t1 values (2, 1); 503select * from t1; 504--error ER_BAD_FIELD_ERROR 505update t1 set k = 2 where i = 2; 506select * from t1; 507--error ER_BAD_FIELD_ERROR 508delete from t1 where i = 2; 509select * from t1; 510# Should fail and insert only 1 row 511--error ER_BAD_FIELD_ERROR 512load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k); 513select * from t1; 514--error ER_BAD_FIELD_ERROR 515insert into t1 select 3, 3; 516select * from t1; 517# Multi-update working on the fly, again it will update only 518# one row even if more matches 519--error ER_BAD_FIELD_ERROR 520update t1, t2 set k = k + 10 where t1.i = t2.i; 521select * from t1; 522# The same for multi-update via temp table 523--error ER_BAD_FIELD_ERROR 524update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3; 525select * from t1; 526# Multi-delete on the fly 527--error ER_BAD_FIELD_ERROR 528delete t1, t2 from t1 straight_join t2 where t1.i = t2.i; 529select * from t1; 530# And via temporary storage 531--error ER_BAD_FIELD_ERROR 532delete t2, t1 from t2 straight_join t1 where t1.i = t2.i; 533select * from t1; 534# Prepare table for testing of REPLACE and INSERT ... ON DUPLICATE KEY UPDATE 535alter table t1 add primary key (i); 536--error ER_BAD_FIELD_ERROR 537insert into t1 values (3, 4) on duplicate key update k= k + 10; 538select * from t1; 539# The following statement will delete old row and won't 540# insert new one since after delete trigger will fail. 541--error ER_BAD_FIELD_ERROR 542replace into t1 values (3, 3); 543select * from t1; 544# Also drops all triggers 545drop table t1, t2; 546 547create table t1 (i int, bt int, k int, key(k)) engine=myisam; 548create table t2 (i int); 549insert into t1 values (1, 1, 1), (2, 2, 2); 550insert into t2 values (1), (2), (3); 551# Create and then break "before" triggers 552create trigger bi before insert on t1 for each row set @a:= new.bt; 553create trigger bu before update on t1 for each row set @a:= new.bt; 554create trigger bd before delete on t1 for each row set @a:= old.bt; 555alter table t1 drop column bt; 556# The following statements changing t1 should fail and should not 557# cause any effect on table, since "before" trigger is executed 558# before operation on the table row. 559--error ER_BAD_FIELD_ERROR 560insert into t1 values (3, 3); 561select * from t1; 562--error ER_BAD_FIELD_ERROR 563update t1 set i = 2; 564select * from t1; 565--error ER_BAD_FIELD_ERROR 566delete from t1; 567select * from t1; 568--error ER_BAD_FIELD_ERROR 569load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k); 570select * from t1; 571--error ER_BAD_FIELD_ERROR 572insert into t1 select 3, 3; 573select * from t1; 574# Both types of multi-update (on the fly and via temp table) 575--error ER_BAD_FIELD_ERROR 576update t1, t2 set k = k + 10 where t1.i = t2.i; 577select * from t1; 578--error ER_BAD_FIELD_ERROR 579update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2; 580select * from t1; 581# Both types of multi-delete 582--error ER_BAD_FIELD_ERROR 583delete t1, t2 from t1 straight_join t2 where t1.i = t2.i; 584select * from t1; 585--error ER_BAD_FIELD_ERROR 586delete t2, t1 from t2 straight_join t1 where t1.i = t2.i; 587select * from t1; 588# Let us test REPLACE/INSERT ... ON DUPLICATE KEY UPDATE. 589# To test properly code-paths different from those that are used 590# in ordinary INSERT we need to drop "before insert" trigger. 591alter table t1 add primary key (i); 592drop trigger bi; 593--error ER_BAD_FIELD_ERROR 594insert into t1 values (2, 4) on duplicate key update k= k + 10; 595select * from t1; 596--error ER_BAD_FIELD_ERROR 597replace into t1 values (2, 4); 598select * from t1; 599# Also drops all triggers 600drop table t1, t2; 601 602# Test for bug #5893 "Triggers with dropped functions cause crashes" 603# Appropriate error should be reported instead of crash. 604# Also test for bug #11889 "Server crashes when dropping trigger 605# using stored routine". 606--disable_warnings 607drop function if exists bug5893; 608--enable_warnings 609create table t1 (col1 int, col2 int); 610insert into t1 values (1, 2); 611create function bug5893 () returns int return 5; 612create trigger t1_bu before update on t1 for each row set new.col1= bug5893(); 613drop function bug5893; 614--error ER_SP_DOES_NOT_EXIST 615update t1 set col2 = 4; 616# This should not crash server too. 617drop trigger t1_bu; 618drop table t1; 619 620# 621# storing and restoring parsing modes for triggers (BUG#5891) 622# 623set sql_mode='ansi'; 624create table t1 ("t1 column" int); 625create trigger t1_bi before insert on t1 for each row set new."t1 column" = 5; 626set sql_mode=""; 627insert into t1 values (0); 628# create trigger with different sql_mode 629create trigger t1_af after insert on t1 for each row set @a=10; 630insert into t1 values (0); 631select * from t1; 632select @a; 633--replace_column 6 # 634show triggers; 635drop table t1; 636# check that rigger preserve sql_mode during execution 637set sql_mode="traditional"; 638create table t1 (a date); 639-- error 1292 640insert into t1 values ('2004-01-00'); 641set sql_mode=""; 642create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00'; 643set sql_mode="traditional"; 644insert into t1 values ('2004-01-01'); 645select * from t1; 646set sql_mode=default; 647show create table t1; 648--replace_column 6 # 649show triggers; 650drop table t1; 651 652# Test for bug #12280 "Triggers: crash if flush tables" 653# FLUSH TABLES and FLUSH PRIVILEGES should be disallowed inside 654# of functions and triggers. 655create table t1 (id int); 656--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 657create trigger t1_ai after insert on t1 for each row reset query cache; 658--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 659create trigger t1_ai after insert on t1 for each row reset master; 660--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 661create trigger t1_ai after insert on t1 for each row reset slave; 662--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 663create trigger t1_ai after insert on t1 for each row flush hosts; 664--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 665create trigger t1_ai after insert on t1 for each row flush tables with read lock; 666--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 667create trigger t1_ai after insert on t1 for each row flush logs; 668--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 669create trigger t1_ai after insert on t1 for each row flush status; 670--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 671create trigger t1_ai after insert on t1 for each row flush slave; 672--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 673create trigger t1_ai after insert on t1 for each row flush master; 674--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 675create trigger t1_ai after insert on t1 for each row flush des_key_file; 676--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 677create trigger t1_ai after insert on t1 for each row flush user_resources; 678--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 679create trigger t1_ai after insert on t1 for each row flush tables; 680--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 681create trigger t1_ai after insert on t1 for each row flush privileges; 682--disable_warnings 683drop procedure if exists p1; 684--enable_warnings 685 686create trigger t1_ai after insert on t1 for each row call p1(); 687create procedure p1() flush tables; 688--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 689insert into t1 values (0); 690 691drop procedure p1; 692create procedure p1() reset query cache; 693--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 694insert into t1 values (0); 695 696drop procedure p1; 697create procedure p1() reset master; 698--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 699insert into t1 values (0); 700 701drop procedure p1; 702create procedure p1() reset slave; 703--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 704insert into t1 values (0); 705 706drop procedure p1; 707create procedure p1() flush hosts; 708--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 709insert into t1 values (0); 710 711drop procedure p1; 712create procedure p1() flush privileges; 713--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 714insert into t1 values (0); 715 716drop procedure p1; 717create procedure p1() flush tables with read lock; 718--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 719insert into t1 values (0); 720 721drop procedure p1; 722create procedure p1() flush tables; 723--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 724insert into t1 values (0); 725 726drop procedure p1; 727create procedure p1() flush logs; 728--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 729insert into t1 values (0); 730 731drop procedure p1; 732create procedure p1() flush status; 733--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 734insert into t1 values (0); 735 736drop procedure p1; 737create procedure p1() flush slave; 738--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 739insert into t1 values (0); 740 741drop procedure p1; 742create procedure p1() flush master; 743--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 744insert into t1 values (0); 745 746drop procedure p1; 747create procedure p1() flush des_key_file; 748--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 749insert into t1 values (0); 750 751drop procedure p1; 752create procedure p1() flush user_resources; 753--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 754insert into t1 values (0); 755 756drop procedure p1; 757drop table t1; 758 759# Test for bug #11973 "SELECT .. INTO var_name; in trigger cause 760# crash on update" 761 762create table t1 (id int, data int, username varchar(16)); 763insert into t1 (id, data) values (1, 0); 764delimiter |; 765create trigger t1_whoupdated before update on t1 for each row 766begin 767 declare user varchar(32); 768 declare i int; 769 select user() into user; 770 set NEW.username = user; 771 select count(*) from ((select 1) union (select 2)) as d1 into i; 772end| 773delimiter ;| 774update t1 set data = 1; 775 776connection addconroot1; 777update t1 set data = 2; 778 779connection default; 780drop table t1; 781 782# 783# #11587 Trigger causes lost connection error 784# 785 786create table t1 (c1 int, c2 datetime); 787delimiter |; 788--error ER_SP_NO_RETSET 789create trigger tr1 before insert on t1 for each row 790begin 791 set new.c2= '2004-04-01'; 792 select 'hello'; 793end| 794delimiter ;| 795 796insert into t1 (c1) values (1),(2),(3); 797select * from t1; 798 799--disable_warnings 800drop procedure if exists bug11587; 801--enable_warnings 802 803delimiter |; 804create procedure bug11587(x char(16)) 805begin 806 select "hello"; 807 select "hello again"; 808end| 809 810create trigger tr1 before insert on t1 for each row 811begin 812 call bug11587(); 813 set new.c2= '2004-04-02'; 814end| 815delimiter ;| 816 817--error ER_SP_NO_RETSET 818insert into t1 (c1) values (4),(5),(6); 819select * from t1; 820 821drop procedure bug11587; 822drop table t1; 823 824# Test for bug #11896 "Partial locking in case of recursive trigger 825# definitions". Recursion in triggers should not be allowed. 826# We also should not allow to change tables which are used in 827# statements invoking this trigger. 828create table t1 (f1 integer); 829create table t2 (f2 integer); 830create trigger t1_ai after insert on t1 831 for each row insert into t2 values (new.f1+1); 832create trigger t2_ai after insert on t2 833 for each row insert into t1 values (new.f2+1); 834# Allow SP resursion to be show that it has not influence here 835set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth; 836set @@max_sp_recursion_depth=100; 837--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG 838insert into t1 values (1); 839set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS; 840select * from t1; 841select * from t2; 842drop trigger t1_ai; 843drop trigger t2_ai; 844create trigger t1_bu before update on t1 845 for each row insert into t1 values (2); 846--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG 847update t1 set f1= 10; 848select * from t1; 849drop trigger t1_bu; 850create trigger t1_bu before update on t1 851 for each row delete from t1 where f1=new.f1; 852--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG 853update t1 set f1= 10; 854select * from t1; 855drop trigger t1_bu; 856# This should work tough 857create trigger t1_bi before insert on t1 858 for each row set new.f1=(select sum(f1) from t1); 859insert into t1 values (3); 860select * from t1; 861drop trigger t1_bi; 862drop tables t1, t2; 863 864# Tests for bug #12704 "Server crashes during trigger execution". 865# If we run DML statements and CREATE TRIGGER statements concurrently 866# it may happen that trigger will be created while DML statement is 867# waiting for table lock. In this case we have to reopen tables and 868# recalculate prelocking set. 869# Unfortunately these tests rely on the order in which tables are locked 870# by statement so they are non determenistic and are disabled. 871--disable_parsing 872create table t1 (id int); 873create table t2 (id int); 874create table t3 (id int); 875create function f1() returns int return (select max(id)+2 from t2); 876create view v1 as select f1() as f; 877 878# Let us check that we notice trigger at all 879connection addconroot1; 880lock tables t2 write; 881connection default; 882send insert into t1 values ((select max(id) from t2)), (2); 883--sleep 1 884connection addconroot2; 885create trigger t1_trg before insert on t1 for each row set NEW.id:= 1; 886connection addconroot1; 887unlock tables; 888connection default; 889reap; 890select * from t1; 891 892# Check that we properly calculate new prelocking set 893insert into t2 values (3); 894connection addconroot1; 895lock tables t2 write; 896connection default; 897send insert into t1 values ((select max(id) from t2)), (4); 898--sleep 1 899connection addconroot2; 900drop trigger t1_trg; 901create trigger t1_trg before insert on t1 for each row 902 insert into t3 values (new.id); 903connection addconroot1; 904unlock tables; 905connection default; 906reap; 907select * from t1; 908select * from t3; 909 910# We should be able to do this even if fancy views are involved 911connection addconroot1; 912lock tables t2 write; 913connection default; 914send insert into t1 values ((select max(f) from v1)), (6); 915--sleep 1 916connection addconroot2; 917drop trigger t1_trg; 918create trigger t1_trg before insert on t1 for each row 919 insert into t3 values (new.id + 100); 920connection addconroot1; 921unlock tables; 922connection default; 923reap; 924select * from t1; 925select * from t3; 926 927# This also should work for multi-update 928# Let us drop trigger to demonstrate that prelocking set is really 929# rebuilt 930drop trigger t1_trg; 931connection addconroot1; 932lock tables t2 write; 933connection default; 934send update t1, t2 set t1.id=10 where t1.id=t2.id; 935--sleep 1 936connection addconroot2; 937create trigger t1_trg before update on t1 for each row 938 insert into t3 values (new.id); 939connection addconroot1; 940unlock tables; 941connection default; 942reap; 943select * from t1; 944select * from t3; 945 946# And even for multi-update converted from ordinary update thanks to view 947drop view v1; 948drop trigger t1_trg; 949create view v1 as select t1.id as id1 from t1, t2 where t1.id= t2.id; 950insert into t2 values (10); 951connection addconroot1; 952lock tables t2 write; 953connection default; 954send update v1 set id1= 11; 955--sleep 1 956connection addconroot2; 957create trigger t1_trg before update on t1 for each row 958 insert into t3 values (new.id + 100); 959connection addconroot1; 960unlock tables; 961connection default; 962reap; 963select * from t1; 964select * from t3; 965 966drop function f1; 967drop view v1; 968drop table t1, t2, t3; 969--enable_parsing 970 971# 972# Test for bug #13399 "Crash when executing PS/SP which should activate 973# trigger which is now dropped". See also test for similar bug for stored 974# routines in sp-error.test (#12329). 975create table t1 (id int); 976create table t2 (id int); 977create trigger t1_bi before insert on t1 for each row insert into t2 values (new.id); 978prepare stmt1 from "insert into t1 values (10)"; 979create procedure p1() insert into t1 values (10); 980call p1(); 981# Actually it is enough to do FLUSH TABLES instead of DROP TRIGGER 982drop trigger t1_bi; 983# Server should not crash on these two statements 984execute stmt1; 985call p1(); 986deallocate prepare stmt1; 987drop procedure p1; 988 989# Let us test more complex situation when we alter trigger in such way that 990# it uses different set of tables (or simply add new trigger). 991create table t3 (id int); 992create trigger t1_bi after insert on t1 for each row insert into t2 values (new.id); 993prepare stmt1 from "insert into t1 values (10)"; 994create procedure p1() insert into t1 values (10); 995call p1(); 996# Altering trigger forcing it use different set of tables 997drop trigger t1_bi; 998create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id); 999execute stmt1; 1000call p1(); 1001deallocate prepare stmt1; 1002drop procedure p1; 1003drop table t1, t2, t3; 1004 1005# 1006# BUG#13549 "Server crash with nested stored procedures". 1007# Server should not crash when during execution of stored procedure 1008# we have to parse trigger/function definition and this new trigger/ 1009# function has more local variables declared than invoking stored 1010# procedure and last of these variables is used in argument of NOT 1011# operator. 1012# 1013create table t1 (a int); 1014DELIMITER //; 1015CREATE PROCEDURE `p1`() 1016begin 1017 insert into t1 values (1); 1018end// 1019create trigger trg before insert on t1 for each row 1020begin 1021 declare done int default 0; 1022 set done= not done; 1023end// 1024DELIMITER ;// 1025CALL p1(); 1026drop procedure p1; 1027drop table t1; 1028 1029# 1030# Test for bug #14863 "Triggers: crash if create and there is no current 1031# database". We should not crash and give proper error when database for 1032# trigger or its table is not specified and there is no current database. 1033# 1034connection addconwithoutdb; 1035--error ER_NO_DB_ERROR 1036create trigger t1_bi before insert on test.t1 for each row set @a:=0; 1037--error ER_NO_SUCH_TABLE 1038create trigger test.t1_bi before insert on t1 for each row set @a:=0; 1039--error ER_NO_DB_ERROR 1040drop trigger t1_bi; 1041connection default; 1042 1043# 1044# Tests for bug #13525 "Rename table does not keep info of triggers" 1045# and bug #17866 "Problem with renaming table with triggers with fully 1046# qualified subject table". 1047# 1048create table t1 (id int); 1049create trigger t1_bi before insert on t1 for each row set @a:=new.id; 1050create trigger t1_ai after insert on test.t1 for each row set @b:=new.id; 1051insert into t1 values (101); 1052select @a, @b; 1053select trigger_schema, trigger_name, event_object_schema, 1054 event_object_table, action_statement from information_schema.triggers 1055 where event_object_schema = 'test'; 1056rename table t1 to t2; 1057# Trigger should work after rename 1058insert into t2 values (102); 1059select @a, @b; 1060select trigger_schema, trigger_name, event_object_schema, 1061 event_object_table, action_statement from information_schema.triggers 1062 where event_object_schema = 'test'; 1063# Let us check that the same works for simple ALTER TABLE ... RENAME 1064alter table t2 rename to t3; 1065insert into t3 values (103); 1066select @a, @b; 1067select trigger_schema, trigger_name, event_object_schema, 1068 event_object_table, action_statement from information_schema.triggers 1069 where event_object_schema = 'test'; 1070# And for more complex ALTER TABLE 1071alter table t3 rename to t4, add column val int default 0; 1072insert into t4 values (104, 1); 1073select @a, @b; 1074select trigger_schema, trigger_name, event_object_schema, 1075 event_object_table, action_statement from information_schema.triggers 1076 where event_object_schema = 'test'; 1077# .TRN file should be updated with new table name 1078drop trigger t1_bi; 1079drop trigger t1_ai; 1080drop table t4; 1081# Rename between different databases if triggers exist should fail 1082create database mysqltest; 1083use mysqltest; 1084create table t1 (id int); 1085create trigger t1_bi before insert on t1 for each row set @a:=new.id; 1086insert into t1 values (101); 1087select @a; 1088select trigger_schema, trigger_name, event_object_schema, 1089 event_object_table, action_statement from information_schema.triggers 1090 where event_object_schema = 'test' or event_object_schema = 'mysqltest'; 1091--error ER_TRG_IN_WRONG_SCHEMA 1092rename table t1 to test.t2; 1093insert into t1 values (102); 1094select @a; 1095select trigger_schema, trigger_name, event_object_schema, 1096 event_object_table, action_statement from information_schema.triggers 1097 where event_object_schema = 'test' or event_object_schema = 'mysqltest'; 1098# There should be no fantom .TRN files 1099--error ER_TRG_DOES_NOT_EXIST 1100drop trigger test.t1_bi; 1101# Let us also check handling of this restriction in ALTER TABLE ... RENAME 1102--error ER_TRG_IN_WRONG_SCHEMA 1103alter table t1 rename to test.t1; 1104insert into t1 values (103); 1105select @a; 1106select trigger_schema, trigger_name, event_object_schema, 1107 event_object_table, action_statement from information_schema.triggers 1108 where event_object_schema = 'test' or event_object_schema = 'mysqltest'; 1109# Again there should be no fantom .TRN files 1110--error ER_TRG_DOES_NOT_EXIST 1111drop trigger test.t1_bi; 1112--error ER_TRG_IN_WRONG_SCHEMA 1113alter table t1 rename to test.t1, add column val int default 0; 1114insert into t1 values (104); 1115select @a; 1116select trigger_schema, trigger_name, event_object_schema, 1117 event_object_table, action_statement from information_schema.triggers 1118 where event_object_schema = 'test' or event_object_schema = 'mysqltest'; 1119# Table definition should not change 1120show create table t1; 1121# And once again check for fantom .TRN files 1122--error ER_TRG_DOES_NOT_EXIST 1123drop trigger test.t1_bi; 1124drop trigger t1_bi; 1125drop table t1; 1126drop database mysqltest; 1127use test; 1128# And now let us check that the properly handle rename if there is some 1129# error during it (that we rollback such renames completely). 1130create table t1 (id int); 1131create trigger t1_bi before insert on t1 for each row set @a:=new.id; 1132create trigger t1_ai after insert on t1 for each row set @b:=new.id; 1133insert into t1 values (101); 1134select @a, @b; 1135select trigger_schema, trigger_name, event_object_schema, 1136 event_object_table, action_statement from information_schema.triggers 1137 where event_object_schema = 'test'; 1138# Trick which makes update of second .TRN file impossible 1139let $MYSQLD_DATADIR= `select @@datadir`; 1140write_file $MYSQLD_DATADIR/test/t1_ai.TRN~; 1141dummy 1142EOF 1143chmod 0000 $MYSQLD_DATADIR/test/t1_ai.TRN~; 1144# Normalize the datadir path; the embedded server doesn't chdir to datadir 1145--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' 1146--error 1 1147rename table t1 to t2; 1148# 't1' should be still there and triggers should work correctly 1149insert into t1 values (102); 1150select @a, @b; 1151select trigger_schema, trigger_name, event_object_schema, 1152 event_object_table, action_statement from information_schema.triggers 1153 where event_object_schema = 'test'; 1154chmod 0600 $MYSQLD_DATADIR/test/t1_ai.TRN~; 1155remove_file $MYSQLD_DATADIR/test/t1_ai.TRN~; 1156# Let us check that updates to .TRN files were rolled back too 1157drop trigger t1_bi; 1158drop trigger t1_ai; 1159drop table t1; 1160 1161# Test for bug #16829 "Firing trigger with RETURN crashes the server" 1162# RETURN is not supposed to be used anywhere except functions, so error 1163# should be returned when one attempts to create trigger with RETURN. 1164create table t1 (i int); 1165--error ER_SP_BADRETURN 1166create trigger t1_bi before insert on t1 for each row return 0; 1167insert into t1 values (1); 1168drop table t1; 1169 1170# Test for bug #17764 "Trigger crashes MyISAM table" 1171# 1172# Table was reported as crashed when it was subject table of trigger invoked 1173# by insert statement which was executed with enabled bulk insert mode (which 1174# is actually set of optimizations enabled by handler::start_bulk_insert()) 1175# and this trigger also explicitly referenced it. 1176# The same problem arose when table to which bulk insert was done was also 1177# referenced in function called by insert statement. 1178create table t1 (a varchar(64), b int); 1179create table t2 like t1; 1180create trigger t1_ai after insert on t1 for each row 1181 set @a:= (select max(a) from t1); 1182insert into t1 (a) values 1183 ("Twas"),("brillig"),("and"),("the"),("slithy"),("toves"), 1184 ("Did"),("gyre"),("and"),("gimble"),("in"),("the"),("wabe"); 1185create trigger t2_ai after insert on t2 for each row 1186 set @a:= (select max(a) from t2); 1187insert into t2 select * from t1; 1188load data infile '../../std_data/words.dat' into table t1 (a); 1189drop trigger t1_ai; 1190drop trigger t2_ai; 1191# Test that the problem for functions is fixed as well 1192create function f1() returns int return (select max(b) from t1); 1193insert into t1 values 1194 ("All",f1()),("mimsy",f1()),("were",f1()),("the",f1()),("borogoves",f1()), 1195 ("And",f1()),("the",f1()),("mome", f1()),("raths",f1()),("outgrabe",f1()); 1196create function f2() returns int return (select max(b) from t2); 1197insert into t2 select a, f2() from t1; 1198load data infile '../../std_data/words.dat' into table t1 (a) set b:= f1(); 1199drop function f1; 1200drop function f2; 1201drop table t1, t2; 1202 1203# 1204# Test for bug #16021 "Wrong index given to function in trigger" which 1205# was caused by the same bulk insert optimization as bug #17764 but had 1206# slightly different symptoms (instead of reporting table as crashed 1207# storage engine reported error number 124) 1208# 1209create table t1(i int not null, j int not null, n numeric(15,2), primary key(i,j)); 1210create table t2(i int not null, n numeric(15,2), primary key(i)); 1211delimiter |; 1212create trigger t1_ai after insert on t1 for each row 1213begin 1214 declare sn numeric(15,2); 1215 select sum(n) into sn from t1 where i=new.i; 1216 replace into t2 values(new.i, sn); 1217end| 1218delimiter ;| 1219insert into t1 values 1220 (1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00), 1221 (1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00), 1222 (1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00); 1223select * from t1; 1224select * from t2; 1225drop tables t1, t2; 1226 1227# 1228# Test for Bug #16461 connection_id() does not work properly inside trigger 1229# 1230--disable_warnings 1231DROP TABLE IF EXISTS t1; 1232--enable_warnings 1233 1234CREATE TABLE t1 ( 1235 conn_id INT, 1236 trigger_conn_id INT 1237); 1238CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 1239 SET NEW.trigger_conn_id = CONNECTION_ID(); 1240 1241INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1); 1242 1243connect (con1,localhost,root,,); 1244INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1); 1245connection default; 1246disconnect con1; 1247 1248SELECT * FROM t1 WHERE conn_id != trigger_conn_id; 1249 1250DROP TRIGGER t1_bi; 1251DROP TABLE t1; 1252 1253 1254# 1255# Bug#6951: Triggers/Traditional: SET @ result wrong 1256# 1257--disable_warnings 1258DROP TABLE IF EXISTS t1; 1259--enable_warnings 1260 1261CREATE TABLE t1 (i1 INT); 1262 1263SET @save_sql_mode=@@sql_mode; 1264 1265SET SQL_MODE=''; 1266 1267CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW 1268 SET @x = 5/0; 1269 1270SET SQL_MODE='traditional'; 1271 1272CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW 1273 SET @x = 5/0; 1274 1275SET @x=1; 1276INSERT INTO t1 VALUES (@x); 1277SELECT @x; 1278 1279SET @x=2; 1280UPDATE t1 SET i1 = @x; 1281SELECT @x; 1282 1283SET SQL_MODE=''; 1284 1285SET @x=3; 1286INSERT INTO t1 VALUES (@x); 1287SELECT @x; 1288 1289SET @x=4; 1290UPDATE t1 SET i1 = @x; 1291SELECT @x; 1292 1293SET @@sql_mode=@save_sql_mode; 1294 1295DROP TRIGGER t1_ai; 1296DROP TRIGGER t1_au; 1297DROP TABLE t1; 1298 1299 1300# 1301# Test for bug #14635 Accept NEW.x as INOUT parameters to stored 1302# procedures from within triggers 1303# 1304--disable_warnings 1305DROP TABLE IF EXISTS t1; 1306DROP PROCEDURE IF EXISTS p1; 1307DROP PROCEDURE IF EXISTS p2; 1308--enable_warnings 1309 1310CREATE TABLE t1 (i1 INT); 1311 1312# Check that NEW.x pseudo variable is accepted as INOUT and OUT 1313# parameter to stored routine. 1314INSERT INTO t1 VALUES (3); 1315CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5; 1316CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7; 1317delimiter //; 1318CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 1319BEGIN 1320 CALL p1(NEW.i1); 1321 CALL p2(NEW.i1); 1322END// 1323delimiter ;// 1324UPDATE t1 SET i1 = 11 WHERE i1 = 3; 1325DROP TRIGGER t1_bu; 1326DROP PROCEDURE p2; 1327DROP PROCEDURE p1; 1328 1329# Check that OLD.x pseudo variable is not accepted as INOUT and OUT 1330# parameter to stored routine. 1331INSERT INTO t1 VALUES (13); 1332CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17; 1333CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 1334 CALL p1(OLD.i1); 1335--error ER_SP_NOT_VAR_ARG 1336UPDATE t1 SET i1 = 19 WHERE i1 = 13; 1337DROP TRIGGER t1_bu; 1338DROP PROCEDURE p1; 1339 1340INSERT INTO t1 VALUES (23); 1341CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29; 1342CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 1343 CALL p1(OLD.i1); 1344--error ER_SP_NOT_VAR_ARG 1345UPDATE t1 SET i1 = 31 WHERE i1 = 23; 1346DROP TRIGGER t1_bu; 1347DROP PROCEDURE p1; 1348 1349# Check that NEW.x pseudo variable is read-only in the AFTER TRIGGER. 1350INSERT INTO t1 VALUES (37); 1351CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41; 1352CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW 1353 CALL p1(NEW.i1); 1354--error ER_SP_NOT_VAR_ARG 1355UPDATE t1 SET i1 = 43 WHERE i1 = 37; 1356DROP TRIGGER t1_au; 1357DROP PROCEDURE p1; 1358 1359INSERT INTO t1 VALUES (47); 1360CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49; 1361CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW 1362 CALL p1(NEW.i1); 1363--error ER_SP_NOT_VAR_ARG 1364UPDATE t1 SET i1 = 51 WHERE i1 = 47; 1365DROP TRIGGER t1_au; 1366DROP PROCEDURE p1; 1367 1368# Post requisite. 1369SELECT * FROM t1; 1370 1371DROP TABLE t1; 1372 1373# 1374# Bug #18005: Creating a trigger on mysql.event leads to server crash on 1375# scheduler startup 1376# 1377# Bug #18361: Triggers on mysql.user table cause server crash 1378# 1379# We don't allow triggers on the mysql schema 1380delimiter |; 1381--error ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA 1382create trigger wont_work after update on mysql.user for each row 1383begin 1384 set @a:= 1; 1385end| 1386# Try when we're already using the mysql schema 1387use mysql| 1388--error ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA 1389create trigger wont_work after update on event for each row 1390begin 1391 set @a:= 1; 1392end| 1393use test| 1394delimiter ;| 1395 1396 1397# 1398# Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause. 1399# 1400 1401# Prepare. 1402 1403--disable_warnings 1404DROP TABLE IF EXISTS t1; 1405DROP TABLE IF EXISTS t2; 1406--enable_warnings 1407 1408CREATE TABLE t1(c INT); 1409CREATE TABLE t2(c INT); 1410 1411--error ER_WRONG_STRING_LENGTH 1412CREATE DEFINER=longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost 1413 TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; 1414 1415--error ER_WRONG_STRING_LENGTH 1416CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY 1417 TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW SET @a = 2; 1418 1419# Cleanup. 1420 1421DROP TABLE t1; 1422DROP TABLE t2; 1423 1424# 1425# Bug#20028 Function with select return no data 1426# 1427 1428--disable_warnings 1429drop table if exists t1; 1430drop table if exists t2; 1431drop table if exists t3; 1432drop table if exists t4; 1433--enable_warnings 1434 1435SET @save_sql_mode=@@sql_mode; 1436 1437delimiter |; 1438SET sql_mode='TRADITIONAL'| 1439create table t1 (id int(10) not null primary key, v int(10) )| 1440create table t2 (id int(10) not null primary key, v int(10) )| 1441create table t3 (id int(10) not null primary key, v int(10) )| 1442create table t4 (c int)| 1443 1444create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1| 1445create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1| 1446 1447insert into t1 values(10, 10)| 1448set @a:=1/0| 1449select 1/0 from t1| 1450 1451create trigger t1_bi before insert on t1 for each row set @a:=1/0| 1452 1453insert into t1 values(20, 20)| 1454 1455drop trigger t1_bi| 1456create trigger t1_bi before insert on t1 for each row 1457begin 1458 insert into t2 values (new.id, new.v); 1459 update t2 set v=v+1 where id= new.id; 1460 replace t3 values (new.id, 0); 1461 update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id; 1462 create temporary table t5 select * from t1; 1463 delete from t5; 1464 insert into t5 select * from t1; 1465 insert into t4 values (0); 1466 set @check= (select count(*) from t5); 1467 update t4 set c= @check; 1468 drop temporary table t5; 1469 1470 set @a:=1/0; 1471end| 1472 1473set @check=0, @t4_bi_called=0, @t4_bu_called=0| 1474insert into t1 values(30, 30)| 1475select @check, @t4_bi_called, @t4_bu_called| 1476 1477delimiter ;| 1478 1479SET @@sql_mode=@save_sql_mode; 1480 1481drop table t1; 1482drop table t2; 1483drop table t3; 1484drop table t4; 1485 1486# 1487# Bug#20670 "UPDATE using key and invoking trigger that modifies 1488# this key does not stop" 1489# 1490 1491--disable_warnings 1492drop table if exists t1; 1493--enable_warnings 1494create table t1 (i int, j int key); 1495insert into t1 values (1,1), (2,2), (3,3); 1496create trigger t1_bu before update on t1 for each row 1497 set new.j = new.j + 10; 1498# This should not work indefinitely and should cause 1499# expected result 1500update t1 set i= i+ 10 where j > 2; 1501select * from t1; 1502drop table t1; 1503 1504# 1505# Bug#23556 TRUNCATE TABLE still maps to DELETE 1506# 1507CREATE TABLE t1 (a INT PRIMARY KEY); 1508CREATE TABLE t2 (a INT PRIMARY KEY); 1509INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 1510 1511CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW 1512 INSERT INTO t2 VALUES (OLD.a); 1513 1514FLUSH STATUS; 1515TRUNCATE t1; 1516SHOW STATUS LIKE 'handler_delete'; 1517SELECT COUNT(*) FROM t2; 1518 1519INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 1520DELETE FROM t2; 1521 1522FLUSH STATUS; 1523DELETE FROM t1; 1524SHOW STATUS LIKE 'handler_delete'; 1525SELECT COUNT(*) FROM t2; 1526 1527DROP TRIGGER trg_t1; 1528DROP TABLE t1,t2; 1529 1530# 1531# Bug #23651 "Server crashes when trigger which uses stored function 1532# invoked from different connections". 1533# 1534--disable_warnings 1535drop table if exists t1; 1536drop function if exists f1; 1537--enable_warnings 1538create table t1 (i int); 1539create function f1() returns int return 10; 1540create trigger t1_bi before insert on t1 for each row set @a:= f1() + 10; 1541insert into t1 values (); 1542select @a; 1543connection addconroot1; 1544insert into t1 values (); 1545select @a; 1546connection default; 1547drop table t1; 1548drop function f1; 1549 1550# 1551# Bug#23703: DROP TRIGGER needs an IF EXISTS 1552# 1553 1554--disable_warnings 1555drop table if exists t1; 1556--enable_warnings 1557 1558create table t1(a int, b varchar(50)); 1559 1560-- error ER_TRG_DOES_NOT_EXIST 1561drop trigger not_a_trigger; 1562 1563drop trigger if exists not_a_trigger; 1564 1565create trigger t1_bi before insert on t1 1566for each row set NEW.b := "In trigger t1_bi"; 1567 1568insert into t1 values (1, "a"); 1569drop trigger if exists t1_bi; 1570insert into t1 values (2, "b"); 1571drop trigger if exists t1_bi; 1572insert into t1 values (3, "c"); 1573 1574select * from t1; 1575 1576drop table t1; 1577 1578# 1579# Bug#25398: crash when a trigger contains a SELECT with 1580# trigger fields in the select list under DISTINCT 1581# 1582 1583CREATE TABLE t1 ( 1584 id int NOT NULL DEFAULT '0', 1585 a varchar(10) NOT NULL, 1586 b varchar(10), 1587 c varchar(10), 1588 d timestamp NOT NULL, 1589 PRIMARY KEY (id, a) 1590); 1591 1592CREATE TABLE t2 ( 1593 fubar_id int unsigned NOT NULL DEFAULT '0', 1594 last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 1595 PRIMARY KEY (fubar_id) 1596); 1597 1598DELIMITER |; 1599 1600CREATE TRIGGER fubar_change 1601 AFTER UPDATE ON t1 1602 FOR EACH ROW 1603 BEGIN 1604 INSERT INTO t2 (fubar_id, last_change_time) 1605 SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time 1606 FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c) 1607 ON DUPLICATE KEY UPDATE 1608 last_change_time = 1609 IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time); 1610 END 1611| 1612 1613DELIMITER ;| 1614 1615INSERT INTO t1 (id,a, b,c,d) VALUES 1616 (1,'a','b','c',now()),(2,'a','b','c',now()); 1617 1618UPDATE t1 SET c='Bang!' WHERE id=1; 1619 1620SELECT fubar_id FROM t2; 1621 1622DROP TABLE t1,t2; 1623 1624# 1625# Bug#21285 (Incorrect message error deleting records in a table with a 1626# trigger for inserting) 1627# 1628 1629--disable_warnings 1630DROP TABLE IF EXISTS bug21825_A; 1631DROP TABLE IF EXISTS bug21825_B; 1632--enable_warnings 1633 1634CREATE TABLE bug21825_A (id int(10)); 1635CREATE TABLE bug21825_B (id int(10)); 1636 1637delimiter //; 1638 1639CREATE TRIGGER trgA AFTER INSERT ON bug21825_A 1640FOR EACH ROW 1641BEGIN 1642 INSERT INTO bug21825_B (id) values (1); 1643END// 1644delimiter ;// 1645 1646INSERT INTO bug21825_A (id) VALUES (10); 1647INSERT INTO bug21825_A (id) VALUES (20); 1648 1649DROP TABLE bug21825_B; 1650 1651# Must pass, the missing table in the insert trigger should not matter. 1652DELETE FROM bug21825_A WHERE id = 20; 1653 1654DROP TABLE bug21825_A; 1655 1656# 1657# Bug#22580 (DROP TABLE in nested stored procedure causes strange dependancy 1658# error) 1659# 1660 1661--disable_warnings 1662DROP TABLE IF EXISTS bug22580_t1; 1663DROP PROCEDURE IF EXISTS bug22580_proc_1; 1664DROP PROCEDURE IF EXISTS bug22580_proc_2; 1665--enable_warnings 1666 1667CREATE TABLE bug22580_t1 (a INT, b INT); 1668 1669DELIMITER ||; 1670 1671CREATE PROCEDURE bug22580_proc_2() 1672BEGIN 1673 DROP TABLE IF EXISTS bug22580_tmp; 1674 CREATE TEMPORARY TABLE bug22580_tmp (a INT); 1675 DROP TABLE bug22580_tmp; 1676END|| 1677 1678CREATE PROCEDURE bug22580_proc_1() 1679BEGIN 1680 CALL bug22580_proc_2(); 1681END|| 1682 1683CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1 1684FOR EACH ROW 1685BEGIN 1686 CALL bug22580_proc_1(); 1687END|| 1688 1689DELIMITER ;|| 1690 1691# Must pass, the actions of the update trigger should not matter 1692INSERT INTO bug22580_t1 VALUES (1,1); 1693 1694DROP TABLE bug22580_t1; 1695DROP PROCEDURE bug22580_proc_1; 1696DROP PROCEDURE bug22580_proc_2; 1697 1698# 1699# Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE 1700# 1701--disable_warnings 1702DROP TRIGGER IF EXISTS trg27006_a_update; 1703DROP TRIGGER IF EXISTS trg27006_a_insert; 1704--enable_warnings 1705 1706CREATE TABLE t1 ( 1707 `id` int(10) unsigned NOT NULL auto_increment, 1708 `val` varchar(10) NOT NULL, 1709 PRIMARY KEY (`id`) 1710); 1711CREATE TABLE t2 like t1; 1712DELIMITER |; 1713 1714CREATE TRIGGER trg27006_a_insert AFTER INSERT ON t1 FOR EACH ROW 1715BEGIN 1716 insert into t2 values (NULL,new.val); 1717END | 1718CREATE TRIGGER trg27006_a_update AFTER UPDATE ON t1 FOR EACH ROW 1719BEGIN 1720 insert into t2 values (NULL,new.val); 1721END | 1722DELIMITER ;| 1723 1724INSERT INTO t1(val) VALUES ('test1'),('test2'); 1725SELECT * FROM t1; 1726SELECT * FROM t2; 1727INSERT INTO t1 VALUES (2,'test2') ON DUPLICATE KEY UPDATE val=VALUES(val); 1728INSERT INTO t1 VALUES (2,'test3') ON DUPLICATE KEY UPDATE val=VALUES(val); 1729INSERT INTO t1 VALUES (3,'test4') ON DUPLICATE KEY UPDATE val=VALUES(val); 1730SELECT * FROM t1; 1731SELECT * FROM t2; 1732DROP TRIGGER trg27006_a_insert; 1733DROP TRIGGER trg27006_a_update; 1734drop table t1,t2; 1735 1736# 1737# Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers" 1738# 1739 1740--disable_warnings 1741drop table if exists t1, t2, t3; 1742--enable_warnings 1743create table t1 (i int); 1744create trigger t1_bi before insert on t1 for each row set new.i = 7; 1745create trigger t1_ai after insert on t1 for each row set @a := 7; 1746create table t2 (j int); 1747insert into t2 values (1), (2); 1748set @a:=""; 1749insert into t1 select * from t2; 1750select * from t1; 1751select @a; 1752# Let us check that trigger that involves table also works ok. 1753drop trigger t1_bi; 1754drop trigger t1_ai; 1755create table t3 (isave int); 1756create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i); 1757insert into t1 select * from t2; 1758select * from t1; 1759select * from t3; 1760drop table t1, t2, t3; 1761 1762disconnect addconroot1; 1763disconnect addconroot2; 1764disconnect addconwithoutdb; 1765--echo 1766--echo Bug#28502 Triggers that update another innodb table will block 1767--echo on X lock unnecessarily 1768--echo 1769--echo Ensure we do not open and lock tables for triggers we do not fire. 1770--echo 1771--disable_warnings 1772drop table if exists t1, t2; 1773drop trigger if exists trg_bug28502_au; 1774--enable_warnings 1775 1776create table t1 (id int, count int); 1777create table t2 (id int); 1778delimiter |; 1779 1780create trigger trg_bug28502_au before update on t2 1781for each row 1782begin 1783 if (new.id is not null) then 1784 update t1 set count= count + 1 where id = old.id; 1785 end if; 1786end| 1787 1788delimiter ;| 1789insert into t1 (id, count) values (1, 0); 1790 1791lock table t1 write; 1792 1793--connect (connection_insert, localhost, root, , test, , ) 1794connection connection_insert; 1795# Is expected to pass. 1796insert into t2 set id=1; 1797connection default; 1798unlock tables; 1799update t2 set id=1 where id=1; 1800select * from t1; 1801select * from t2; 1802# Will drop the trigger 1803drop table t1, t2; 1804disconnect connection_insert; 1805--echo 1806--echo Additionally, provide test coverage for triggers and 1807--echo all MySQL data changing commands. 1808--echo 1809--disable_warnings 1810drop table if exists t1, t2, t1_op_log; 1811drop view if exists v1; 1812drop trigger if exists trg_bug28502_bi; 1813drop trigger if exists trg_bug28502_ai; 1814drop trigger if exists trg_bug28502_bu; 1815drop trigger if exists trg_bug28502_au; 1816drop trigger if exists trg_bug28502_bd; 1817drop trigger if exists trg_bug28502_ad; 1818--enable_warnings 1819create table t1 (id int primary key auto_increment, operation varchar(255)); 1820create table t2 (id int primary key); 1821create table t1_op_log(operation varchar(255)); 1822create view v1 as select * from t1; 1823create trigger trg_bug28502_bi before insert on t1 1824for each row 1825 insert into t1_op_log (operation) 1826 values (concat("Before INSERT, new=", new.operation)); 1827 1828create trigger trg_bug28502_ai after insert on t1 1829for each row 1830 insert into t1_op_log (operation) 1831 values (concat("After INSERT, new=", new.operation)); 1832 1833create trigger trg_bug28502_bu before update on t1 1834for each row 1835 insert into t1_op_log (operation) 1836 values (concat("Before UPDATE, new=", new.operation, 1837 ", old=", old.operation)); 1838 1839create trigger trg_bug28502_au after update on t1 1840for each row 1841 insert into t1_op_log (operation) 1842 values (concat("After UPDATE, new=", new.operation, 1843 ", old=", old.operation)); 1844 1845create trigger trg_bug28502_bd before delete on t1 1846for each row 1847 insert into t1_op_log (operation) 1848 values (concat("Before DELETE, old=", old.operation)); 1849 1850create trigger trg_bug28502_ad after delete on t1 1851for each row 1852 insert into t1_op_log (operation) 1853 values (concat("After DELETE, old=", old.operation)); 1854 1855insert into t1 (operation) values ("INSERT"); 1856 1857set @id=last_insert_id(); 1858 1859select * from t1; 1860select * from t1_op_log; 1861truncate t1_op_log; 1862 1863update t1 set operation="UPDATE" where id=@id; 1864 1865select * from t1; 1866select * from t1_op_log; 1867truncate t1_op_log; 1868 1869delete from t1 where id=@id; 1870 1871select * from t1; 1872select * from t1_op_log; 1873truncate t1; 1874truncate t1_op_log; 1875 1876insert into t1 (id, operation) values 1877(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key") 1878on duplicate key update id=NULL, operation="Should never happen"; 1879 1880set @id=last_insert_id(); 1881 1882select * from t1; 1883select * from t1_op_log; 1884truncate t1_op_log; 1885 1886insert into t1 (id, operation) values 1887(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same") 1888on duplicate key update id=NULL, 1889operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate"; 1890 1891select * from t1; 1892select * from t1_op_log; 1893truncate t1; 1894truncate t1_op_log; 1895 1896replace into t1 values (NULL, "REPLACE, inserting a new key"); 1897 1898set @id=last_insert_id(); 1899 1900select * from t1; 1901select * from t1_op_log; 1902truncate t1_op_log; 1903 1904replace into t1 values (@id, "REPLACE, deleting the duplicate"); 1905 1906select * from t1; 1907select * from t1_op_log; 1908truncate t1; 1909truncate t1_op_log; 1910 1911insert into t1 1912select NULL, "CREATE TABLE ... SELECT, inserting a new key"; 1913 1914set @id=last_insert_id(); 1915 1916select * from t1; 1917select * from t1_op_log; 1918truncate t1_op_log; 1919 1920replace into t1 1921select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key"; 1922 1923select * from t1; 1924select * from t1_op_log; 1925truncate t1; 1926truncate t1_op_log; 1927 1928insert into t1 (id, operation) 1929select NULL, "INSERT ... SELECT, inserting a new key"; 1930 1931set @id=last_insert_id(); 1932 1933select * from t1; 1934select * from t1_op_log; 1935truncate t1_op_log; 1936 1937insert into t1 (id, operation) 1938select @id, 1939"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate" 1940on duplicate key update id=NULL, 1941operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"; 1942 1943select * from t1; 1944select * from t1_op_log; 1945truncate t1; 1946truncate t1_op_log; 1947 1948replace into t1 (id, operation) 1949select NULL, "REPLACE ... SELECT, inserting a new key"; 1950 1951set @id=last_insert_id(); 1952 1953select * from t1; 1954select * from t1_op_log; 1955truncate t1_op_log; 1956 1957replace into t1 (id, operation) 1958select @id, "REPLACE ... SELECT, deleting a duplicate"; 1959 1960select * from t1; 1961select * from t1_op_log; 1962truncate t1; 1963truncate t1_op_log; 1964 1965insert into t1 (id, operation) values (1, "INSERT for multi-DELETE"); 1966insert into t2 (id) values (1); 1967 1968delete t1.*, t2.* from t1, t2 where t1.id=1; 1969 1970select * from t1; 1971select * from t2; 1972select * from t1_op_log; 1973truncate t1; 1974truncate t2; 1975truncate t1_op_log; 1976 1977insert into t1 (id, operation) values (1, "INSERT for multi-UPDATE"); 1978insert into t2 (id) values (1); 1979update t1, t2 set t1.id=2, operation="multi-UPDATE" where t1.id=1; 1980update t1, t2 1981set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where t1.id=2; 1982 1983select * from t1; 1984select * from t2; 1985select * from t1_op_log; 1986truncate table t1; 1987truncate table t2; 1988truncate table t1_op_log; 1989 1990--echo 1991--echo Now do the same but use a view instead of the base table. 1992--echo 1993 1994insert into v1 (operation) values ("INSERT"); 1995 1996set @id=last_insert_id(); 1997 1998select * from t1; 1999select * from t1_op_log; 2000truncate t1_op_log; 2001 2002update v1 set operation="UPDATE" where id=@id; 2003 2004select * from t1; 2005select * from t1_op_log; 2006truncate t1_op_log; 2007 2008delete from v1 where id=@id; 2009 2010select * from t1; 2011select * from t1_op_log; 2012truncate t1; 2013truncate t1_op_log; 2014 2015insert into v1 (id, operation) values 2016(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key") 2017on duplicate key update id=NULL, operation="Should never happen"; 2018 2019set @id=last_insert_id(); 2020 2021select * from t1; 2022select * from t1_op_log; 2023truncate t1_op_log; 2024 2025insert into v1 (id, operation) values 2026(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same") 2027on duplicate key update id=NULL, 2028operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate"; 2029 2030select * from t1; 2031select * from t1_op_log; 2032truncate t1; 2033truncate t1_op_log; 2034 2035replace into v1 values (NULL, "REPLACE, inserting a new key"); 2036 2037set @id=last_insert_id(); 2038 2039select * from t1; 2040select * from t1_op_log; 2041truncate t1_op_log; 2042 2043replace into v1 values (@id, "REPLACE, deleting the duplicate"); 2044 2045select * from t1; 2046select * from t1_op_log; 2047truncate t1; 2048truncate t1_op_log; 2049 2050insert into v1 2051select NULL, "CREATE TABLE ... SELECT, inserting a new key"; 2052 2053set @id=last_insert_id(); 2054 2055select * from t1; 2056select * from t1_op_log; 2057truncate t1_op_log; 2058 2059replace into v1 2060select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key"; 2061 2062select * from t1; 2063select * from t1_op_log; 2064truncate t1; 2065truncate t1_op_log; 2066 2067insert into v1 (id, operation) 2068select NULL, "INSERT ... SELECT, inserting a new key"; 2069 2070set @id=last_insert_id(); 2071 2072select * from t1; 2073select * from t1_op_log; 2074truncate t1_op_log; 2075 2076insert into v1 (id, operation) 2077select @id, 2078"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate" 2079on duplicate key update id=NULL, 2080operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"; 2081 2082select * from t1; 2083select * from t1_op_log; 2084truncate t1; 2085truncate t1_op_log; 2086 2087replace into v1 (id, operation) 2088select NULL, "REPLACE ... SELECT, inserting a new key"; 2089 2090set @id=last_insert_id(); 2091 2092select * from t1; 2093select * from t1_op_log; 2094truncate t1_op_log; 2095 2096replace into v1 (id, operation) 2097select @id, "REPLACE ... SELECT, deleting a duplicate"; 2098 2099select * from t1; 2100select * from t1_op_log; 2101truncate t1; 2102truncate t1_op_log; 2103 2104insert into v1 (id, operation) values (1, "INSERT for multi-DELETE"); 2105insert into t2 (id) values (1); 2106 2107delete v1.*, t2.* from v1, t2 where v1.id=1; 2108 2109select * from t1; 2110select * from t2; 2111select * from t1_op_log; 2112truncate t1; 2113truncate t2; 2114truncate t1_op_log; 2115 2116insert into v1 (id, operation) values (1, "INSERT for multi-UPDATE"); 2117insert into t2 (id) values (1); 2118update v1, t2 set v1.id=2, operation="multi-UPDATE" where v1.id=1; 2119update v1, t2 2120set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where v1.id=2; 2121 2122select * from t1; 2123select * from t2; 2124select * from t1_op_log; 2125 2126drop view v1; 2127drop table t1, t2, t1_op_log; 2128 2129# 2130# TODO: test LOAD DATA INFILE 2131# 2132--echo 2133--echo Bug#27248 Triggers: error if insert affects temporary table 2134--echo 2135--echo The bug was fixed by the fix for Bug#26141 2136--echo 2137--disable_warnings 2138drop table if exists t1; 2139drop temporary table if exists t2; 2140--enable_warnings 2141create table t1 (s1 int); 2142create temporary table t2 (s1 int); 2143create trigger t1_bi before insert on t1 for each row insert into t2 values (0); 2144create trigger t1_bd before delete on t1 for each row delete from t2; 2145insert into t1 values (0); 2146insert into t1 values (0); 2147select * from t1; 2148select * from t2; 2149delete from t1; 2150select * from t1; 2151select * from t2; 2152drop table t1; 2153drop temporary table t2; 2154 2155--echo #------------------------------------------------------------------------ 2156--echo # Bug#39953 Triggers are not working properly with multi table updates 2157--echo #------------------------------------------------------------------------ 2158 2159--disable_warnings 2160DROP TABLE IF EXISTS t1; 2161DROP TRIGGER IF EXISTS t_insert; 2162DROP TABLE IF EXISTS t2; 2163--enable_warnings 2164 2165CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a)); 2166INSERT INTO t1 (a) VALUES (2),(5); 2167CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); 2168DELIMITER |; 2169CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET 2170date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END | 2171DELIMITER ;| 2172INSERT INTO t2 (a,b) VALUES (1,2); 2173 2174DROP TRIGGER t_insert; 2175 2176DELIMITER |; 2177CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET 2178date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END | 2179DELIMITER ;| 2180--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG 2181INSERT INTO t2 (a,b) VALUES (3,5); 2182 2183DROP TABLE t1; 2184DROP TRIGGER t_insert; 2185DROP TABLE t2; 2186 2187--echo # End of 5.0 tests 2188 2189# 2190# Bug#25411 (trigger code truncated) 2191# 2192 2193--disable_warnings 2194drop table if exists table_25411_a; 2195drop table if exists table_25411_b; 2196--enable_warnings 2197 2198create table table_25411_a(a int); 2199create table table_25411_b(b int); 2200 2201create trigger trg_25411a_ai after insert on table_25411_a 2202for each row 2203 insert into table_25411_b select new.*; 2204 2205select * from table_25411_a; 2206 2207--error ER_BAD_TABLE_ERROR 2208insert into table_25411_a values (1); 2209 2210select * from table_25411_a; 2211 2212drop table table_25411_a; 2213drop table table_25411_b; 2214 2215# 2216# Bug #31866: MySQL Server crashes on SHOW CREATE TRIGGER statement 2217# 2218 2219--disable_warnings 2220DROP TRIGGER IF EXISTS trg; 2221--enable_warnings 2222 2223--error ER_TRG_DOES_NOT_EXIST 2224SHOW CREATE TRIGGER trg; 2225 2226# 2227# Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock 2228# 2229# Test of trigger creation and removal under LOCK TABLES 2230# 2231 2232--disable_warnings 2233drop table if exists t1; 2234--enable_warnings 2235 2236create table t1 (i int, j int); 2237 2238create trigger t1_bi before insert on t1 for each row begin end; 2239--error ER_TRG_ALREADY_EXISTS 2240create trigger t1_bi before insert on t1 for each row begin end; 2241create trigger t1_bi2 before insert on t1 for each row begin end; 2242drop trigger t1_bi; 2243drop trigger t1_bi2; 2244--error ER_TRG_DOES_NOT_EXIST 2245drop trigger t1_bi; 2246 2247lock tables t1 read; 2248--error ER_TABLE_NOT_LOCKED_FOR_WRITE 2249create trigger t1_bi before insert on t1 for each row begin end; 2250--error ER_TABLE_NOT_LOCKED_FOR_WRITE 2251create trigger t1_bi before insert on t1 for each row begin end; 2252--error ER_TRG_DOES_NOT_EXIST 2253drop trigger t1_bi; 2254unlock tables; 2255 2256create trigger t1_bi before insert on t1 for each row begin end; 2257lock tables t1 read; 2258--error ER_TABLE_NOT_LOCKED_FOR_WRITE 2259create trigger t1_bi before insert on t1 for each row begin end; 2260--error ER_TABLE_NOT_LOCKED_FOR_WRITE 2261drop trigger t1_bi; 2262unlock tables; 2263drop trigger t1_bi; 2264 2265lock tables t1 write; 2266create trigger b1_bi before insert on t1 for each row set new.i = new.i + 10; 2267insert into t1 values (10, 10); 2268drop trigger b1_bi; 2269insert into t1 values (10, 10); 2270select * from t1; 2271unlock tables; 2272 2273drop table t1; 2274 2275# 2276# Bug#23771 AFTER UPDATE trigger not invoked when there are no changes of the data 2277# 2278 2279--disable_warnings 2280drop table if exists t1, t2; 2281drop trigger if exists trg1; 2282drop trigger if exists trg2; 2283--enable_warnings 2284create table t1 (a int); 2285create table t2 (b int); 2286create trigger trg1 after update on t1 for each row set @a= @a+1; 2287create trigger trg2 after update on t2 for each row set @b= @b+1; 2288insert into t1 values (1), (2), (3); 2289insert into t2 values (1), (2), (3); 2290set @a= 0; 2291set @b= 0; 2292update t1, t2 set t1.a= t1.a, t2.b= t2.b; 2293select @a, @b; 2294update t1, t2 set t1.a= t2.b, t2.b= t1.a; 2295select @a, @b; 2296update t1 set a= a; 2297select @a, @b; 2298update t2 set b= b; 2299select @a, @b; 2300update t1 set a= 1; 2301select @a, @b; 2302update t2 set b= 1; 2303select @a, @b; 2304drop trigger trg1; 2305drop trigger trg2; 2306drop table t1, t2; 2307 2308# 2309# Bug#44653: Server crash noticed when executing random queries with partitions. 2310# 2311CREATE TABLE t1 ( a INT, b INT ); 2312CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY, b INT ); 2313 2314INSERT INTO t1 (a) VALUES (1); 2315 2316delimiter //; 2317CREATE TRIGGER tr1 2318BEFORE INSERT ON t2 2319FOR EACH ROW 2320BEGIN 2321 UPDATE a_nonextisting_table SET a = 1; 2322END// 2323delimiter ;// 2324 2325--disable_abort_on_error 2326CREATE TABLE IF NOT EXISTS t2 ( a INT, b INT ) SELECT a, b FROM t1; 2327--enable_abort_on_error 2328 2329# Caused failed assertion 2330SELECT * FROM t2; 2331 2332DROP TABLE t1, t2; 2333 2334--echo # 2335--echo # Bug#51650 crash with user variables and triggers 2336--echo # 2337 2338--disable_warnings 2339DROP TRIGGER IF EXISTS trg1; 2340DROP TABLE IF EXISTS t1, t2; 2341--enable_warnings 2342 2343CREATE TABLE t1 (b VARCHAR(50) NOT NULL); 2344CREATE TABLE t2 (a VARCHAR(10) NOT NULL DEFAULT ''); 2345 2346delimiter //; 2347CREATE TRIGGER trg1 AFTER INSERT ON t2 2348FOR EACH ROW BEGIN 2349 SELECT 1 FROM t1 c WHERE 2350 (@bug51650 IS NULL OR @bug51650 != c.b) AND c.b = NEW.a LIMIT 1 INTO @foo; 2351END// 2352delimiter ;// 2353 2354SET @bug51650 = 1; 2355INSERT IGNORE INTO t2 VALUES(); 2356INSERT IGNORE INTO t1 SET b = '777'; 2357INSERT IGNORE INTO t2 SET a = '111'; 2358SET @bug51650 = 1; 2359INSERT IGNORE INTO t2 SET a = '777'; 2360 2361DROP TRIGGER trg1; 2362DROP TABLE t1, t2; 2363 2364# 2365# Bug #48525: trigger changes "Column 'id' cannot be null" behaviour 2366# 2367CREATE TABLE t1 (id INT NOT NULL); 2368CREATE TABLE t2 (id INT NOT NULL); 2369INSERT t1 VALUES (1),(2),(3); 2370UPDATE IGNORE t1 SET id=NULL; 2371CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 2372 INSERT INTO t2 VALUES (3); 2373UPDATE t1 SET id=NULL; 2374DROP TRIGGER t1_bu; 2375DROP TABLE t1,t2; 2376 2377--echo # 2378--echo # Bug#50755: Crash if stored routine def contains version comments 2379--echo # 2380 2381--disable_warnings 2382DROP DATABASE IF EXISTS db1; 2383DROP TRIGGER IF EXISTS trg1; 2384DROP TABLE IF EXISTS t1, t2; 2385--enable_warnings 2386 2387CREATE DATABASE db1; 2388USE db1; 2389 2390CREATE TABLE t1 (b INT); 2391CREATE TABLE t2 (a INT); 2392 2393CREATE TRIGGER trg1 BEFORE INSERT ON t2 FOR EACH ROW INSERT/*!INTO*/t1 VALUES (1); 2394--echo # Used to crash 2395--replace_column 6 # 2396SHOW TRIGGERS IN db1; 2397--error ER_PARSE_ERROR 2398INSERT INTO t2 VALUES (1); 2399SELECT * FROM t1; 2400 2401--echo # Work around Bug#45235 2402let $MYSQLD_DATADIR = `select @@datadir`; 2403--remove_file $MYSQLD_DATADIR/db1/t2.TRG 2404--remove_file $MYSQLD_DATADIR/db1/trg1.TRN 2405 2406DROP DATABASE db1; 2407USE test; 2408 2409--echo # End of 5.1 tests. 2410 2411# 2412# Test that using a trigger will not open mysql.proc 2413# 2414create table t1 (i int); 2415create table t2 (i int); 2416flush tables; 2417flush status; 2418delimiter //; 2419CREATE DEFINER=`root`@`localhost` TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW BEGIN DELETE FROM t2 WHERE t2.i = OLD.i; END // 2420delimiter ;// 2421insert into t1 values (1),(2); 2422insert into t2 values (1),(2); 2423delete from t1 where i=1; 2424# 2425# If mysql.proc would be used we would have 4 here. 3 is the correct number. 2426# (CREATE TRIGGER will open t1 and then flush it) 2427# 2428show status like 'Opened_tables'; 2429select * from t1; 2430select * from t2; 2431drop table t1,t2; 2432 2433--echo # End of 5.2 tests. 2434 2435--echo # 2436--echo # Bug#34453 Can't change size of file (Errcode: 1224) 2437--echo # 2438 2439--disable_warnings 2440DROP TRIGGER IF EXISTS t1_bi; 2441DROP TRIGGER IF EXISTS t1_bd; 2442DROP TABLE IF EXISTS t1; 2443DROP TEMPORARY TABLE IF EXISTS t2; 2444--enable_warnings 2445 2446CREATE TABLE t1 (s1 INT); 2447CREATE TEMPORARY TABLE t2 (s1 INT); 2448CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (0); 2449CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t2; 2450INSERT INTO t1 VALUES (0); 2451INSERT INTO t1 VALUES (0); 2452SELECT * FROM t1; 2453SELECT * FROM t2; 2454-- echo # Reported to give ERROR 14 (HY000): 2455-- echo # Can't change size of file (Errcode: 1224) 2456-- echo # on Windows 2457DELETE FROM t1; 2458 2459DROP TABLE t1; 2460DROP TEMPORARY TABLE t2; 2461 2462# 2463# Bug#36649: Condition area is not properly cleaned up after stored routine invocation 2464# 2465 2466SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 2467--disable_warnings 2468DROP TRIGGER IF EXISTS trg1; 2469DROP TABLE IF EXISTS t1; 2470--enable_warnings 2471 2472CREATE TABLE t1 (a INT); 2473 2474delimiter |; 2475CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW 2476BEGIN 2477 DECLARE a CHAR; 2478 SELECT 'ab' INTO a; 2479 SELECT 'ab' INTO a; 2480 SELECT 'a' INTO a; 2481END| 2482delimiter ;| 2483 2484INSERT INTO t1 VALUES (1); 2485 2486DROP TRIGGER trg1; 2487DROP TABLE t1; 2488 2489# 2490# Successive trigger actuations 2491# 2492 2493--disable_warnings 2494DROP TRIGGER IF EXISTS trg1; 2495DROP TRIGGER IF EXISTS trg2; 2496DROP TABLE IF EXISTS t1; 2497--enable_warnings 2498 2499CREATE TABLE t1 (a INT); 2500 2501delimiter |; 2502 2503CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW 2504BEGIN 2505 DECLARE trg1 CHAR; 2506 SELECT 'ab' INTO trg1; 2507END| 2508 2509CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW 2510BEGIN 2511 DECLARE trg2 CHAR; 2512 SELECT 'ab' INTO trg2; 2513END| 2514 2515delimiter ;| 2516SET sql_mode = DEFAULT; 2517 2518INSERT INTO t1 VALUES (0); 2519SELECT * FROM t1; 2520SHOW WARNINGS; 2521INSERT INTO t1 VALUES (1),(2); 2522 2523DROP TRIGGER trg1; 2524DROP TRIGGER trg2; 2525DROP TABLE t1; 2526 2527 2528--echo # 2529--echo # Bug #46747 "Crash in MDL_ticket::upgrade_shared_lock_to_exclusive 2530--echo # on TRIGGER + TEMP table". 2531--echo # 2532 2533--disable_warnings 2534drop trigger if exists t1_bi; 2535drop temporary table if exists t1; 2536drop table if exists t1; 2537--enable_warnings 2538 2539create table t1 (i int); 2540create trigger t1_bi before insert on t1 for each row set @a:=1; 2541--echo # Create temporary table which shadows base table with trigger. 2542create temporary table t1 (j int); 2543--echo # Dropping of trigger should succeed. 2544drop trigger t1_bi; 2545select trigger_name from information_schema.triggers 2546 where event_object_schema = 'test' and event_object_table = 't1'; 2547--echo # Clean-up. 2548drop temporary table t1; 2549drop table t1; 2550 2551--echo # 2552--echo # Bug #12362125: SP INOUT HANDLING IS BROKEN FOR TEXT TYPE. 2553--echo # 2554 2555--disable_warnings 2556DROP TABLE IF EXISTS t1; 2557--enable_warnings 2558 2559CREATE TABLE t1(c TEXT); 2560 2561delimiter |; 2562CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 2563BEGIN 2564 DECLARE v TEXT; 2565 SET v = 'aaa'; 2566 SET NEW.c = v; 2567END| 2568delimiter ;| 2569 2570INSERT INTO t1 VALUES('qazwsxedc'); 2571 2572SELECT c FROM t1; 2573 2574DROP TABLE t1; 2575 2576--echo 2577--echo # End of 5.5 tests. 2578 2579--echo # 2580--echo # BUG #910083: materialized subquery in a trigger 2581--echo # 2582 2583SET @save_optimizer_switch=@@optimizer_switch; 2584SET optimizer_switch='materialization=on'; 2585 2586CREATE TABLE t1 (a int); 2587CREATE TABLE t2 (b int); 2588 2589CREATE TRIGGER tr AFTER UPDATE ON t1 FOR EACH ROW 2590 UPDATE t2 SET b = (SELECT COUNT(a) FROM t1); 2591 2592INSERT INTO t1 2593 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); 2594 2595INSERT INTO t2 2596 VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0); 2597 2598send 2599 UPDATE t1 SET a = 3; 2600 2601connect(con1,localhost,root,,); 2602 SELECT COUNT(*) FROM t1; 2603disconnect con1; 2604 2605connection default; 2606reap; 2607SELECT * FROM t2; 2608UPDATE t1 SET a = 2; 2609SELECT * FROM t2; 2610 2611SET optimizer_switch=@save_optimizer_switch; 2612 2613DROP TRIGGER tr; 2614DROP TABLE t1, t2; 2615 2616--echo # End of 5.3 tests. 2617 2618# 2619# MDEV-4829 BEFORE INSERT triggers don't issue 1406 error 2620# Also check timestamp for trigger 2621# 2622 2623set time_zone="+00:00"; 2624SET TIMESTAMP=UNIX_TIMESTAMP('2001-01-01 10:20:30'); 2625SET @@session.sql_mode = 'STRICT_ALL_TABLES,STRICT_TRANS_TABLES'; 2626CREATE TABLE t1 (c CHAR(1) NOT NULL); 2627DELIMITER |; 2628CREATE TRIGGER t1_bi 2629 BEFORE INSERT 2630 ON t1 2631 FOR EACH ROW 2632 BEGIN 2633 SET NEW.c = 'www'; 2634 END; 2635| 2636DELIMITER ;| 2637SET @@session.sql_mode = default; 2638--error ER_DATA_TOO_LONG 2639INSERT INTO t1 VALUES ('a'); 2640show create trigger t1_bi; 2641DROP TRIGGER t1_bi; 2642DROP TABLE t1; 2643SET TIMESTAMP=DEFAULT; 2644set time_zone= @@global.time_zone; 2645 2646--echo # 2647--echo # MDEV-13936: Server crashes in Time_and_counter_tracker::incr_loops 2648--echo # 2649 2650CREATE TABLE t1 (i INT); 2651CREATE VIEW v1 AS SELECT * FROM t1 WHERE RAND() > 0.5; 2652CREATE TABLE t2 (a int); 2653CREATE TABLE t3 (a int); 2654 2655create trigger trg after insert on t2 for each row 2656 INSERT INTO t3 SELECT MAX(i) FROM v1 UNION SELECT MAX(i) FROM v1; 2657 2658drop table t1; 2659 2660--error ER_NO_SUCH_TABLE 2661insert into t2 value (2); 2662CREATE TABLE t1 (i INT); 2663insert into t2 value (2); 2664 2665DROP VIEW v1; 2666DROP TABLE t1,t2,t3; 2667 2668--echo # 2669--echo # MDEV-16093 2670--echo # Assertion `global_status_var.global_memory_used == 0' failed or 2671--echo # bytes lost after inserting into table with non-null blob and trigger 2672--echo # 2673 2674CREATE TABLE t1 (b BLOB NOT NULL); 2675CREATE TRIGGER tr BEFORE UPDATE ON t1 FOR EACH ROW BEGIN END; 2676INSERT INTO t1 VALUES ('foo'); 2677DROP TABLE t1; 2678 2679--echo # 2680--echo # End of 10.1 tests. 2681--echo # 2682 2683# 2684# MDEV-10915 Count number of executed triggers 2685# 2686 2687create table t1 (i int); 2688create trigger tr1 after insert on t1 for each row set @a=@a+1; 2689create trigger tr2 after insert on t1 for each row set @a=@a+1; 2690create trigger tr3 after insert on t1 for each row set @a=@a+1; 2691flush status; 2692show status like 'Executed_triggers'; 2693set @a=0; 2694insert into t1 values (1); 2695show status like 'Executed_triggers'; 2696select @a; 2697drop table t1; 2698 2699# 2700# MDEV-10916 In trigger's CREATED time microseconds are misinterpreted 2701# 2702 2703create table t1 (i int); 2704set time_zone="+0:00"; 2705SET TIMESTAMP=UNIX_TIMESTAMP('2016-01-01 10:10:10.33'); 2706select now(2); 2707create or replace trigger tr1 after insert on t1 for each row set @a=@a+1; 2708SET TIMESTAMP=UNIX_TIMESTAMP('2016-01-01 10:10:10.99'); 2709select now(2); 2710create or replace trigger tr2 after insert on t1 for each row set @a=@a+1; 2711select now(2); 2712select trigger_name, action_order, created from information_schema.triggers 2713 where event_object_table = 't1' and trigger_schema='test'; 2714drop table t1; 2715set time_zone= @@global.time_zone; 2716 2717--echo # MDEV-12992: Increasing memory consumption 2718--echo with each invocation of trigger 2719--echo # 2720 2721--let $n= 20000 2722 2723CREATE TABLE t1 (a INT); 2724INSERT INTO t1 VALUES (1); 2725CREATE TABLE t2 (b INT); 2726CREATE TRIGGER tr 2727 AFTER UPDATE ON t1 FOR EACH ROW SELECT (SELECT b FROM t2) INTO @x; 2728 2729--disable_query_log 2730--echo # Running $n queries 2731while ($n) 2732{ 2733 UPDATE t1 SET a = 2; 2734 --dec $n 2735} 2736--enable_query_log 2737 2738DROP TABLE t1,t2; 2739 2740--echo # 2741--echo # MDEV-19188 Server Crash When Using a Trigger With A Number of Virtual Columns on INSERT/UPDATE 2742--echo # 2743 2744CREATE TABLE t1 ( 2745 virt1 INT GENERATED ALWAYS AS (0) VIRTUAL, 2746 virt2 INT GENERATED ALWAYS AS (0) VIRTUAL, 2747 virt3 INT GENERATED ALWAYS AS (0) VIRTUAL, 2748 virt4 INT GENERATED ALWAYS AS (0) VIRTUAL, 2749 virt5 INT GENERATED ALWAYS AS (0) VIRTUAL, 2750 virt6 INT GENERATED ALWAYS AS (0) VIRTUAL, 2751 virt7 INT GENERATED ALWAYS AS (0) VIRTUAL, 2752 virt8 INT GENERATED ALWAYS AS (0) VIRTUAL 2753); 2754INSERT INTO t1 () VALUES (); 2755CREATE TRIGGER t1_trigger BEFORE INSERT ON t1 FOR EACH ROW BEGIN END; 2756INSERT INTO t1 () VALUES (); 2757DROP TABLE t1; 2758 2759--echo # 2760--echo # Bug#33141958 - THE FIRST ASAN UAF ISSUE OF MYSQL SERVER 2761--echo # 2762create table t1 (a int); 2763--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG 2764create trigger tr1 after insert on t1 for each row alter table t1 tablespace s2; 2765drop table t1; 2766 2767--echo # 2768--echo # End of 10.2 tests 2769--echo # 2770 2771--echo # 2772--echo # MDEV-12461 TYPE OF and ROW TYPE OF anchored data types 2773--echo # 2774 2775CREATE TABLE t1 (a INT, b INT, total INT); 2776DELIMITER $$; 2777CREATE TRIGGER tr1 BEFORE INSERT ON t1 2778FOR EACH ROW 2779BEGIN 2780 DECLARE va TYPE OF t1.a DEFAULT NEW.a; 2781 DECLARE vb TYPE OF t1.b DEFAULT NEW.b; 2782 SET NEW.total:= va + vb; 2783END; 2784$$ 2785DELIMITER ;$$ 2786INSERT INTO t1 (a,b) VALUES (10, 20); 2787SELECT * FROM t1; 2788DROP TABLE t1; 2789 2790--echo # 2791--echo # End of 10.3 tests 2792--echo # 2793