2
# SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine
5
# There is an inconsistency with DROP DATABASE IF EXISTS, DROP
6
# TABLE IF EXISTS and DROP VIEW IF EXISTS: those are binlogged even
7
# if the DB or TABLE does not exist, whereas DROP PROCEDURE IF
8
# EXISTS does not. It would be nice or at least consistent if DROP
9
# PROCEDURE/STATEMENT worked the same too.
12
# DROP PROCEDURE|FUNCTION IF EXISTS does not get binlogged whereas DROP
13
# DATABASE|TABLE|TRIGGER|... IF EXISTS do.
15
# Fixed DROP PROCEDURE|FUNCTION IF EXISTS by adding a call to
16
# write_bin_log in mysql_execute_command. Checked also if all
17
# documented "DROP (...) IF EXISTS" get binlogged. Left out DROP
18
# SERVER IF EXISTS because it seems that it only gets binlogged when
19
# using row event (see BUG#25705).
21
# TODO: add DROP SERVER IF EXISTS to the test case when its
22
# binlogging procedure gets fixed (BUG#25705). Furthermore, when
23
# logging in RBR format the events that get logged are effectively in
24
# RBR format and not in STATEMENT format meaning that one must needs
25
# to be extra careful when writing a test for it, or change the CREATE
26
# SERVER logging to always log as STATEMENT. You can quickly check this
27
# by enabling the flag below $fixed_bug_25705=1 and watch the diff on
28
# the STDOUT. More detail may be found on the generated reject file.
30
# Test is implemented as follows:
32
# i) test each "drop if exists" (DDL), found in MySQL 5.1 manual,
33
# on inexistent objects (except for DROP SERVER);
34
# ii) show binlog events;
35
# iii) create an object for each drop if exists statement;
36
# iv) issue "drop if exists" in existent objects.
37
# v) show binlog events;
40
# http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
42
--source include/have_log_bin.inc
47
# test all "drop if exists" in manual with inexistent objects
48
DROP PROCEDURE IF EXISTS db_bug_13684.p;
49
DROP FUNCTION IF EXISTS db_bug_13684.f;
50
DROP TRIGGER IF EXISTS db_bug_13684.tr;
51
DROP VIEW IF EXISTS db_bug_13684.v;
52
DROP EVENT IF EXISTS db_bug_13684.e;
53
DROP TABLE IF EXISTS db_bug_13684.t;
54
DROP DATABASE IF EXISTS db_bug_13684;
56
let $fixed_bug_25705 = 0;
60
DROP SERVER IF EXISTS s_bug_13684;
62
--source include/show_binlog_events.inc
64
# test drop with existing values
67
CREATE DATABASE db_bug_13684;
69
CREATE TABLE db_bug_13684.t (a int);
71
CREATE EVENT db_bug_13684.e
72
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
74
UPDATE db_bug_13684.t SET a = a + 1;
76
CREATE VIEW db_bug_13684.v
77
AS SELECT * FROM db_bug_13684.t;
79
CREATE TRIGGER db_bug_13684.tr BEFORE INSERT ON db_bug_13684.t
83
CREATE PROCEDURE db_bug_13684.p (OUT p1 INT)
87
CREATE FUNCTION db_bug_13684.f (s CHAR(20))
88
RETURNS CHAR(50) DETERMINISTIC
93
CREATE SERVER s_bug_13684
94
FOREIGN DATA WRAPPER mysql
95
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
98
--source include/show_binlog_events.inc
101
DROP PROCEDURE IF EXISTS db_bug_13684.p;
102
DROP FUNCTION IF EXISTS db_bug_13684.f;
103
DROP TRIGGER IF EXISTS db_bug_13684.tr;
104
DROP VIEW IF EXISTS db_bug_13684.v;
105
DROP EVENT IF EXISTS db_bug_13684.e;
106
DROP TABLE IF EXISTS db_bug_13684.t;
107
DROP DATABASE IF EXISTS db_bug_13684;
110
DROP SERVER IF EXISTS s_bug_13684;
113
--source include/show_binlog_events.inc
added
removed
mysql-test/suite/rpl/t/rpl_drop_if_exists.test
