MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:guilhem Date:May 6 2005 4:52pm
Subject:bk commit into 5.0 tree (gbichot:1.1930) BUG#10417
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of gbichot. When gbichot 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
  1.1930 05/05/06 18:52:19 gbichot@stripped +4 -0
  Dmitri please review. Fix for BUG#10417 "CREATE TRIGGER not written to binlog":
  writing DROP and CREATE TRIGGER to binlog, disabling binlogging
  of substatements, testing.

  sql/sql_trigger.h
    1.5 05/05/06 18:52:14 gbichot@stripped +8 -0
    disable binlogging of substatements of triggers (even if now triggers
    can't do updates)

  sql/sql_trigger.cc
    1.16 05/05/06 18:52:14 gbichot@stripped +25 -1
    trigger can be used to destroy slave, so only SUPER can create it by default.
    If create|drop goes ok, binlog CREATE TRIGGER and DROP TRIGGER.

  mysql-test/t/rpl_sp.test
    1.3 05/05/06 18:52:14 gbichot@stripped +34 -2
    removing useless lines, plus testing replication of triggers

  mysql-test/r/rpl_sp.result
    1.3 05/05/06 18:52:14 gbichot@stripped +29 -6
    result update

# 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:	gbichot
# Host:	quadita2.mysql.com
# Root:	/nfstmp1/guilhem/mysql-5.0-4ita

--- 1.15/sql/sql_trigger.cc	2005-03-27 14:15:15 +02:00
+++ 1.16/sql/sql_trigger.cc	2005-05-06 18:52:14 +02:00
@@ -98,6 +98,21 @@
   if (wait_if_global_read_lock(thd, 0, 0))
     DBUG_RETURN(TRUE);
 
+  /*
+    There is no DETERMINISTIC clause for triggers, so can't check it.
+    But a trigger can in theory be used to do nasty things (if it supported
+    DROP for example) so we do the check for privileges. For now there is
+    already a stronger test above (see start of the function); but when this
+    stronger test will be removed, the test below will hold.
+  */
+  if (!trust_routine_creators && mysql_bin_log.is_open() &&
+      !(thd->master_access & SUPER_ACL))
+  {
+    my_message(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER,
+	       ER(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER), MYF(0));
+    DBUG_RETURN(TRUE);
+  }
+
   VOID(pthread_mutex_lock(&LOCK_open));
   result= (create ?
            table->triggers->create_trigger(thd, tables):
@@ -109,7 +124,16 @@
   start_waiting_global_read_lock(thd);
 
   if (!result)
-    send_ok(thd);
+    {
+      if (mysql_bin_log.is_open())
+      {
+	thd->clear_error();
+	/* Such a statement can always go directly to binlog, no trans cache */
+	Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+	mysql_bin_log.write(&qinfo);
+      }
+      send_ok(thd);
+    }
 
   DBUG_RETURN(result);
 }

--- 1.4/sql/sql_trigger.h	2005-03-04 14:34:54 +01:00
+++ 1.5/sql/sql_trigger.h	2005-05-06 18:52:14 +02:00
@@ -52,7 +52,15 @@
         FIXME: We should juggle with security context here (because trigger
         should be invoked with creator rights).
       */
+      /*
+	Guilhem puts code to disable binlogging, as in SP/functions, even
+        though currently triggers can't do updates. When triggers can do
+        updates, someone should add such a trigger to rpl_sp.test to verify
+        that the update does NOT go into binlog.
+      */
+      tmp_disable_binlog(thd);
       res= bodies[event][time_type]->execute_function(thd, 0, 0, 0);
+      reenable_binlog(thd);
 
 #ifndef EMBEDDED_LIBRARY
       thd->net.no_send_ok= nsok;

--- 1.2/mysql-test/r/rpl_sp.result	2005-05-05 18:27:23 +02:00
+++ 1.3/mysql-test/r/rpl_sp.result	2005-05-06 18:52:14 +02:00
@@ -158,9 +158,6 @@
 select * from t2;
 a
 3
-select if(compte<>3,"this is broken but documented","this unexpectedly works?") from (select count(*) as compte from t2) as aggreg;
-if(compte<>3,"this is broken but documented","this unexpectedly works?")
-this is broken but documented
 select * from mysql.proc where name="foo4" and db='mysqltest1';
 db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
 mysqltest1	foo4	PROCEDURE	foo4	SQL	CONTAINS_SQL	YES	INVOKER			begin
@@ -197,9 +194,6 @@
 select * from t1;
 a
 21
-select if(compte<>1,"this is broken but documented","this unexpectedly works?") from (select count(*) as compte from t1 where a=20) as aggreg;
-if(compte<>1,"this is broken but documented","this unexpectedly works?")
-this is broken but documented
 select * from t2;
 a
 23
@@ -230,6 +224,35 @@
 mysqltest1	fn1	FUNCTION	fn1	SQL	CONTAINS_SQL	YES	DEFINER		int(11)	begin
 return unix_timestamp();
 end	@	#	#		
+create trigger trg before insert on t1 for each row set new.a= 10;
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
+flush logs;
+delete from t1;
+create trigger trg before insert on t1 for each row set new.a= 10;
+insert into t1 values (1);
+select * from t1;
+a
+10
+select * from t1;
+a
+10
+delete from t1;
+drop trigger t1.trg;
+insert into t1 values (1);
+select * from t1;
+a
+1
+show binlog events in 'master-bin.000002' from 98;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000002	#	Query	1	#	use `mysqltest1`; delete from t1
+master-bin.000002	#	Query	1	#	use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10
+master-bin.000002	#	Query	1	#	use `mysqltest1`; insert into t1 values (1)
+master-bin.000002	#	Query	1	#	use `mysqltest1`; delete from t1
+master-bin.000002	#	Query	1	#	use `mysqltest1`; drop trigger t1.trg
+master-bin.000002	#	Query	1	#	use `mysqltest1`; insert into t1 values (1)
+select * from t1;
+a
+1
 drop function fn1;
 drop database mysqltest1;
 drop user "zedjzlcsjhd"@127.0.0.1;

--- 1.2/mysql-test/t/rpl_sp.test	2005-05-05 18:26:55 +02:00
+++ 1.3/mysql-test/t/rpl_sp.test	2005-05-06 18:52:14 +02:00
@@ -157,7 +157,6 @@
 sync_slave_with_master;
 select * from t1;
 select * from t2;
-select if(compte<>3,"this is broken but documented","this unexpectedly works?") from (select count(*) as compte from t2) as aggreg;
 
 # Test of DROP PROCEDURE
 
@@ -194,7 +193,6 @@
 select * from t2;
 sync_slave_with_master;
 select * from t1;
-select if(compte<>1,"this is broken but documented","this unexpectedly works?") from (select count(*) as compte from t1 where a=20) as aggreg;
 select * from t2;
 
 connection master;
@@ -224,6 +222,40 @@
 --replace_result localhost.localdomain localhost 127.0.0.1 localhost
 --replace_column 13 # 14 #
 select * from mysql.proc where db='mysqltest1';
+
+# And now triggers
+
+connection con1;
+--error 1227;
+create trigger trg before insert on t1 for each row set new.a= 10;
+
+connection master;
+# fn1() above uses timestamps, so in !ps-protocol, the timezone will be
+# binlogged, but in --ps-protocol it will not be (BUG#9359) so
+# the binlog offsets get shifted which spoils SHOW BINLOG EVENTS.
+# To be immune, we take a new binlog.
+flush logs;
+delete from t1;
+# TODO: when triggers can contain an update, test that this update
+# does not go into binlog.
+# I'm not setting user vars in the trigger, because replication of user vars
+# would take care of propagating the user var's value to slave, so even if
+# the trigger was not executed on slave it would not be discovered.
+create trigger trg before insert on t1 for each row set new.a= 10;
+insert into t1 values (1);
+select * from t1;
+sync_slave_with_master;
+select * from t1;
+
+connection master;
+delete from t1;
+drop trigger t1.trg;
+insert into t1 values (1);
+select * from t1;
+--replace_column 2 # 5 #
+show binlog events in 'master-bin.000002' from 98;
+sync_slave_with_master;
+select * from t1;
 
 
 # Clean up
Thread
bk commit into 5.0 tree (gbichot:1.1930) BUG#10417guilhem6 May