List:Commits« Previous MessageNext Message »
From:Dao-Gang.Qu Date:August 5 2009 6:34am
Subject:bzr commit into mysql-5.1-bugteam branch (Dao-Gang.Qu:3034) Bug#44331
View as plain text  
#At file:///home/daogangq/mysql/bzrwork/bug44331/5.1-bugteam/ based on revid:epotemkin@stripped

 3034 Dao-Gang.Qu@stripped	2009-08-05
      Bug #44331  Restore of database with events produces warning in replication
      
      If an EVENT is created without the DEFINER clause set explicitly or with it set  
      to CURRENT_USER, the master and slaves become inconsistent. This issue stems from 
      the fact that in both cases, the DEFINER is set to the CURRENT_USER of the current 
      thread. On the master, the CURRENT_USER is the mysqld's user, while on the slave,  
      the CURRENT_USER is empty for the SQL Thread which is responsible for executing 
      the statement.
      
      To fix the problem, we do what follows. If the definer is not set explicitly,  
      a DEFINER clause is added when writing the query into binlog; if 'CURRENT_USER' is 
      used as the DEFINER, it is replaced with the value of the current user before 
      writing to binlog.
     @ mysql-test/suite/rpl/r/rpl_drop_if_exists.result
        Updated the result file after fixing bug#44331
     @ mysql-test/suite/rpl/r/rpl_events.result
        Test result of Bug#44331
     @ mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result
        Updated the result file after fixing bug#44331
     @ mysql-test/suite/rpl/t/rpl_events.test
        Added test to verify if the definer is consistent between master and slave
        when the event is created without the DEFINER clause set explicitly or the
        DEFINER is set to CURRENT_USER
     @ sql/events.cc
        The "create_query_string" function is added to create a new query string  
        and insert the DEFINER clause to it.
        Added a new parameter "definer_set_mode" to 'Events::create_event' function to 
        indicate the mode of the definer.
        The value of CURRENT_USER will be written into the binary log 
        as default definer for the SQL thread.

    modified:
      mysql-test/suite/rpl/r/rpl_drop_if_exists.result
      mysql-test/suite/rpl/r/rpl_events.result
      mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result
      mysql-test/suite/rpl/t/rpl_events.test
      sql/events.cc
      sql/events.h
      sql/sql_acl.h
      sql/sql_lex.h
      sql/sql_parse.cc
      sql/sql_yacc.yy
=== modified file 'mysql-test/suite/rpl/r/rpl_drop_if_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_drop_if_exists.result	2009-02-11 17:46:43 +0000
+++ b/mysql-test/suite/rpl/r/rpl_drop_if_exists.result	2009-08-05 06:34:17 +0000
@@ -43,7 +43,7 @@ master-bin.000001	#	Query	#	#	use `test`
 master-bin.000001	#	Query	#	#	DROP DATABASE IF EXISTS db_bug_13684
 master-bin.000001	#	Query	#	#	CREATE DATABASE db_bug_13684
 master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE db_bug_13684.t (a int)
-master-bin.000001	#	Query	#	#	use `test`; CREATE EVENT db_bug_13684.e
+master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` EVENT db_bug_13684.e
 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
 DO
 UPDATE db_bug_13684.t SET a = a + 1
@@ -75,7 +75,7 @@ master-bin.000001	#	Query	#	#	use `test`
 master-bin.000001	#	Query	#	#	DROP DATABASE IF EXISTS db_bug_13684
 master-bin.000001	#	Query	#	#	CREATE DATABASE db_bug_13684
 master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE db_bug_13684.t (a int)
-master-bin.000001	#	Query	#	#	use `test`; CREATE EVENT db_bug_13684.e
+master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` EVENT db_bug_13684.e
 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
 DO
 UPDATE db_bug_13684.t SET a = a + 1

=== modified file 'mysql-test/suite/rpl/r/rpl_events.result'
--- a/mysql-test/suite/rpl/r/rpl_events.result	2009-01-30 13:44:49 +0000
+++ b/mysql-test/suite/rpl/r/rpl_events.result	2009-08-05 06:34:17 +0000
@@ -191,5 +191,36 @@ select * from t28953;
 END;|
 ALTER EVENT event1 RENAME TO event2;
 DROP EVENT event2;
