#At file:///home/lsoares/Workspace/mysql-server/bugfix/13684/5.1-bt/
2765 Luis Soares 2009-01-21
Bug #13684 SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine does not exist
Fixed DROP PROCEDURE|FUNCTION IF EXISTS by adding a call to write_bin_log in
mysql_execute_command. Added also a test for checking if all documented
"DROP (...) IF EXISTS" get binlogged (except for SERVER - 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:
sql/sql_parse.cc
=== 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-01-21 10:22:01 +0000
@@ -0,0 +1,100 @@
+DROP PROCEDURE IF EXISTS d.p;
+DROP FUNCTION IF EXISTS d.f;
+DROP TRIGGER IF EXISTS d.tr;
+DROP VIEW IF EXISTS d.v;
+DROP EVENT IF EXISTS d.e;
+DROP TABLE IF EXISTS d.t;
+DROP DATABASE IF EXISTS d;
+SHOW BINLOG EVENTS;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 X Format_desc 1 X Server ver: 5.1.31-debug-log, Binlog ver: 4
+master-bin.000001 X Query 1 X use `test`; DROP PROCEDURE IF EXISTS d.p
+master-bin.000001 X Query 1 X use `test`; DROP FUNCTION IF EXISTS d.f
+master-bin.000001 X Query 1 X use `test`; DROP TRIGGER IF EXISTS d.tr
+master-bin.000001 X Query 1 X use `test`; DROP VIEW IF EXISTS d.v
+master-bin.000001 X Query 1 X use `test`; DROP EVENT IF EXISTS d.e
+master-bin.000001 X Query 1 X use `test`; DROP TABLE IF EXISTS d.t
+master-bin.000001 X Query 1 X DROP DATABASE IF EXISTS d
+CREATE DATABASE d;
+CREATE TABLE d.t (a int);
+CREATE EVENT d.e
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
+DO
+UPDATE d.t SET a = a + 1;
+CREATE VIEW d.v
+AS SELECT * FROM d.t;
+CREATE TRIGGER d.tr BEFORE INSERT ON d.t
+FOR EACH ROW BEGIN
+END;
+CREATE PROCEDURE d.p (OUT p1 INT)
+BEGIN
+END;
+CREATE FUNCTION d.f (s CHAR(20))
+RETURNS CHAR(50) DETERMINISTIC
+RETURN s;
+SHOW BINLOG EVENTS;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 X Format_desc 1 X Server ver: 5.1.31-debug-log, Binlog ver: 4
+master-bin.000001 X Query 1 X use `test`; DROP PROCEDURE IF EXISTS d.p
+master-bin.000001 X Query 1 X use `test`; DROP FUNCTION IF EXISTS d.f
+master-bin.000001 X Query 1 X use `test`; DROP TRIGGER IF EXISTS d.tr
+master-bin.000001 X Query 1 X use `test`; DROP VIEW IF EXISTS d.v
+master-bin.000001 X Query 1 X use `test`; DROP EVENT IF EXISTS d.e
+master-bin.000001 X Query 1 X use `test`; DROP TABLE IF EXISTS d.t
+master-bin.000001 X Query 1 X DROP DATABASE IF EXISTS d
+master-bin.000001 X Query 1 X CREATE DATABASE d
+master-bin.000001 X Query 1 X use `test`; CREATE TABLE d.t (a int)
+master-bin.000001 X Query 1 X use `test`; CREATE EVENT d.e
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
+DO
+UPDATE d.t SET a = a + 1
+master-bin.000001 X Query 1 X use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `d`.`v` AS SELECT * FROM d.t
+master-bin.000001 X Query 1 X use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER d.tr BEFORE INSERT ON d.t
+FOR EACH ROW BEGIN
+END
+master-bin.000001 X Query 1 X use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `d`.`p`(OUT p1 INT)
+BEGIN
+END
+master-bin.000001 X Query 1 X use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `d`.`f`(s CHAR(20)) RETURNS char(50) CHARSET latin1
+ DETERMINISTIC
+RETURN s
+DROP PROCEDURE IF EXISTS d.p;
+DROP FUNCTION IF EXISTS d.f;
+DROP TRIGGER IF EXISTS d.tr;
+DROP VIEW IF EXISTS d.v;
+DROP EVENT IF EXISTS d.e;
+DROP TABLE IF EXISTS d.t;
+DROP DATABASE IF EXISTS d;
+SHOW BINLOG EVENTS;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 X Format_desc 1 X Server ver: 5.1.31-debug-log, Binlog ver: 4
+master-bin.000001 X Query 1 X use `test`; DROP PROCEDURE IF EXISTS d.p
+master-bin.000001 X Query 1 X use `test`; DROP FUNCTION IF EXISTS d.f
+master-bin.000001 X Query 1 X use `test`; DROP TRIGGER IF EXISTS d.tr
+master-bin.000001 X Query 1 X use `test`; DROP VIEW IF EXISTS d.v
+master-bin.000001 X Query 1 X use `test`; DROP EVENT IF EXISTS d.e
+master-bin.000001 X Query 1 X use `test`; DROP TABLE IF EXISTS d.t
+master-bin.000001 X Query 1 X DROP DATABASE IF EXISTS d
+master-bin.000001 X Query 1 X CREATE DATABASE d
+master-bin.000001 X Query 1 X use `test`; CREATE TABLE d.t (a int)
+master-bin.000001 X Query 1 X use `test`; CREATE EVENT d.e
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
+DO
+UPDATE d.t SET a = a + 1
+master-bin.000001 X Query 1 X use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `d`.`v` AS SELECT * FROM d.t
+master-bin.000001 X Query 1 X use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER d.tr BEFORE INSERT ON d.t
+FOR EACH ROW BEGIN
+END
+master-bin.000001 X Query 1 X use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `d`.`p`(OUT p1 INT)
+BEGIN
+END
+master-bin.000001 X Query 1 X use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `d`.`f`(s CHAR(20)) RETURNS char(50) CHARSET latin1
+ DETERMINISTIC
+RETURN s
+master-bin.000001 X Query 1 X use `test`; DROP PROCEDURE IF EXISTS d.p
+master-bin.000001 X Query 1 X use `test`; DROP FUNCTION IF EXISTS d.f
+master-bin.000001 X Query 1 X use `test`; DROP TRIGGER IF EXISTS d.tr
+master-bin.000001 X Query 1 X use `test`; DROP VIEW IF EXISTS d.v
+master-bin.000001 X Query 1 X use `test`; DROP EVENT IF EXISTS d.e
+master-bin.000001 X Query 1 X use `test`; DROP TABLE IF EXISTS d.t
+master-bin.000001 X Query 1 X DROP DATABASE IF EXISTS d
=== 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-01-21 10:22:01 +0000
@@ -0,0 +1,82 @@
+# BUG#13684:
+# SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine
+# does not exist
+#
+# Description:
+# DROP PROCEDURE|FUNCTION IF EXISTS does not get binlogged as DROP
+# DATABASE|TABLE|TRIGGER|...
+#
+# Left out DROP SERVER IF EXISTS because it seems that it only gets
+# binlogged when using row event (see BUG#25705).
+#
+# 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 d.p;
+DROP FUNCTION IF EXISTS d.f;
+DROP TRIGGER IF EXISTS d.tr;
+DROP VIEW IF EXISTS d.v;
+DROP EVENT IF EXISTS d.e;
+DROP TABLE IF EXISTS d.t;
+DROP DATABASE IF EXISTS d;
+
+replace_column 2 X 5 X;
+SHOW BINLOG EVENTS;
+
+# test drop with existing values
+
+# create
+CREATE DATABASE d;
+
+CREATE TABLE d.t (a int);
+
+CREATE EVENT d.e
+ ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
+ DO
+ UPDATE d.t SET a = a + 1;
+
+CREATE VIEW d.v
+ AS SELECT * FROM d.t;
+
+CREATE TRIGGER d.tr BEFORE INSERT ON d.t
+ FOR EACH ROW BEGIN
+ END;
+
+CREATE PROCEDURE d.p (OUT p1 INT)
+ BEGIN
+ END;
+
+CREATE FUNCTION d.f (s CHAR(20))
+ RETURNS CHAR(50) DETERMINISTIC
+ RETURN s;
+
+replace_column 2 X 5 X;
+SHOW BINLOG EVENTS;
+
+# drop existing
+DROP PROCEDURE IF EXISTS d.p;
+DROP FUNCTION IF EXISTS d.f;
+DROP TRIGGER IF EXISTS d.tr;
+DROP VIEW IF EXISTS d.v;
+DROP EVENT IF EXISTS d.e;
+DROP TABLE IF EXISTS d.t;
+DROP DATABASE IF EXISTS d;
+
+replace_column 2 X 5 X;
+SHOW BINLOG EVENTS;
+
+enable_warnings;
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2008-12-17 13:23:21 +0000
+++ b/sql/sql_parse.cc 2009-01-21 10:22:01 +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:2765) Bug#13684 | Luis Soares | 21 Jan |
