Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2
3 # BUG#13684:
4 # SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine
5 # does not exist
6 #
7 # There is an inconsistency with DROP DATABASE IF EXISTS, DROP
8 # TABLE IF EXISTS and DROP VIEW IF EXISTS: those are binlogged even
9 # if the DB or TABLE does not exist, whereas DROP PROCEDURE IF
10 # EXISTS does not. It would be nice or at least consistent if DROP
11 # PROCEDURE/STATEMENT worked the same too.
12 #
13 # Description:
14 # DROP PROCEDURE|FUNCTION IF EXISTS does not get binlogged whereas DROP
15 # DATABASE|TABLE|TRIGGER|... IF EXISTS do.
16 #
17 # Fixed DROP PROCEDURE|FUNCTION IF EXISTS by adding a call to
18 # write_bin_log in mysql_execute_command. Checked also if all
19 # documented "DROP (...) IF EXISTS" get binlogged. Left out DROP
20 # SERVER IF EXISTS because it seems that it only gets binlogged when
21 # using row event (see BUG#25705).
22 #
23 # TODO: add DROP SERVER IF EXISTS to the test case when its
24 # binlogging procedure gets fixed (BUG#25705). Furthermore, when
25 # logging in RBR format the events that get logged are effectively in
26 # RBR format and not in STATEMENT format meaning that one must needs
27 # to be extra careful when writing a test for it, or change the CREATE
28 # SERVER logging to always log as STATEMENT. You can quickly check this
29 # by enabling the flag below $fixed_bug_25705=1 and watch the diff on
30 # the STDOUT. More detail may be found on the generated reject file.
31 #
32 # Test is implemented as follows:
33 #
34 # i) test each "drop if exists" (DDL), found in MySQL 5.1 manual,
35 # on inexistent objects (except for DROP SERVER);
36 # ii) show binlog events;
37 # iii) create an object for each drop if exists statement;
38 # iv) issue "drop if exists" in existent objects.
39 # v) show binlog events;
40 #
41 # References:
42 # http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
43 #
44 --source include/have_log_bin.inc
45 RESET MASTER;
46
47 disable_warnings;
48
49 # test all "drop if exists" in manual with inexistent objects
50 DROP PROCEDURE IF EXISTS db_bug_13684.p;
51 DROP FUNCTION IF EXISTS db_bug_13684.f;
52 DROP TRIGGER IF EXISTS db_bug_13684.tr;
53 DROP VIEW IF EXISTS db_bug_13684.v;
54 DROP EVENT IF EXISTS db_bug_13684.e;
55 DROP TABLE IF EXISTS db_bug_13684.t;
56 DROP DATABASE IF EXISTS db_bug_13684;
57
58 let $fixed_bug_25705 = 0;
59
60 if($fixed_bug_25705)
61 {
62 DROP SERVER IF EXISTS s_bug_13684;
63 }
64 --source include/show_binlog_events.inc
65
66 # test drop with existing values
67
68 # create
69 CREATE DATABASE db_bug_13684;
70
71 CREATE TABLE db_bug_13684.t (a int);
72
73 CREATE EVENT db_bug_13684.e
74 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
75 DO
76 UPDATE db_bug_13684.t SET a = a + 1;
77
78 CREATE VIEW db_bug_13684.v
79 AS SELECT * FROM db_bug_13684.t;
80
81 CREATE TRIGGER db_bug_13684.tr BEFORE INSERT ON db_bug_13684.t
82 FOR EACH ROW BEGIN
83 END;
84
85 CREATE PROCEDURE db_bug_13684.p (OUT p1 INT)
86 BEGIN
87 END;
88
89 CREATE FUNCTION db_bug_13684.f (s CHAR(20))
90 RETURNS CHAR(50) DETERMINISTIC
91 RETURN s;
92
93 if($fixed_bug_25705)
94 {
95 CREATE SERVER s_bug_13684
96 FOREIGN DATA WRAPPER mysql
97 OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
98 }
99
100 --source include/show_binlog_events.inc
101
102 # drop existing
103 DROP PROCEDURE IF EXISTS db_bug_13684.p;
104 DROP FUNCTION IF EXISTS db_bug_13684.f;
105 DROP TRIGGER IF EXISTS db_bug_13684.tr;
106 DROP VIEW IF EXISTS db_bug_13684.v;
107 DROP EVENT IF EXISTS db_bug_13684.e;
108 DROP TABLE IF EXISTS db_bug_13684.t;
109 DROP DATABASE IF EXISTS db_bug_13684;
110 if($fixed_bug_25705)
111 {
112 DROP SERVER IF EXISTS s_bug_13684;
113 }
114
115 --source include/show_binlog_events.inc
116
117 enable_warnings;
118
119 # Drop comments in binlog
120 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
121 CREATE TABLE t1(id int);
122 DROP TABLE /* comment */ t1;
123 CREATE TABLE t1(id int);
124 DROP TABLE IF EXISTS /* comment */ t1;
125
126 CREATE TABLE t1(id int);
127 DROP TABLE /**/ t1;
128 CREATE TABLE t1(id int);
129 DROP TABLE IF EXISTS /* */ t1;
130
131 --source include/show_binlog_events.inc
132
133 --echo #
134 --echo # MDEV-22820 Bogus "Unknown table" warnings produced upon attempt to
135 --echo # drop parent table referenced by FK
136 --echo #
137
138 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
139
140 create table t1 (a int, key(a)) engine=InnoDB;
141 create table t2 (b int, foreign key(b) references t1(a)) engine=InnoDB;
142 --error ER_ROW_IS_REFERENCED_2
143 drop table if exists t1;
144 --error ER_ROW_IS_REFERENCED_2
145 drop table if exists t1,t0;
146 show warnings;
147 drop table t2,t1;
148
149 create table t3 (a int) engine=aria;
150
151 # This is not logged
152 --error ER_BAD_TABLE_ERROR
153 drop table t10,t20;
154 # This is logged
155 --error ER_BAD_TABLE_ERROR
156 drop table t10,t20,t3;
157
158 # These are both logged
159 drop table if exists t10,t20;
160 drop table if exists t10,t20,t3;
161
162 --source include/show_binlog_events.inc