List:Commits« Previous MessageNext Message »
From:Luis Soares Date:February 4 2009 9:41am
Subject:bzr commit into mysql-5.1-bugteam branch (luis.soares:2768) Bug#13684
View as plain text  
#At file:///home/lsoares/Workspace/mysql-server/bugfix/13684/5.1-bugteam/ based on revid:patrick.crews@stripped

 2768 Luis Soares	2009-02-04
      BUG#13684: SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine does not exist
      
      There is an inconsistency with DROP DATABASE IF EXISTS, DROP
      TABLE IF EXISTS and DROP VIEW IF EXISTS: those are binlogged even
      if the DB or TABLE does not exist, whereas DROP PROCEDURE IF
      EXISTS does not. It would be nice or at least consistent if DROP
      PROCEDURE/STATEMENT worked the same too.
      
      Fixed DROP PROCEDURE|FUNCTION IF EXISTS by adding a call to
      write_bin_log in mysql_execute_command. Checked also if all
      documented "DROP (...) IF EXISTS" get binlogged. Left out DROP
      SERVER IF EXISTS because it seems that it only gets binlogged when
      using row event (see BUG#25705).
added:
  mysql-test/suite/rpl/r/rpl_drop_if_exists.result
  mysql-test/suite/rpl/t/rpl_drop_if_exists.test
modified:
  mysql-test/suite/rpl/r/rpl_sp.result
  sql/sql_parse.cc

per-file messages:
  mysql-test/suite/rpl/r/rpl_sp.result
    This test needed an update on its result file due to the extra binlog events generated by the drop if exists function|procedure.
=== added file 'mysql-test/suite/rpl/r/rpl_drop_if_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_drop_if_exists.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_drop_if_exists.result	2009-02-04 09:41:30 +0000
@@ -0,0 +1,97 @@
+DROP PROCEDURE IF EXISTS db_bug_13684.p;
+DROP FUNCTION IF EXISTS db_bug_13684.f;
+DROP TRIGGER IF EXISTS db_bug_13684.tr;
+DROP VIEW IF EXISTS db_bug_13684.v;
+DROP EVENT IF EXISTS db_bug_13684.e;
+DROP TABLE IF EXISTS db_bug_13684.t;
+DROP DATABASE IF EXISTS db_bug_13684;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; DROP PROCEDURE IF EXISTS db_bug_13684.p
+master-bin.000001	#	Query	#	#	use `test`; DROP FUNCTION IF EXISTS db_bug_13684.f
+master-bin.000001	#	Query	#	#	use `test`; DROP TRIGGER IF EXISTS db_bug_13684.tr
+master-bin.000001	#	Query	#	#	use `test`; DROP VIEW IF EXISTS db_bug_13684.v
+master-bin.000001	#	Query	#	#	use `test`; DROP EVENT IF EXISTS db_bug_13684.e
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS db_bug_13684.t
+master-bin.000001	#	Query	#	#	DROP DATABASE IF EXISTS db_bug_13684
+CREATE DATABASE db_bug_13684;
+CREATE TABLE db_bug_13684.t (a int);
+CREATE EVENT db_bug_13684.e
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
+DO
+UPDATE db_bug_13684.t SET a = a + 1;
+CREATE VIEW db_bug_13684.v 
+AS SELECT * FROM db_bug_13684.t;
+CREATE TRIGGER db_bug_13684.tr BEFORE INSERT ON db_bug_13684.t
+FOR EACH ROW BEGIN
+END;
+CREATE PROCEDURE db_bug_13684.p (OUT p1 INT)
+BEGIN
+END;
+CREATE FUNCTION db_bug_13684.f (s CHAR(20))
+RETURNS CHAR(50) DETERMINISTIC
+RETURN s;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; DROP PROCEDURE IF EXISTS db_bug_13684.p
+master-bin.000001	#	Query	#	#	use `test`; DROP FUNCTION IF EXISTS db_bug_13684.f
+master-bin.000001	#	Query	#	#	use `test`; DROP TRIGGER IF EXISTS db_bug_13684.tr
+master-bin.000001	#	Query	#	#	use `test`; DROP VIEW IF EXISTS db_bug_13684.v
+master-bin.000001	#	Query	#	#	use `test`; DROP EVENT IF EXISTS db_bug_13684.e
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS db_bug_13684.t
+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
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
+DO
+UPDATE db_bug_13684.t SET a = a + 1
+master-bin.000001	#	Query	#	#	use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db_bug_13684`.`v` AS SELECT * FROM db_bug_13684.t
+master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER db_bug_13684.tr BEFORE INSERT ON db_bug_13684.t
+FOR EACH ROW BEGIN
+END
+master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `db_bug_13684`.`p`(OUT p1 INT)
+BEGIN
+END
+master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `db_bug_13684`.`f`(s CHAR(20)) RETURNS char(50) CHARSET latin1
+    DETERMINISTIC
+RETURN s
+DROP PROCEDURE IF EXISTS db_bug_13684.p;
+DROP FUNCTION IF EXISTS db_bug_13684.f;
+DROP TRIGGER IF EXISTS db_bug_13684.tr;
+DROP VIEW IF EXISTS db_bug_13684.v;
+DROP EVENT IF EXISTS db_bug_13684.e;
+DROP TABLE IF EXISTS db_bug_13684.t;
+DROP DATABASE IF EXISTS db_bug_13684;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; DROP PROCEDURE IF EXISTS db_bug_13684.p
+master-bin.000001	#	Query	#	#	use `test`; DROP FUNCTION IF EXISTS db_bug_13684.f
+master-bin.000001	#	Query	#	#	use `test`; DROP TRIGGER IF EXISTS db_bug_13684.tr
+master-bin.000001	#	Query	#	#	use `test`; DROP VIEW IF EXISTS db_bug_13684.v
+master-bin.000001	#	Query	#	#	use `test`; DROP EVENT IF EXISTS db_bug_13684.e
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS db_bug_13684.t
+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
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
+DO
+UPDATE db_bug_13684.t SET a = a + 1
+master-bin.000001	#	Query	#	#	use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db_bug_13684`.`v` AS SELECT * FROM db_bug_13684.t
+master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER db_bug_13684.tr BEFORE INSERT ON db_bug_13684.t
+FOR EACH ROW BEGIN
+END
+master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `db_bug_13684`.`p`(OUT p1 INT)
+BEGIN
+END
+master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `db_bug_13684`.`f`(s CHAR(20)) RETURNS char(50) CHARSET latin1
+    DETERMINISTIC
+RETURN s
+master-bin.000001	#	Query	#	#	use `test`; DROP PROCEDURE IF EXISTS db_bug_13684.p
+master-bin.000001	#	Query	#	#	use `test`; DROP FUNCTION IF EXISTS db_bug_13684.f
+master-bin.000001	#	Query	#	#	use `test`; DROP TRIGGER IF EXISTS db_bug_13684.tr
+master-bin.000001	#	Query	#	#	use `test`; DROP VIEW IF EXISTS db_bug_13684.v
+master-bin.000001	#	Query	#	#	use `test`; DROP EVENT IF EXISTS db_bug_13684.e
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS db_bug_13684.t
+master-bin.000001	#	Query	#	#	DROP DATABASE IF EXISTS db_bug_13684

=== modified file 'mysql-test/suite/rpl/r/rpl_sp.result'
--- a/mysql-test/suite/rpl/r/rpl_sp.result	2008-10-24 12:51:06 +0000
+++ b/mysql-test/suite/rpl/r/rpl_sp.result	2009-02-04 09:41:30 +0000
@@ -511,6 +511,7 @@ master-bin.000001	#	Query	1	#	use `mysql
 master-bin.000001	#	Query	1	#	use `mysqltest1`; drop function fn1
 master-bin.000001	#	Query	1	#	drop database mysqltest1
 master-bin.000001	#	Query	1	#	drop user "zedjzlcsjhd"@127.0.0.1
+master-bin.000001	#	Query	1	#	use `test`; drop function if exists f1
 master-bin.000001	#	Query	1	#	use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
     READS SQL DATA
 begin
@@ -526,12 +527,15 @@ master-bin.000001	#	Query	1	#	use `test`
 master-bin.000001	#	Query	1	#	use `test`; insert into t1 (a) values (f1())
 master-bin.000001	#	Query	1	#	use `test`; drop view v1
 master-bin.000001	#	Query	1	#	use `test`; drop function f1
+master-bin.000001	#	Query	1	#	use `test`; DROP PROCEDURE IF EXISTS p1
 master-bin.000001	#	Query	1	#	use `test`; DROP TABLE IF EXISTS t1
 master-bin.000001	#	Query	1	#	use `test`; CREATE TABLE t1(col VARCHAR(10))
 master-bin.000001	#	Query	1	#	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(arg VARCHAR(10))
 INSERT INTO t1 VALUES(arg)
 master-bin.000001	#	Query	1	#	use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci'))
 master-bin.000001	#	Query	1	#	use `test`; DROP PROCEDURE p1
+master-bin.000001	#	Query	1	#	use `test`; DROP PROCEDURE IF EXISTS p1
+master-bin.000001	#	Query	1	#	use `test`; DROP FUNCTION IF EXISTS f1
 master-bin.000001	#	Query	1	#	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
 SET @a = 1
 master-bin.000001	#	Query	1	#	use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
@@ -842,6 +846,9 @@ drop user "zedjzlcsjhd"@127.0.0.1
 /*!*/;
 use test/*!*/;
 SET TIMESTAMP=t/*!*/;
+drop function if exists f1
+/*!*/;
+SET TIMESTAMP=t/*!*/;
 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
     READS SQL DATA
 begin
@@ -869,6 +876,9 @@ SET TIMESTAMP=t/*!*/;
 drop function f1
 /*!*/;
 SET TIMESTAMP=t/*!*/;
+DROP PROCEDURE IF EXISTS p1
+/*!*/;
+SET TIMESTAMP=t/*!*/;
 DROP TABLE IF EXISTS t1
 /*!*/;
 SET TIMESTAMP=t/*!*/;
@@ -885,6 +895,12 @@ SET TIMESTAMP=t/*!*/;
 DROP PROCEDURE p1
 /*!*/;
 SET TIMESTAMP=t/*!*/;
+DROP PROCEDURE IF EXISTS p1
+/*!*/;
+SET TIMESTAMP=t/*!*/;
+DROP FUNCTION IF EXISTS f1
+/*!*/;
+SET TIMESTAMP=t/*!*/;
 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
 SET @a = 1
 /*!*/;

=== added file 'mysql-test/suite/rpl/t/rpl_drop_if_exists.test'
--- a/mysql-test/suite/rpl/t/rpl_drop_if_exists.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_drop_if_exists.test	2009-02-04 09:41:30 +0000
@@ -0,0 +1,114 @@
+# BUG#13684: 
+#   SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine 
+#   does not exist
+#   
+#   There is an inconsistency with DROP DATABASE IF EXISTS, DROP
+#   TABLE IF EXISTS and DROP VIEW IF EXISTS: those are binlogged even
+#   if the DB or TABLE does not exist, whereas DROP PROCEDURE IF
+#   EXISTS does not. It would be nice or at least consistent if DROP
+#   PROCEDURE/STATEMENT worked the same too.
+#
+# Description: 
+#   DROP PROCEDURE|FUNCTION IF EXISTS does not get binlogged whereas DROP
+#   DATABASE|TABLE|TRIGGER|... IF EXISTS do.
+#
+#   Fixed DROP PROCEDURE|FUNCTION IF EXISTS by adding a call to
+#   write_bin_log in mysql_execute_command. Checked also if all
+#   documented "DROP (...) IF EXISTS" get binlogged. Left out DROP
+#   SERVER IF EXISTS because it seems that it only gets binlogged when
+#   using row event (see BUG#25705).
+#
+#   TODO: add DROP SERVER IF EXISTS to the test case when its
+#   binlogging procedure gets fixed (BUG#25705). Furthermore, when
+#   logging in RBR format the events that get logged are effectively in
+#   RBR format and not in STATEMENT format meaning that one must needs
+#   to be extra careful when writing a test for it, or change the CREATE
+#   SERVER logging to always log as STATEMENT. You can quickly check this
+#   by enabling the flag below $fixed_bug_25705=1 and watch the diff on
+#   the STDOUT. More detail may be found on the generated reject file.
+#   
+#   Test is implemented as follows:
+#
+#       i) test each "drop if exists" (DDL), found in MySQL 5.1 manual, 
+#          on inexistent objects (except for DROP SERVER);
+#      ii) show binlog events;
+#     iii) create an object for each drop if exists statement;
+#      iv) issue "drop if exists" in existent objects.
+#       v) show binlog events;
+#
+# References:
+#  http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
+#
+--source include/have_log_bin.inc
+
+disable_warnings;
+
+# test all "drop if exists" in manual with inexistent objects
+DROP PROCEDURE IF EXISTS db_bug_13684.p;
+DROP FUNCTION IF EXISTS db_bug_13684.f;
+DROP TRIGGER IF EXISTS db_bug_13684.tr;
+DROP VIEW IF EXISTS db_bug_13684.v;
+DROP EVENT IF EXISTS db_bug_13684.e;
+DROP TABLE IF EXISTS db_bug_13684.t;
+DROP DATABASE IF EXISTS db_bug_13684;
+
+let $fixed_bug_25705 = 0;
+
+if($fixed_bug_25705)
+{
+  DROP SERVER IF EXISTS s_bug_13684;
+}
+--source include/show_binlog_events.inc
+
+# test drop with existing values
+
+# create 
+CREATE DATABASE db_bug_13684;
+
+CREATE TABLE db_bug_13684.t (a int);
+
+CREATE EVENT db_bug_13684.e
+  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
+  DO
+    UPDATE db_bug_13684.t SET a = a + 1;
+
+CREATE VIEW db_bug_13684.v 
+  AS SELECT * FROM db_bug_13684.t;
+
+CREATE TRIGGER db_bug_13684.tr BEFORE INSERT ON db_bug_13684.t
+  FOR EACH ROW BEGIN
+  END;
+
+CREATE PROCEDURE db_bug_13684.p (OUT p1 INT)
+  BEGIN
+  END;
+
+CREATE FUNCTION db_bug_13684.f (s CHAR(20))
+  RETURNS CHAR(50) DETERMINISTIC
+  RETURN s;
+
+if($fixed_bug_25705)
+{
+  CREATE SERVER s_bug_25705
+    FOREIGN DATA WRAPPER mysql
+    OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
+}
+
+--source include/show_binlog_events.inc
+
+# drop existing 
+DROP PROCEDURE IF EXISTS db_bug_13684.p;
+DROP FUNCTION IF EXISTS db_bug_13684.f;
+DROP TRIGGER IF EXISTS db_bug_13684.tr;
+DROP VIEW IF EXISTS db_bug_13684.v;
+DROP EVENT IF EXISTS db_bug_13684.e;
+DROP TABLE IF EXISTS db_bug_13684.t;
+DROP DATABASE IF EXISTS db_bug_13684;
+if($fixed_bug_25705)
+{
+  DROP SERVER IF EXISTS s_bug_13684;
+}
+
+--source include/show_binlog_events.inc
+
+enable_warnings;

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-01-30 13:44:49 +0000
+++ b/sql/sql_parse.cc	2009-02-04 09:41:30 +0000
@@ -4427,6 +4427,7 @@ create_sp_error:
       case SP_KEY_NOT_FOUND:
 	if (lex->drop_if_exists)
 	{
+          write_bin_log(thd, TRUE, thd->query, thd->query_length);
 	  push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
 			      ER_SP_DOES_NOT_EXIST, ER(ER_SP_DOES_NOT_EXIST),
 			      SP_COM_STRING(lex), lex->spname->m_name.str);

Thread
bzr commit into mysql-5.1-bugteam branch (luis.soares:2768) Bug#13684Luis Soares4 Feb
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2768)Bug#13684Alfranio Correia5 Feb
    • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2768)Bug#13684Luís Soares6 Feb