#At file:///home/lsoares/Workspace/mysql-server/bugfix/13684/5.0-bt/ based on revid:sergey.glukhov@stripped
2723 Luis Soares 2009-04-09
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
mysql_bin_log.write in mysql_execute_command. Checked also if all
documented "DROP (...) IF EXISTS" get binlogged.
NOTE: This is a 5.0 backport patch as requested by support.
@ mysql-test/r/rpl_drop_if_exists.result
Result file for test case added.
@ mysql-test/r/rpl_sp.result
Updated result file for existing test case that has now extra events in
binary log (the ones from drop if exists procedure/function).
@ mysql-test/t/rpl_drop_if_exists.test
Added test case for asserting validity of proposed patch.
@ sql/sql_parse.cc
Added call mysql_bin_log.write when lex has drop_if_exists enabled for
stored procedures.
added:
mysql-test/r/rpl_drop_if_exists.result
mysql-test/t/rpl_drop_if_exists.test
modified:
mysql-test/r/rpl_sp.result
sql/sql_parse.cc
=== added file 'mysql-test/r/rpl_drop_if_exists.result'
--- a/mysql-test/r/rpl_drop_if_exists.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/rpl_drop_if_exists.result 2009-04-09 10:40:22 +0000
@@ -0,0 +1,80 @@
+RESET MASTER;
+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 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 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 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 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 ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db_bug_13684`.`v` AS select `db_bug_13684`.`t`.`a` AS `a` 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 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 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 ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db_bug_13684`.`v` AS select `db_bug_13684`.`t`.`a` AS `a` 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 TABLE IF EXISTS db_bug_13684.t
+master-bin.000001 # Query # # DROP DATABASE IF EXISTS db_bug_13684
=== modified file 'mysql-test/r/rpl_sp.result'
--- a/mysql-test/r/rpl_sp.result 2008-10-01 09:48:47 +0000
+++ b/mysql-test/r/rpl_sp.result 2009-04-09 10:40:22 +0000
@@ -483,6 +483,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
@@ -498,12 +499,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)
@@ -804,6 +808,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
@@ -831,6 +838,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/*!*/;
@@ -847,6 +857,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/t/rpl_drop_if_exists.test'
--- a/mysql-test/t/rpl_drop_if_exists.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/rpl_drop_if_exists.test 2009-04-09 10:40:22 +0000
@@ -0,0 +1,77 @@
+# 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
+# mysql_bin_log.write in mysql_execute_command. Checked also if all
+# documented "DROP (...) IF EXISTS" get binlogged.
+#
+# Test is implemented as follows:
+#
+# i) test each "drop if exists" (DDL)
+# 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;
+#
+
+--source include/have_log_bin.inc
+RESET MASTER;
+
+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 TABLE IF EXISTS db_bug_13684.t;
+DROP DATABASE IF EXISTS db_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 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;
+
+--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 TABLE IF EXISTS db_bug_13684.t;
+DROP DATABASE IF EXISTS db_bug_13684;
+
+--source include/show_binlog_events.inc
+
+enable_warnings;
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2009-04-08 08:17:26 +0000
+++ b/sql/sql_parse.cc 2009-04-09 10:40:22 +0000
@@ -5067,6 +5067,14 @@ create_sp_error:
case SP_KEY_NOT_FOUND:
if (lex->drop_if_exists)
{
+ if (mysql_bin_log.is_open())
+ {
+ Query_log_event qinfo(thd, thd->query, thd->query_length,
+ /* using_trans */ 0,
+ /* suppress use */ FALSE,
+ THD::NOT_KILLED);
+ mysql_bin_log.write(&qinfo);
+ }
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);
Attachment: [text/bzr-bundle] bzr/luis.soares@sun.com-20090409104022-4a4jwovfageh6mbb.bundle
| Thread |
|---|
| • bzr commit into mysql-5.0-bugteam branch (luis.soares:2723) Bug#13684 | Luis Soares | 9 Apr |
