Bug#23703 (DROP TRIGGER needs an IF EXISTS)

This change set implements the DROP TRIGGER IF EXISTS functionality.

This fix is considered a bug and not a feature, because without it,
there is no known method to write a database creation script that can create
a trigger without failing, when executed on a database that may or may not
contain already a trigger of the same name.

Implementing this functionality closes an orthogonality gap between triggers
and stored procedures / stored functions (which do support the DROP IF
EXISTS syntax).

In sql_trigger.cc, in mysql_create_or_drop_trigger,
the code has been reordered to:
- perform the tests that do not depend on the file system (access()),
- get the locks (wait_if_global_read_lock, LOCK_open)
- call access()
- perform the operation
- write to the binlog
- unlock (LOCK_open, start_waiting_global_read_lock)

This is to ensure that all the code that depends on the presence of the
trigger file is executed in the same critical section,
and prevents race conditions similar to the case fixed by Bug 14262 :

- thread 1 executes DROP TRIGGER IF EXISTS, access() returns a failure
- thread 2 executes CREATE TRIGGER
- thread 2 logs CREATE TRIGGER
- thread 1 logs DROP TRIGGER IF EXISTS

The patch itself is based on code contributed by the MySQL community,
under the terms of the Contributor License Agreement (See Bug 18161).


mysql-test/r/rpl_trigger.result:
  DROP TRIGGER IF EXISTS
mysql-test/r/trigger.result:
  DROP TRIGGER IF EXISTS
mysql-test/t/rpl_trigger.test:
  DROP TRIGGER IF EXISTS
mysql-test/t/trigger.test:
  DROP TRIGGER IF EXISTS
sql/sql_trigger.cc:
  DROP TRIGGER IF EXISTS
sql/sql_yacc.yy:
  DROP TRIGGER IF EXISTS

1 files changed, 28 insertions, 0 deletions

diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 81334f9b8ef..a9395c12a63 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -1519,4 +1519,32 @@ 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;
+
 --echo End of 5.0 tests