+CREATE TABLE test.t1(details CHAR(30));
+/* comment befor create */ CREATE /* comment after create */ EVENT /* comment after event */ event44331_1
+ON SCHEDULE AT CURRENT_TIMESTAMP
+ON COMPLETION PRESERVE DISABLE
+DO INSERT INTO test.t1 VALUES('event event44331_1 fired - no definer');
+CREATE /* comment after create */ DEFINER=CURRENT_USER EVENT /* comment after event */  event44331_2
+ON SCHEDULE AT CURRENT_TIMESTAMP
+ON COMPLETION PRESERVE DISABLE
+DO INSERT INTO test.t1 VALUES('event event44331_2 fired - DEFINER=CURRENT_USER');
+CREATE /* comment after create */ DEFINER=CURRENT_USER() /* comment after CURRENT_USER() */ EVENT event44331_3
+ON SCHEDULE AT CURRENT_TIMESTAMP
+ON COMPLETION PRESERVE DISABLE
+DO INSERT INTO test.t1 VALUES('event event44331_3 fired - DEFINER=CURRENT_USER() function');
+#on master 
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' || EVENT_NAME='event44331_3';
+EVENT_SCHEMA	EVENT_NAME	DEFINER
+test	event44331_1	root@localhost
+test	event44331_2	root@localhost
+test	event44331_3	root@localhost
+#on slave
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' || EVENT_NAME='event44331_3';
+EVENT_SCHEMA	EVENT_NAME	DEFINER
+test	event44331_1	root@localhost
+test	event44331_2	root@localhost
+test	event44331_3	root@localhost
 SET @@global.event_scheduler= @old_event_scheduler;
 DROP TABLE t28953;
+DROP TABLE t1;
+DROP EVENT event44331_1;
+DROP EVENT event44331_2;
+DROP EVENT event44331_3;

=== modified file 'mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result'
--- a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result	2009-05-31 05:44:41 +0000
+++ b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result	2009-08-05 06:34:17 +0000
@@ -690,7 +690,7 @@ test_rpl	e1	root@localhost	SYSTEM	RECURR
 USE test_rpl;
 SHOW EVENTS;
 Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
-test_rpl	e1	@	SYSTEM	RECURRING	NULL	1	#	#	NULL	SLAVESIDE_DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
+test_rpl	e1	root@localhost	SYSTEM	RECURRING	NULL	1	#	#	NULL	SLAVESIDE_DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
 ==========MASTER==========
 SELECT COUNT(*) FROM t1;
 COUNT(*)
@@ -1076,7 +1076,7 @@ master-bin.000001	#	Query	1	#	use `test_
 master-bin.000001	#	Query	1	#	BEGIN
 master-bin.000001	#	Query	1	#	use `test_rpl`; INSERT INTO t1 VALUES(1, 'test1')
 master-bin.000001	#	Xid	1	#	#
-master-bin.000001	#	Query	1	#	use `test_rpl`; CREATE EVENT e1 ON SCHEDULE EVERY '1' SECOND COMMENT 'e_second_comment' DO DELETE FROM t1
+master-bin.000001	#	Query	1	#	use `test_rpl`; CREATE DEFINER=`root`@`localhost` EVENT e1 ON SCHEDULE EVERY '1' SECOND COMMENT 'e_second_comment' DO DELETE FROM t1
 master-bin.000001	#	Query	1	#	use `test_rpl`; ALTER EVENT e1 RENAME TO e2
 master-bin.000001	#	Query	1	#	use `test_rpl`; DROP EVENT e2
 master-bin.000001	#	Query	1	#	BEGIN

=== modified file 'mysql-test/suite/rpl/t/rpl_events.test'
--- a/mysql-test/suite/rpl/t/rpl_events.test	2009-01-30 13:44:49 +0000
+++ b/mysql-test/suite/rpl/t/rpl_events.test	2009-08-05 06:34:17 +0000
@@ -46,12 +46,44 @@ connection master;
 
 DROP EVENT event2;
 
-sync_slave_with_master;
+#
+# BUG#44331
+# This test verifies if the definer is consistent between master and slave,
+# when the event is created without the DEFINER clause set explicitly or the 
+# DEFINER is set to CURRENT_USER
+#
+CREATE TABLE test.t1(details CHAR(30));
+
+/* comment befor create */ CREATE /* comment after create */ EVENT /* comment after event */ event44331_1
+  ON SCHEDULE AT CURRENT_TIMESTAMP
+  ON COMPLETION PRESERVE DISABLE
+  DO INSERT INTO test.t1 VALUES('event event44331_1 fired - no definer');
+
+CREATE /* comment after create */ DEFINER=CURRENT_USER EVENT /* comment after event */  event44331_2
+  ON SCHEDULE AT CURRENT_TIMESTAMP
+  ON COMPLETION PRESERVE DISABLE
+  DO INSERT INTO test.t1 VALUES('event event44331_2 fired - DEFINER=CURRENT_USER');
+            
+CREATE /* comment after create */ DEFINER=CURRENT_USER() /* comment after CURRENT_USER() */ EVENT event44331_3
+  ON SCHEDULE AT CURRENT_TIMESTAMP
+  ON COMPLETION PRESERVE DISABLE
+  DO INSERT INTO test.t1 VALUES('event event44331_3 fired - DEFINER=CURRENT_USER() function');
 
