bk commit into 5.0 tree (tnurnberg:1.2392) BUG#23713

[Tatjana A Nuernberg]

Below is the list of changes that have just been committed into a local
5.0 repository of tnurnberg. When tnurnberg 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, 2007-02-11 10:02:57+01:00, tnurnberg@stripped +7 -0
  Bug #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
  
  modify locking so CREATE/DROP TRIGGER will work when we hold an exclusive lock
  on the table in question, or no lock.  fail gracefully for a shared lock.
  sleep while locked by other client/thread.

  mysql-test/r/rpl_trigger.result@stripped, 2007-02-11 10:02:55+01:00, tnurnberg@stripped +0
-2
    Bug #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
    
    We were throwing ER_TRG_NO_DEFINER even on DROP TRIGGER, asking the user to
    improve on code they were just throwing away anyways.

  mysql-test/r/trigger-compat.result@stripped, 2007-02-11 10:02:55+01:00, tnurnberg@stripped
+0 -2
    Bug #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
    
    We were throwing ER_TRG_NO_DEFINER even on DROP TRIGGER, asking the user to
    improve on code they were just throwing away anyways.

  mysql-test/r/trigger.result@stripped, 2007-02-11 10:02:55+01:00, tnurnberg@stripped +32 -0
    Bug #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
    
    show that CREATE/DROP TRIGGER will work when we hold an exclusive lock
    on the table in question, or no lock.  fail gracefully for a shared lock.

  mysql-test/t/trigger.test@stripped, 2007-02-11 10:02:55+01:00, tnurnberg@stripped +42 -0
    Bug #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
    
    show that CREATE/DROP TRIGGER will work when we hold an exclusive lock
    on the table in question, or no lock.  fail gracefully for a shared lock.

  sql/mysql_priv.h@stripped, 2007-02-11 10:02:55+01:00, tnurnberg@stripped +1 -0
    Bug #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
    
    Make wait_while_table_is_used() global so we can use it in sql_trigger.cc

  sql/sql_table.cc@stripped, 2007-02-11 10:02:55+01:00, tnurnberg@stripped +2 -2
    Bug #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
    
    Make wait_while_table_is_used() global so we can use it in sql_trigger.cc

  sql/sql_trigger.cc@stripped, 2007-02-11 10:02:55+01:00, tnurnberg@stripped +31 -10
    Bug #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
    
    modify locking so CREATE/DROP TRIGGER will work when we hold an exclusive lock
    on the table in question, or no lock.  fail gracefully for a shared lock.

# 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:	tnurnberg
# Host:	sin.azundris.com
# Root:	/home/tnurnberg/23713/50-23713

--- 1.429/sql/mysql_priv.h	2007-01-17 19:43:41 +01:00
+++ 1.430/sql/mysql_priv.h	2007-02-11 10:02:55 +01:00
@@ -667,6 +667,7 @@
 				   bool log_query);
 int quick_rm_table(enum db_type base,const char *db,
 		   const char *table_name);
+void wait_while_table_is_used(THD *thd, TABLE *table, enum ha_extra_function function);
 void close_cached_table(THD *thd, TABLE *table);
 bool mysql_rename_tables(THD *thd, TABLE_LIST *table_list);
 bool do_rename(THD *thd, TABLE_LIST *ren_table, char *new_db,

--- 1.332/sql/sql_table.cc	2007-01-20 12:10:17 +01:00
+++ 1.333/sql/sql_table.cc	2007-02-11 10:02:55 +01:00
@@ -1895,8 +1895,8 @@
     Win32 clients must also have a WRITE LOCK on the table !
 */
 
-static void wait_while_table_is_used(THD *thd,TABLE *table,
-				     enum ha_extra_function function)
+void wait_while_table_is_used(THD *thd,TABLE *table,
+                              enum ha_extra_function function)
 {
   DBUG_PRINT("enter",("table: %s", table->s->table_name));
   DBUG_ENTER("wait_while_table_is_used");

--- 1.4/mysql-test/r/trigger-compat.result	2006-05-12 18:58:49 +02:00
+++ 1.5/mysql-test/r/trigger-compat.result	2007-02-11 10:02:55 +01:00
@@ -37,8 +37,6 @@
 NULL	mysqltest_db1	wl2818_trg1	INSERT	NULL	mysqltest_db1	t1	0	NULL	INSERT INTO t2
VALUES(CURRENT_USER())	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		
 NULL	mysqltest_db1	wl2818_trg2	INSERT	NULL	mysqltest_db1	t1	0	NULL	INSERT INTO t2
VALUES(CURRENT_USER())	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		mysqltest_dfn@localhost
 DROP TRIGGER wl2818_trg1;
-Warnings:
-Warning	1454	No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger
will be activated under the authorization of the invoker, which may have insufficient
privileges. Please recreate the trigger.
 DROP TRIGGER wl2818_trg2;
 use mysqltest_db1;
 DROP TABLE t1;

--- 1.50/mysql-test/r/trigger.result	2006-11-13 23:38:34 +01:00
+++ 1.51/mysql-test/r/trigger.result	2007-02-11 10:02:55 +01:00
@@ -1278,4 +1278,36 @@
 2	b
 3	c
 drop table t1;
+create table t1 (i int);
+create trigger t1_bi before insert on t1 for each row begin end;
+create trigger t1_bi before insert on t1 for each row begin end;
+ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same
action time and event for one table'
+drop trigger t1_bi;
+drop trigger t1_bi;
+ERROR HY000: Trigger does not exist
+lock tables t1 read;
+create trigger t1_bi before insert on t1 for each row begin end;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+create trigger t1_bi before insert on t1 for each row begin end;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+drop trigger t1_bi;
+ERROR HY000: Trigger does not exist
+unlock tables;
+create trigger t1_bi before insert on t1 for each row begin end;
+lock tables t1 read;
+create trigger t1_bi before insert on t1 for each row begin end;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+drop trigger t1_bi;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+drop trigger t1_bi;
+lock tables t1 write;
+create trigger t1_bi before insert on t1 for each row begin end;
+create trigger t1_bi before insert on t1 for each row begin end;
+ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same
action time and event for one table'
+drop trigger t1_bi;
+drop trigger t1_bi;
+ERROR HY000: Trigger does not exist
+unlock tables;
+drop table if exists t1;
 End of 5.0 tests

--- 1.58/mysql-test/t/trigger.test	2007-01-11 15:43:42 +01:00
+++ 1.59/mysql-test/t/trigger.test	2007-02-11 10:02:55 +01:00
@@ -1554,4 +1554,46 @@
 
 drop table t1;
 
+#
+# Bug #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
+#
+
+create table t1 (i int);
+
+create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_NOT_SUPPORTED_YET
+create trigger t1_bi before insert on t1 for each row begin end;
+drop trigger t1_bi;
+--error ER_TRG_DOES_NOT_EXIST
+drop trigger t1_bi;
+
+lock tables t1 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_TRG_DOES_NOT_EXIST
+drop trigger t1_bi;
+unlock tables;
+
+create trigger t1_bi before insert on t1 for each row begin end;
+lock tables t1 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop trigger t1_bi;
+unlock tables;
+drop trigger t1_bi;
+
+lock tables t1 write;
+create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_NOT_SUPPORTED_YET
+create trigger t1_bi before insert on t1 for each row begin end;
+drop trigger t1_bi;
+--error ER_TRG_DOES_NOT_EXIST
+drop trigger t1_bi;
+unlock tables;
+
+drop table if exists t1;
+
 --echo End of 5.0 tests

--- 1.61/sql/sql_trigger.cc	2006-12-23 20:04:27 +01:00
+++ 1.62/sql/sql_trigger.cc	2007-02-11 10:02:55 +01:00
@@ -136,6 +136,7 @@
   LEX_STRING *trigger_table_value;
 };
 
+
 /*
   Create or drop trigger for table.
 
@@ -165,7 +166,7 @@
     This is a good candidate for a minor refactoring.
   */
   TABLE *table;
-  bool result= TRUE;
+  bool result= TRUE, need_unlock= FALSE;
   String stmt_query;
 
   DBUG_ENTER("mysql_create_or_drop_trigger");
@@ -223,16 +224,17 @@
     DBUG_RETURN(TRUE);
   }
 
