diff options
| -rw-r--r-- | mysql-test/r/trigger.result | 12 | ||||
| -rw-r--r-- | mysql-test/t/trigger.test | 17 | ||||
| -rw-r--r-- | sql/sql_delete.cc | 9 | ||||
| -rw-r--r-- | sql/sql_trigger.h | 6 |
4 files changed, 42 insertions, 2 deletions
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 2a2e10dbbd5..b45aaea0cbe 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -174,3 +174,15 @@ create trigger tx1 before insert on t1 for each row set new.x1col = 'x'; insert into t1 values ('y'); drop trigger t1.tx1; drop table t1; +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; +@del_before @del_after +3 3 +drop trigger t1.trg1; +drop trigger t1.trg2; +drop table t1; diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index f842d561dc1..7dc976cf716 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -207,3 +207,20 @@ create trigger tx1 before insert on t1 for each row set new.x1col = 'x'; insert into t1 values ('y'); drop trigger t1.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 t1.trg1; +drop trigger t1.trg2; +drop table t1; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index b8f560b3ef2..165a5409995 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -63,9 +63,14 @@ int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, SQL_LIST *order, if (thd->lex->duplicates == DUP_IGNORE) select_lex->no_error= 1; - /* Test if the user wants to delete all rows */ + /* + Test if the user wants to delete all rows and deletion doesn't have + any side-effects (because of triggers), so we can use optimized + handler::delete_all_rows() method. + */ if (!using_limit && const_cond && (!conds || conds->val_int()) && - !(specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE))) + !(specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) && + !(table->triggers && table->triggers->has_delete_triggers())) { deleted= table->file->records; if (!(error=table->file->delete_all_rows())) diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h index 8ab2ab003f8..d0376f056d9 100644 --- a/sql/sql_trigger.h +++ b/sql/sql_trigger.h @@ -58,5 +58,11 @@ public: static bool check_n_load(THD *thd, const char *db, const char *table_name, TABLE *table); + bool has_delete_triggers() + { + return (bodies[TRG_EVENT_DELETE][TRG_ACTION_BEFORE] || + bodies[TRG_EVENT_DELETE][TRG_ACTION_AFTER]); + } + friend class Item_trigger_field; }; |