-# Doing cleanup of the table referred to in the event to guarantee
-# that there is no bad timing cauing it to try to access the table.
+--echo #on master 
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+  where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' || EVENT_NAME='event44331_3';
+
+sync_slave_with_master;
+connection slave;
+--echo #on slave
+select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
+  where EVENT_NAME='event44331_1' || EVENT_NAME='event44331_2' || EVENT_NAME='event44331_3';
 
 connection master;
 SET @@global.event_scheduler= @old_event_scheduler;
 DROP TABLE t28953;
+DROP TABLE t1;
+DROP EVENT event44331_1;
+DROP EVENT event44331_2;
+DROP EVENT event44331_3;
 sync_slave_with_master;

=== modified file 'sql/events.cc'
--- a/sql/events.cc	2009-04-09 06:22:06 +0000
+++ b/sql/events.cc	2009-08-05 06:34:17 +0000
@@ -342,12 +342,82 @@ common_1_lev_code:
 
 
 /**
+  Create a new query string and insert the DEFINER clause to it.
+  
+  @param[in] thd                 Thread handler
+  @param[in] buf                 Query string
+  @param[in] definer_set_mode    The mode of the definer is set
+
+  @return
+             0           ok
+             1           error
+*/
+static int
+create_query_string(THD *thd, String *buf, int definer_set_mode)
+{ 
+  const int length_event_kw= 5;
+  if (definer_set_mode == DEFINER_NOT_SET)
+  {
+    /* Handle no definer case */
+    const char* offset_event_kw= thd->lex->offset[0];
+    /* Append the "CREATE" part of the query */
+    if (buf->append(thd->query, offset_event_kw - thd->query - length_event_kw))
+      return 1;
+    /* Append definer */
+    append_definer(thd, buf, &(thd->lex->definer->user), &(thd->lex->definer->host));
+    /* Append the left part of thd->query after "CREATE" part */
+    if (buf->append(offset_event_kw - length_event_kw))
+      return 1;
+  }
+  else if (definer_set_mode == DEFINER_CURRENT_USER)
+  { 
+    const char* offset_definer_kw= thd->lex->offset[0];
+    const char* offset_event_kw= thd->lex->offset[1];
+    const int length_definer_kw= 7;
+    if ((offset_event_kw - 1)[0] == 't' || (offset_event_kw - 1)[0] == 'T')
+    { 
+      /* Handle definer is set to CURRENT_USER case */
+      /* Append the "CREATE" part of the query */
+      if (buf->append(thd->query, offset_definer_kw - thd->query - length_definer_kw))
+        return 1;
+      /* Append definer */
+      append_definer(thd, buf, &(thd->lex->definer->user), &(thd->lex->definer->host));
+      /* Append the left part of thd->query after "DEFINER" part */
+      if (buf->append(offset_event_kw - length_event_kw))
+        return 1;      
+    }
+    else
+    {
+      /* Handle definer is set to CURRENT_USER() function case */
+      /* Append the "CREATE" part of the query */
+      if (buf->append(thd->query, offset_definer_kw - thd->query - length_definer_kw))
+        return 1;
+      /* Append definer */
+      append_definer(thd, buf, &(thd->lex->definer->user), &(thd->lex->definer->host));
+      /* Append the left part of thd->query after "DEFINER" part  */
+      if (buf->append(offset_event_kw))
+        return 1;
+    }
+  }
+  else
+    return 1;
+  
+  return 0;
+}
+
+
+/**
   Create a new event.
 
   @param[in,out]  thd            THD
   @param[in]      parse_data     Event's data from parsing stage
   @param[in]      if_not_exists  Whether IF NOT EXISTS was
                                  specified
+  @param[in]      definer_set_mode  The mode of the definer is set
+                       DEFINER_SET           the definer is set explicitly
+                       DEFINER_NOT_SET       the definer is not set
+                       DEFINER_CURRENT_USER  the definer is set to CURRENT_USER
+
   In case there is an event with the same name (db) and
   IF NOT EXISTS is specified, an warning is put into the stack.
   @sa Events::drop_event for the notes about locking, pre-locking
@@ -359,7 +429,7 @@ common_1_lev_code:
 
 bool
 Events::create_event(THD *thd, Event_parse_data *parse_data,
-                     bool if_not_exists)
+                     bool if_not_exists, int definer_set_mode)
 {
   int ret;
   DBUG_ENTER("Events::create_event");
@@ -439,7 +509,21 @@ Events::create_event(THD *thd, Event_par
     {
       /* Binlog the create event. */
       DBUG_ASSERT(thd->query && thd->query_length);
