Debian Codegraph @

mysql-test/suite/binlog/t/binlog_drop_if_exists.test

raw
1 # BUG#13684:
2 # SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine
3 # does not exist
4 #
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.
10 #
11 # Description:
12 # DROP PROCEDURE|FUNCTION IF EXISTS does not get binlogged whereas DROP
13 # DATABASE|TABLE|TRIGGER|... IF EXISTS do.
14 #
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).
20 #
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.
29 #
30 # Test is implemented as follows:
31 #
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;
38 #
39 # References:
40 # http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
41 #
42 --source include/have_log_bin.inc
43 RESET MASTER;
44
45 disable_warnings;
46
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;
55
56 let $fixed_bug_25705 = 0;
57
58 if($fixed_bug_25705)
59 {
60 DROP SERVER IF EXISTS s_bug_13684;
61 }
62 --source include/show_binlog_events.inc
63
64 # test drop with existing values
65
66 # create
67 CREATE DATABASE db_bug_13684;
68
69 CREATE TABLE db_bug_13684.t (a int);
70
71 CREATE EVENT db_bug_13684.e
72 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
73 DO
74 UPDATE db_bug_13684.t SET a = a + 1;
75
76 CREATE VIEW db_bug_13684.v
77 AS SELECT * FROM db_bug_13684.t;
78
79 CREATE TRIGGER db_bug_13684.tr BEFORE INSERT ON db_bug_13684.t
80 FOR EACH ROW BEGIN
81 END;
82
83 CREATE PROCEDURE db_bug_13684.p (OUT p1 INT)
84 BEGIN
85 END;
86
87 CREATE FUNCTION db_bug_13684.f (s CHAR(20))
88 RETURNS CHAR(50) DETERMINISTIC
89 RETURN s;
90
91 if($fixed_bug_25705)
92 {
93 CREATE SERVER s_bug_13684
94 FOREIGN DATA WRAPPER mysql
95 OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
96 }
97
98 --source include/show_binlog_events.inc
99
100 # drop existing
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;
108 if($fixed_bug_25705)
109 {
110 DROP SERVER IF EXISTS s_bug_13684;
111 }
112
113 --source include/show_binlog_events.inc
114
115 enable_warnings;