MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:marc.alff Date:November 7 2006 12:34am
Subject:bk commit into 5.1 tree (malff:1.2341) BUG#23703
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of marcsql. When marcsql does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-11-06 17:34:04-07:00, malff@weblab.(none) +6 -0
  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).
  
  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@stripped, 2006-11-06 17:34:01-07:00, malff@weblab.(none) +27 -0
    DROP TRIGGER IF EXISTS

  mysql-test/r/trigger.result@stripped, 2006-11-06 17:34:01-07:00, malff@weblab.(none) +22 -0
    DROP TRIGGER IF EXISTS

  mysql-test/t/rpl_trigger.test@stripped, 2006-11-06 17:34:01-07:00, malff@weblab.(none) +37 -0
    DROP TRIGGER IF EXISTS

  mysql-test/t/trigger.test@stripped, 2006-11-06 17:34:01-07:00, malff@weblab.(none) +27 -0
    DROP TRIGGER IF EXISTS

  sql/sql_trigger.cc@stripped, 2006-11-06 17:34:01-07:00, malff@weblab.(none) +51 -3
    DROP TRIGGER IF EXISTS

  sql/sql_yacc.yy@stripped, 2006-11-06 17:34:01-07:00, malff@weblab.(none) +3 -2
    DROP TRIGGER IF EXISTS

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	malff
# Host:	weblab.(none)
# Root:	/home/marcsql/TREE/mysql-5.1-23703

--- 1.513/sql/sql_yacc.yy	2006-11-06 17:34:10 -07:00
+++ 1.514/sql/sql_yacc.yy	2006-11-06 17:34:10 -07:00
@@ -7662,11 +7662,12 @@ drop:
             Lex->spname= $4;
             Lex->sql_command = SQLCOM_DROP_EVENT;
           }
-        | DROP TRIGGER_SYM sp_name
+        | DROP TRIGGER_SYM if_exists sp_name
           {
             LEX *lex= Lex;
             lex->sql_command= SQLCOM_DROP_TRIGGER;
-            lex->spname= $3;
+            lex->drop_if_exists= $3;
+            lex->spname= $4;
 	  }
         | DROP TABLESPACE tablespace_name opt_ts_engine opt_ts_wait
           {

--- 1.51/mysql-test/r/trigger.result	2006-11-06 17:34:10 -07:00
+++ 1.52/mysql-test/r/trigger.result	2006-11-06 17:34:10 -07:00
@@ -1241,4 +1241,26 @@ i	j
 2	2
 13	13
 drop table t1;
+drop table if exists t1;
+create table t1(a int, b varchar(50));
+drop trigger not_a_trigger;
+ERROR HY000: Trigger does not exist
+drop trigger if exists not_a_trigger;
+Warnings:
+Note	1360	Trigger does not exist
+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;
+Warnings:
+Note	1360	Trigger does not exist
+insert into t1 values (3, "c");
+select * from t1;
+a	b
+1	In trigger t1_bi
+2	b
+3	c
+drop table t1;
 End of 5.0 tests

--- 1.55/mysql-test/t/trigger.test	2006-11-06 17:34:10 -07:00
+++ 1.56/mysql-test/t/trigger.test	2006-11-06 17:34:10 -07:00
@@ -1498,5 +1498,32 @@ update t1 set i= i+ 10 where j > 2;
 select * from t1;
 drop table t1;
 
+#
+# 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

--- 1.71/sql/sql_trigger.cc	2006-11-06 17:34:10 -07:00
+++ 1.72/sql/sql_trigger.cc	2006-11-06 17:34:10 -07:00
@@ -107,6 +107,7 @@ const LEX_STRING trg_event_type_names[]=
 };
 
 
+static bool check_trigger_exists(THD *thd, sp_name *trig);
 static TABLE_LIST *add_table_for_trigger(THD *thd, sp_name *trig);
 
 class Handle_old_incorrect_sql_modes_hook: public Unknown_key_hook
@@ -181,9 +182,32 @@ bool mysql_create_or_drop_trigger(THD *t
     DBUG_RETURN(TRUE);
   }
 