-      write_bin_log(thd, TRUE, thd->query, thd->query_length);
+      String log_query;
+      if (definer_set_mode)
+      {
+        if (create_query_string(thd, &log_query,definer_set_mode))
+        {
+          sql_print_error("Event Error: An error occurred while creating query string, "
+                          "before writing it into binary log.");
+          DBUG_RETURN(TRUE);
+        }
+        /* If the definer is not set or set to CURRENT_USER, the value of CURRENT_USER 
+           will be written into the binary log as the definer for the SQL thread. */
+        write_bin_log(thd, TRUE, log_query.c_ptr(), log_query.length());
+      }
+      else
+        write_bin_log(thd, TRUE, thd->query, thd->query_length);
     }
   }
   pthread_mutex_unlock(&LOCK_event_metadata);

=== modified file 'sql/events.h'
--- a/sql/events.h	2007-08-15 15:08:44 +0000
+++ b/sql/events.h	2009-08-05 06:34:17 +0000
@@ -107,7 +107,8 @@ public:
   switch_event_scheduler_state(enum enum_opt_event_scheduler new_state);
 
   static bool
-  create_event(THD *thd, Event_parse_data *parse_data, bool if_exists);
+  create_event(THD *thd, Event_parse_data *parse_data,
+               bool if_exists, int definer_set_mode);
 
   static bool
   update_event(THD *thd, Event_parse_data *parse_data,

=== modified file 'sql/sql_acl.h'
--- a/sql/sql_acl.h	2009-05-29 13:37:54 +0000
+++ b/sql/sql_acl.h	2009-08-05 06:34:17 +0000
@@ -43,6 +43,11 @@
 #define CREATE_USER_ACL (1L << 25)
 #define EVENT_ACL       (1L << 26)
 #define TRIGGER_ACL     (1L << 27)
+/* Define the mode of the definer */
+#define DEFINER_SET           0
+#define DEFINER_NOT_SET       1
+#define DEFINER_CURRENT_USER  2
+
 /*
   don't forget to update
   1. static struct show_privileges_st sys_privileges[]

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2009-07-10 23:12:13 +0000
+++ b/sql/sql_lex.h	2009-08-05 06:34:17 +0000
@@ -1529,6 +1529,8 @@ typedef struct st_lex : public Query_tab
   SELECT_LEX *current_select;
   /* list of all SELECT_LEX */
   SELECT_LEX *all_selects_list;
+  /* Record the current offset address of query */
+  const char* offset[2];
 
   char *length,*dec,*change;
   LEX_STRING name;

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-07-16 12:43:17 +0000
+++ b/sql/sql_parse.cc	2009-08-05 06:34:17 +0000
@@ -3708,6 +3708,17 @@ end_with_restore_list:
       break;
     }
 
+    /* The definer_set_mode is assigned an initial value DEFINER_SET
+       to indicate that the definer is set explicitly */
+    int definer_set_mode= DEFINER_SET;
+    if (!lex->definer)
+      /* The definer is not set */
+      definer_set_mode= DEFINER_NOT_SET;
+    else if ((lex->definer->user.str == thd->security_ctx->priv_user) &&
+             (lex->definer->host.str == thd->security_ctx->priv_host))
+      /* The definer is set to CURRENT_USER */
+      definer_set_mode= DEFINER_CURRENT_USER;
+    
     res= sp_process_definer(thd);
     if (res)
       break;
@@ -3717,7 +3728,8 @@ end_with_restore_list:
     {
       bool if_not_exists= (lex->create_info.options &
                            HA_LEX_CREATE_IF_NOT_EXISTS);
-      res= Events::create_event(thd, lex->event_parse_data, if_not_exists);
+      res= Events::create_event(thd, lex->event_parse_data, 
+                                if_not_exists, definer_set_mode);
       break;
     }
     case SQLCOM_ALTER_EVENT:

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2009-07-16 12:43:17 +0000
+++ b/sql/sql_yacc.yy	2009-08-05 06:34:17 +0000
@@ -1744,6 +1744,7 @@ create:
             Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED;
             Lex->create_view_suid= TRUE;
           }
+          record_offset
           view_or_trigger_or_sp_or_event
           {}
         | CREATE USER clear_privileges grant_list
@@ -1764,6 +1765,14 @@ create:
           }
         ;
 
+record_offset:
+          {
+            /* Record the current offset address */
+            Lex_input_stream *lip= YYLIP;
+            Lex->offset[0]= lip->get_ptr();
+          }
+        ;
+
 server_def:
           SERVER_SYM
           ident_or_text
@@ -13021,6 +13030,9 @@ definer:
           DEFINER_SYM EQ user
           {
             YYTHD->lex->definer= get_current_user(YYTHD, $3);
+            /* Record the current offset address */
+            Lex_input_stream *lip= YYLIP;
+            Lex->offset[1]= lip->get_ptr();
           }
         ;
 


Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20090805063417-hh1c1cwkdvwg0kdi.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (Dao-Gang.Qu:3034) Bug#44331Dao-Gang.Qu5 Aug