+
   /*
     We don't want perform our operations while global read lock is held
     so we have to wait until its end and then prevent it from occurring
     again until we are done. (Acquiring LOCK_open is not enough because
     global read lock is held without holding LOCK_open).
   */
-  if (wait_if_global_read_lock(thd, 0, 1))
-    DBUG_RETURN(TRUE);
+  /* end_active_trans(thd) was called in sql_parse */
 
-  VOID(pthread_mutex_lock(&LOCK_open));
+  if (!thd->locked_tables && wait_if_global_read_lock(thd, 0, 1))
+    DBUG_RETURN(1);
 
   if (!create)
   {
@@ -259,6 +261,9 @@
     }
   }
 
+  if (tables)
+    mysql_ha_flush(thd, tables, MYSQL_HA_CLOSE_FINAL, FALSE);
+
   /* We should have only one table in table list. */
   DBUG_ASSERT(tables->next_global == 0);
 
@@ -269,19 +274,21 @@
     goto end;
   }
 
-  if (lock_table_names(thd, tables))
-    goto end;
-
   /* We also don't allow creation of triggers on views. */
   tables->required_type= FRMTYPE_TABLE;
 
-  if (reopen_name_locked_table(thd, tables))
+  if (!(tables->table= open_ltable(thd,tables,TL_WRITE_ALLOW_READ)))
   {
-    unlock_table_name(thd, tables);
     goto end;
   }
   table= tables->table;
 
+  VOID(pthread_mutex_lock(&LOCK_open));
+
+  need_unlock= TRUE;
+
+  wait_while_table_is_used(thd, table, HA_EXTRA_FORCE_REOPEN);
+
   if (!table->triggers)
   {
     if (!create)
@@ -311,9 +318,23 @@
                             FALSE);
       mysql_bin_log.write(&qinfo);
     }
+    if (need_unlock)
+    {
+      if (!thd->locked_tables ||
+          (table &&
+           (close_data_tables(thd, table->s->db, table->s->table_name) ||
+            reopen_tables(thd, 1, 0))))
+      {						// This shouldn't happen
+        close_cached_table(thd, table);		// Remove lock for table
+      }
+    }
+  }
+
+  if (need_unlock)
+  {
+    VOID(pthread_mutex_unlock(&LOCK_open));
   }
 
-  VOID(pthread_mutex_unlock(&LOCK_open));
   start_waiting_global_read_lock(thd);
 
   if (!result)

--- 1.11/mysql-test/r/rpl_trigger.result	2006-11-13 23:38:34 +01:00
+++ 1.12/mysql-test/r/rpl_trigger.result	2007-02-11 10:02:55 +01:00
@@ -883,8 +883,6 @@
 @
 root@localhost
 DROP TRIGGER trg1;
-Warnings:
-Warning	1454	No definer attribute for trigger 'test'.'trg1'. The trigger will be
activated under the authorization of the invoker, which may have insufficient privileges.
Please recreate the trigger.
 DROP TABLE t1;
 DROP TABLE t2;
 STOP SLAVE;