Debian Codegraph @

mysql-test/suite/binlog/r/binlog_database.result

raw
1 set binlog_format=statement;
2 reset master;
3 create database testing_1;
4 use testing_1;
5 create table t1 (a int);
6 create function sf1 (a int) returns int return a+1;
7 create trigger tr1 before insert on t1 for each row insert into t2 values (2*new.a);
8 create procedure sp1 (a int) insert into t1 values(a);
9 drop database testing_1;
10 include/show_binlog_events.inc
11 Log_name Pos Event_type Server_id End_log_pos Info
12 master-bin.000001 # Gtid # # GTID #-#-#
13 master-bin.000001 # Query # # create database testing_1
14 master-bin.000001 # Gtid # # GTID #-#-#
15 master-bin.000001 # Query # # use `testing_1`; create table t1 (a int)
16 master-bin.000001 # Gtid # # GTID #-#-#
17 master-bin.000001 # Query # # use `testing_1`; CREATE DEFINER=`root`@`localhost` FUNCTION `sf1`(a int) RETURNS int(11)
18 return a+1
19 master-bin.000001 # Gtid # # GTID #-#-#
20 master-bin.000001 # Query # # use `testing_1`; CREATE DEFINER=`root`@`localhost` trigger tr1 before insert on t1 for each row insert into t2 values (2*new.a)
21 master-bin.000001 # Gtid # # GTID #-#-#
22 master-bin.000001 # Query # # use `testing_1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`(a int)
23 insert into t1 values(a)
24 master-bin.000001 # Gtid # # GTID #-#-#
25 master-bin.000001 # Query # # drop database testing_1
26 use test;
27 reset master;
28 create temporary table tt1 (a int);
29 create table t1 (a int);
30 insert into t1 values (1);
31 drop database if exists mysqltest1;
32 insert into t1 values (1);
33 drop table tt1, t1;
34 include/show_binlog_events.inc
35 Log_name Pos Event_type Server_id End_log_pos Info
36 master-bin.000001 # Gtid # # GTID #-#-#
37 master-bin.000001 # Query # # use `test`; create temporary table tt1 (a int)
38 master-bin.000001 # Gtid # # GTID #-#-#
39 master-bin.000001 # Query # # use `test`; create table t1 (a int)
40 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
41 master-bin.000001 # Query # # use `test`; insert into t1 values (1)
42 master-bin.000001 # Query # # COMMIT
43 master-bin.000001 # Gtid # # GTID #-#-#
44 master-bin.000001 # Query # # drop database if exists mysqltest1
45 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
46 master-bin.000001 # Query # # use `test`; insert into t1 values (1)
47 master-bin.000001 # Query # # COMMIT
48 master-bin.000001 # Gtid # # GTID #-#-#
49 master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `tt1` /* generated by server */
50 master-bin.000001 # Gtid # # GTID #-#-#
51 master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
52 FLUSH STATUS;
53
54 # 'DROP TABLE IF EXISTS <deleted tables>' is binlogged
55 # when 'DROP DATABASE' fails and at least one table is deleted
56 # from the database.
57 RESET MASTER;
58 CREATE DATABASE testing_1;
59 USE testing_1;
60 CREATE TABLE t1(c1 INT);
61 CREATE TABLE t2(c1 INT);
62 # Create a file in the database directory
63 SELECT 'hello' INTO OUTFILE 'fake_file.FAKE_FILE';
64
65 # 'DROP DATABASE' will fail if there is any other file in the the
66 # database directory
67 DROP DATABASE testing_1;
68 ERROR HY000: Error dropping database (can't rmdir './testing_1', errno: 39 "Directory not empty")
69
70 # Remove the fake file.
71 # Now we can drop the database.
72 DROP DATABASE testing_1;
73 #
74 # Bug#11765416 58381: FAILED DROP DATABASE CAN BREAK STATEMENT
75 # BASED REPLICATION
76 #
77 USE test;
78 DROP DATABASE IF EXISTS db1;
79 DROP TABLE IF EXISTS t3;
80 CREATE DATABASE db1;
81 CREATE TABLE db1.t1 (a INT);
82 CREATE TABLE db1.t2 (b INT, KEY(b)) engine=innodb;
83 CREATE TABLE t3 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b))
84 engine=innodb;
85 RESET MASTER;
86 DROP DATABASE db1;
87 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
88 SHOW TABLES FROM db1;
89 Tables_in_db1
90 t2
91 include/show_binlog_events.inc
92 Log_name Pos Event_type Server_id End_log_pos Info
93 master-bin.000001 # Gtid # # GTID #-#-#
94 master-bin.000001 # Query # # use `db1`; DROP TABLE IF EXISTS `t1`
95 DROP TABLE t3;
96 DROP DATABASE db1;
97 set binlog_format=mixed;
98 reset master;
99 create database testing_1;
100 use testing_1;
101 create table t1 (a int);
102 create function sf1 (a int) returns int return a+1;
103 create trigger tr1 before insert on t1 for each row insert into t2 values (2*new.a);
104 create procedure sp1 (a int) insert into t1 values(a);
105 drop database testing_1;
106 include/show_binlog_events.inc
107 Log_name Pos Event_type Server_id End_log_pos Info
108 master-bin.000001 # Gtid # # GTID #-#-#
109 master-bin.000001 # Query # # create database testing_1
110 master-bin.000001 # Gtid # # GTID #-#-#
111 master-bin.000001 # Query # # use `testing_1`; create table t1 (a int)
112 master-bin.000001 # Gtid # # GTID #-#-#
113 master-bin.000001 # Query # # use `testing_1`; CREATE DEFINER=`root`@`localhost` FUNCTION `sf1`(a int) RETURNS int(11)
114 return a+1
115 master-bin.000001 # Gtid # # GTID #-#-#
116 master-bin.000001 # Query # # use `testing_1`; CREATE DEFINER=`root`@`localhost` trigger tr1 before insert on t1 for each row insert into t2 values (2*new.a)
117 master-bin.000001 # Gtid # # GTID #-#-#
118 master-bin.000001 # Query # # use `testing_1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`(a int)
119 insert into t1 values(a)
120 master-bin.000001 # Gtid # # GTID #-#-#
121 master-bin.000001 # Query # # drop database testing_1
122 use test;
123 reset master;
124 create temporary table tt1 (a int);
125 create table t1 (a int);
126 insert into t1 values (1);
127 drop database if exists mysqltest1;
128 insert into t1 values (1);
129 drop table tt1, t1;
130 include/show_binlog_events.inc
131 Log_name Pos Event_type Server_id End_log_pos Info
132 master-bin.000001 # Gtid # # GTID #-#-#
133 master-bin.000001 # Query # # use `test`; create temporary table tt1 (a int)
134 master-bin.000001 # Gtid # # GTID #-#-#
135 master-bin.000001 # Query # # use `test`; create table t1 (a int)
136 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
137 master-bin.000001 # Query # # use `test`; insert into t1 values (1)
138 master-bin.000001 # Query # # COMMIT
139 master-bin.000001 # Gtid # # GTID #-#-#
140 master-bin.000001 # Query # # drop database if exists mysqltest1
141 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
142 master-bin.000001 # Query # # use `test`; insert into t1 values (1)
143 master-bin.000001 # Query # # COMMIT
144 master-bin.000001 # Gtid # # GTID #-#-#
145 master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `tt1` /* generated by server */
146 master-bin.000001 # Gtid # # GTID #-#-#
147 master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
148 FLUSH STATUS;
149
150 # 'DROP TABLE IF EXISTS <deleted tables>' is binlogged
151 # when 'DROP DATABASE' fails and at least one table is deleted
152 # from the database.
153 RESET MASTER;
154 CREATE DATABASE testing_1;
155 USE testing_1;
156 CREATE TABLE t1(c1 INT);
157 CREATE TABLE t2(c1 INT);
158 # Create a file in the database directory
159 SELECT 'hello' INTO OUTFILE 'fake_file.FAKE_FILE';
160
161 # 'DROP DATABASE' will fail if there is any other file in the the
162 # database directory
163 DROP DATABASE testing_1;
164 ERROR HY000: Error dropping database (can't rmdir './testing_1', errno: 39 "Directory not empty")
165
166 # Remove the fake file.
167 # Now we can drop the database.
168 DROP DATABASE testing_1;
169 #
170 # Bug#11765416 58381: FAILED DROP DATABASE CAN BREAK STATEMENT
171 # BASED REPLICATION
172 #
173 USE test;
174 DROP DATABASE IF EXISTS db1;
175 DROP TABLE IF EXISTS t3;
176 CREATE DATABASE db1;
177 CREATE TABLE db1.t1 (a INT);
178 CREATE TABLE db1.t2 (b INT, KEY(b)) engine=innodb;
179 CREATE TABLE t3 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b))
180 engine=innodb;
181 RESET MASTER;
182 DROP DATABASE db1;
183 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
184 SHOW TABLES FROM db1;
185 Tables_in_db1
186 t2
187 include/show_binlog_events.inc
188 Log_name Pos Event_type Server_id End_log_pos Info
189 master-bin.000001 # Gtid # # GTID #-#-#
190 master-bin.000001 # Query # # use `db1`; DROP TABLE IF EXISTS `t1`
191 DROP TABLE t3;
192 DROP DATABASE db1;
193 set binlog_format=row;
194 reset master;
195 create database testing_1;
196 use testing_1;
197 create table t1 (a int);
198 create function sf1 (a int) returns int return a+1;
199 create trigger tr1 before insert on t1 for each row insert into t2 values (2*new.a);
200 create procedure sp1 (a int) insert into t1 values(a);
201 drop database testing_1;
202 include/show_binlog_events.inc
203 Log_name Pos Event_type Server_id End_log_pos Info
204 master-bin.000001 # Gtid # # GTID #-#-#
205 master-bin.000001 # Query # # create database testing_1
206 master-bin.000001 # Gtid # # GTID #-#-#
207 master-bin.000001 # Query # # use `testing_1`; create table t1 (a int)
208 master-bin.000001 # Gtid # # GTID #-#-#
209 master-bin.000001 # Query # # use `testing_1`; CREATE DEFINER=`root`@`localhost` FUNCTION `sf1`(a int) RETURNS int(11)
210 return a+1
211 master-bin.000001 # Gtid # # GTID #-#-#
212 master-bin.000001 # Query # # use `testing_1`; CREATE DEFINER=`root`@`localhost` trigger tr1 before insert on t1 for each row insert into t2 values (2*new.a)
213 master-bin.000001 # Gtid # # GTID #-#-#
214 master-bin.000001 # Query # # use `testing_1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`(a int)
215 insert into t1 values(a)
216 master-bin.000001 # Gtid # # GTID #-#-#
217 master-bin.000001 # Query # # drop database testing_1
218 use test;
219 reset master;
220 create temporary table tt1 (a int);
221 create table t1 (a int);
222 insert into t1 values (1);
223 drop database if exists mysqltest1;
224 insert into t1 values (1);
225 drop table tt1, t1;
226 include/show_binlog_events.inc
227 Log_name Pos Event_type Server_id End_log_pos Info
228 master-bin.000001 # Gtid # # GTID #-#-#
229 master-bin.000001 # Query # # use `test`; create table t1 (a int)
230 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
231 master-bin.000001 # Annotate_rows # # insert into t1 values (1)
232 master-bin.000001 # Table_map # # table_id: # (test.t1)
233 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
234 master-bin.000001 # Query # # COMMIT
235 master-bin.000001 # Gtid # # GTID #-#-#
236 master-bin.000001 # Query # # drop database if exists mysqltest1
237 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
238 master-bin.000001 # Annotate_rows # # insert into t1 values (1)
239 master-bin.000001 # Table_map # # table_id: # (test.t1)
240 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
241 master-bin.000001 # Query # # COMMIT
242 master-bin.000001 # Gtid # # GTID #-#-#
243 master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
244 FLUSH STATUS;
245
246 # 'DROP TABLE IF EXISTS <deleted tables>' is binlogged
247 # when 'DROP DATABASE' fails and at least one table is deleted
248 # from the database.
249 RESET MASTER;
250 CREATE DATABASE testing_1;
251 USE testing_1;
252 CREATE TABLE t1(c1 INT);
253 CREATE TABLE t2(c1 INT);
254 # Create a file in the database directory
255 SELECT 'hello' INTO OUTFILE 'fake_file.FAKE_FILE';
256
257 # 'DROP DATABASE' will fail if there is any other file in the the
258 # database directory
259 DROP DATABASE testing_1;
260 ERROR HY000: Error dropping database (can't rmdir './testing_1', errno: 39 "Directory not empty")
261
262 # Remove the fake file.
263 # Now we can drop the database.
264 DROP DATABASE testing_1;
265 #
266 # Bug#11765416 58381: FAILED DROP DATABASE CAN BREAK STATEMENT
267 # BASED REPLICATION
268 #
269 USE test;
270 DROP DATABASE IF EXISTS db1;
271 DROP TABLE IF EXISTS t3;
272 CREATE DATABASE db1;
273 CREATE TABLE db1.t1 (a INT);
274 CREATE TABLE db1.t2 (b INT, KEY(b)) engine=innodb;
275 CREATE TABLE t3 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b))
276 engine=innodb;
277 RESET MASTER;
278 DROP DATABASE db1;
279 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
280 SHOW TABLES FROM db1;
281 Tables_in_db1
282 t2
283 include/show_binlog_events.inc
284 Log_name Pos Event_type Server_id End_log_pos Info
285 master-bin.000001 # Gtid # # GTID #-#-#
286 master-bin.000001 # Query # # use `db1`; DROP TABLE IF EXISTS `t1`
287 DROP TABLE t3;
288 DROP DATABASE db1;
289 show databases;
290 Database
291 information_schema
292 mtr
293 mysql
294 performance_schema
295 sys
296 test