-  if (!create &&
-      !(tables= add_table_for_trigger(thd, thd->lex->spname)))
-    DBUG_RETURN(TRUE);
+  if (!create)
+  {
+    if (thd->lex->drop_if_exists && ! check_trigger_exists(thd, thd->lex->spname))
+    {
+      push_warning_printf(thd,
+                          MYSQL_ERROR::WARN_LEVEL_NOTE,
+                          ER_TRG_DOES_NOT_EXIST,
+                          ER(ER_TRG_DOES_NOT_EXIST));
+
+      /*
+        Since the trigger does not exist, there is no associated table,
+        and therefore :
+        - no TRIGGER privileges to check,
+        - no trigger to drop
+        - no table to lock/modify, etc
+        so the drop statement is successful.
+      */
+      result= FALSE;
+      /* Still, we need to log the query ... */
+      stmt_query.append(thd->query, thd->query_length);
+      goto end_no_locks;
+    }
+
+    if ( !(tables= add_table_for_trigger(thd, thd->lex->spname)))
+      DBUG_RETURN(TRUE);
+  }
 
   /*
     We don't allow creating triggers on tables in the 'mysql' schema
@@ -279,6 +303,7 @@ end:
   VOID(pthread_mutex_unlock(&LOCK_open));
   start_waiting_global_read_lock(thd);
 
+end_no_locks:
   if (!result)
   {
     if (mysql_bin_log.is_open())
@@ -1131,6 +1156,29 @@ bool Table_triggers_list::get_trigger_in
   DBUG_RETURN(1);
 }
 
+/**
+  Check if a trigger exists.
+  @param thd the current thread
+  @param trig the trigger name
+  @return true if the trigger exists
+*/
+static bool check_trigger_exists(THD *thd, sp_name *trig)
+{
+  char path_buff[FN_REFLEN];
+  LEX_STRING path;
+
+  DBUG_ENTER("check_trigger_exists");
+
+  path.length= build_table_filename(path_buff, sizeof(path_buff)-1,
+                                    trig->m_db.str, trig->m_name.str,
+                                    trigname_file_ext, 0);
+  path.str= path_buff;
+
+  if (access(path_buff, F_OK))
+    DBUG_RETURN(FALSE);
+
+  DBUG_RETURN(TRUE);
+}
 
 /*
   Find trigger's table from trigger identifier and add it to

--- 1.9/mysql-test/r/rpl_trigger.result	2006-11-06 17:34:10 -07:00
+++ 1.10/mysql-test/r/rpl_trigger.result	2006-11-06 17:34:10 -07:00
@@ -946,3 +946,30 @@ c
 ---> Cleaning up...
 DROP TABLE t1;
 DROP TABLE t2;
+drop table if exists t1;
+create table t1(a int, b varchar(50));
+drop trigger not_a_trigger;
+ERROR HY000: Trigger does not exist
+drop trigger if exists not_a_trigger;
+Warnings:
+Note	1360	Trigger does not exist
+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;
+Warnings:
+Note	1360	Trigger does not exist
+insert into t1 values (3, "c");
+select * from t1;
+a	b
+1	In trigger t1_bi
+2	b
+3	c
+select * from t1;
+a	b
+1	In trigger t1_bi
+2	b
+3	c
+drop table t1;

--- 1.10/mysql-test/t/rpl_trigger.test	2006-11-06 17:34:10 -07:00
+++ 1.11/mysql-test/t/rpl_trigger.test	2006-11-06 17:34:10 -07:00
@@ -431,6 +431,43 @@ DROP TABLE t2;
 --sync_with_master
 --connection master
 
+#
+# BUG#23703: DROP TRIGGER needs an IF EXISTS
+#
+
+connection master;
+
+--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;
+
+save_master_pos;
+connection slave;
+sync_with_master;
+
+select * from t1;
+
+connection master;
+
+drop table t1;
 
 #
 # End of tests
Thread
bk commit into 5.1 tree (malff:1.2341) BUG#23703marc.alff7 Nov