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