1# ==== Purpose ==== 2# 3# This test check if DDL statements are correctly binlogged when the 4# thread is killed 5# 6# ==== Method ==== 7# 8# Start a DDL query and kill it, check if the error code of the binlog 9# event is correct. 10# 11# DDL statements tested: 12# CREATE/ALTER/RENAME/DROP DATABASE 13# CREATE/ALTER/DROP EVENT 14# CREATE/ALTER/DROP FUNCTION 15# CREATE/ALTER/DROP PROCEDURE 16# CREATE/ALTER/DROP SERVER 17# CREATE/ALTER/RENAME/DROP TABLE 18# CREATE/DROP TRIGGER 19# CREATE/ALTER/DROP VIEW 20# 21# ==== Bugs ===== 22# 23# BUG#37145 24# 25# ==== TODO ==== 26# 27# There are some part of the test are temporarily disabled because of 28# the following bugs, please enable then once they get fixed: 29# - BUG#22473427 30# - Bug#22587377 31 32# Temporarily disabled on Windows due to bug #47638 33--source include/not_windows.inc 34 35source include/have_debug.inc; 36source include/master-slave.inc; 37 38# Use the DBUG_SYNC_POINT to make sure the thread running the DDL is 39# waiting before creating the query log event 40 41let $debug_lock= "debug_lock.before_query_log_event"; 42 43######## INITIALIZATION ######## 44 45disable_warnings; 46DROP DATABASE IF EXISTS d1; 47DROP DATABASE IF EXISTS d2; 48DROP DATABASE IF EXISTS d3; 49DROP DATABASE IF EXISTS d4; 50DROP EVENT IF EXISTS e1; 51DROP EVENT IF EXISTS e2; 52DROP EVENT IF EXISTS e3; 53DROP EVENT IF EXISTS e4; 54DROP FUNCTION IF EXISTS f1; 55DROP FUNCTION IF EXISTS f2; 56DROP FUNCTION IF EXISTS f3; 57DROP FUNCTION IF EXISTS f4; 58DROP SERVER IF EXISTS s1; 59DROP SERVER IF EXISTS s2; 60DROP SERVER IF EXISTS s3; 61DROP SERVER IF EXISTS s4; 62DROP TABLE IF EXISTS t1; 63DROP TABLE IF EXISTS t2; 64DROP TABLE IF EXISTS t3; 65DROP TABLE IF EXISTS t4; 66DROP PROCEDURE IF EXISTS p1; 67DROP PROCEDURE IF EXISTS p2; 68DROP PROCEDURE IF EXISTS p3; 69DROP PROCEDURE IF EXISTS p4; 70DROP TRIGGER IF EXISTS tr1; 71DROP TRIGGER IF EXISTS tr2; 72DROP TRIGGER IF EXISTS tr3; 73DROP TRIGGER IF EXISTS tr4; 74enable_warnings; 75 76CREATE DATABASE d1; 77 78CREATE EVENT e1 79 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY 80 DO INSERT INTO test.t1 VALUES (1); 81 82CREATE FUNCTION f1 () RETURNS INT DETERMINISTIC 83 RETURN 1; 84 85DELIMITER //; 86CREATE PROCEDURE p1 (OUT rows_cnt INT) 87 BEGIN 88 SELECT COUNT(*) INTO rows_cnt FROM t1; 89 END; 90 // 91DELIMITER ;// 92 93CREATE SERVER s1 94FOREIGN DATA WRAPPER mysql 95OPTIONS (USER 'user1', HOST '192.168.1.106', DATABASE 'test'); 96 97CREATE TABLE t1 (a int); 98CREATE TABLE t3 (a int); 99 100DELIMITER //; 101CREATE TRIGGER tr1 BEFORE INSERT ON t1 102 FOR EACH ROW BEGIN 103 DELETE FROM t4 WHERE a=NEW.a; 104 END; 105 // 106DELIMITER ;// 107 108CREATE INDEX i1 ON t1 (a); 109 110CREATE VIEW v1 AS SELECT a FROM t1 WHERE a < 100; 111 112sync_slave_with_master; 113 114connection master1; 115let $connection_name= master1; 116let $connection_id= `SELECT CONNECTION_ID()`; 117 118connection master; 119 120# This will block the execution of a statement at the DBUG_SYNC_POINT 121# with given lock name 122if ($debug_lock) 123{ 124 disable_query_log; 125 disable_result_log; 126 eval SELECT IS_FREE_LOCK($debug_lock); 127 eval SELECT GET_LOCK($debug_lock, 10); 128 eval SELECT IS_FREE_LOCK($debug_lock); 129 enable_query_log; 130 enable_result_log; 131} 132 133######## START TEST ######## 134 135connection master1; 136 137disable_warnings; 138 139######## DATABASE ######## 140 141let $rpl_diff_statement= SELECT schema_name FROM information_schema.schemata 142 WHERE schema_name LIKE \'d%\' ORDER BY schema_name; 143 144send CREATE DATABASE d2; 145source include/kill_query_and_diff_master_slave.inc; 146 147send ALTER DATABASE d1 148 DEFAULT CHARACTER SET = 'utf8'; 149source include/kill_query_and_diff_master_slave.inc; 150 151send DROP DATABASE d1; 152source include/kill_query_and_diff_master_slave.inc; 153 154send DROP DATABASE IF EXISTS d2; 155source include/kill_query_and_diff_master_slave.inc; 156 157######## EVENT ######## 158 159let $rpl_diff_statement= SELECT event_name, event_body, execute_at 160 FROM information_schema.events where event_name like \'e%\' 161 ORDER BY event_name; 162 163send CREATE EVENT e2 164 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY 165 DO INSERT INTO test.t1 VALUES (2); 166source include/kill_query_and_diff_master_slave.inc; 167 168# Temporarily disabled,see Bug#22587377-RPL.RPL_KILLED_DDL 169# FAILS SPORADICALLY ON PB2 IN 5.5 AND 5.6 170#send ALTER EVENT e1 171# ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY; 172#source include/kill_query_and_diff_master_slave.inc; 173 174send DROP EVENT e1; 175source include/kill_query_and_diff_master_slave.inc; 176 177send DROP EVENT IF EXISTS e2; 178source include/kill_query_and_diff_master_slave.inc; 179 180######## FUNCTION ######## 181 182--let $rpl_diff_statement= SHOW FUNCTION STATUS LIKE \'f%\' 183 184send CREATE FUNCTION f2 () RETURNS INT DETERMINISTIC 185 RETURN 1; 186source include/kill_query_and_diff_master_slave.inc; 187 188send ALTER FUNCTION f1 SQL SECURITY INVOKER; 189source include/kill_query_and_diff_master_slave.inc; 190 191# function f1 probably does not exist because the ALTER query was 192# killed 193send DROP FUNCTION f1; 194source include/kill_query_and_diff_master_slave.inc; 195 196# function f2 probably does not exist because the CREATE query was 197# killed 198send DROP FUNCTION IF EXISTS f2; 199source include/kill_query_and_diff_master_slave.inc; 200 201######## PROCEDURE ######## 202 203--let $rpl_diff_statement= SHOW PROCEDURE STATUS LIKE \'p%\' 204 205DELIMITER //; 206send CREATE PROCEDURE p2 (OUT rows_cnt INT) 207 BEGIN 208 SELECT COUNT(*) INTO rows_cnt FROM t2; 209 END; 210 // 211DELIMITER ;// 212source include/kill_query_and_diff_master_slave.inc; 213 214send ALTER PROCEDURE p1 SQL SECURITY INVOKER COMMENT 'return rows_cnt of table t1'; 215source include/kill_query_and_diff_master_slave.inc; 216 217send DROP PROCEDURE p1; 218source include/kill_query_and_diff_master_slave.inc; 219 220send DROP PROCEDURE IF EXISTS p2; 221source include/kill_query_and_diff_master_slave.inc; 222 223######## TABLE ######## 224 225--let $rpl_diff_statement= SHOW TABLES LIKE \'t%\' 226 227send CREATE TABLE t2 (b int); 228source include/kill_query_and_diff_master_slave.inc; 229 230send ALTER TABLE t1 ADD (d int); 231source include/kill_query_and_diff_master_slave.inc; 232 233send RENAME TABLE t3 TO t4; 234source include/kill_query_and_diff_master_slave.inc; 235 236######## INDEX ######## 237 238--let $rpl_diff_statement= SHOW INDEX FROM t1 239 240send CREATE INDEX i2 on t1 (a); 241source include/kill_query_and_diff_master_slave.inc; 242 243send DROP INDEX i1 on t1; 244source include/kill_query_and_diff_master_slave.inc; 245 246 247######## SERVER ######## 248 249# Temporarily disabled, see Bug #22473427 - DROP SERVER FAILS 250# AFTER ALTER SERVER+KILL QUERY 251 252# --let $rpl_diff_statement= SELECT * FROM mysql.servers WHERE Server_name like \'s%\' 253 254# send CREATE SERVER s2 255# FOREIGN DATA WRAPPER mysql 256# OPTIONS (USER 'user2', HOST '192.168.1.108', DATABASE 'test'); 257# source include/kill_query_and_diff_master_slave.inc; 258 259# send ALTER SERVER s1 260# OPTIONS (DATABASE 'test1'); 261# source include/kill_query_and_diff_master_slave.inc; 262 263# send DROP SERVER s1; 264# source include/kill_query_and_diff_master_slave.inc; 265 266# send DROP SERVER IF EXIST s1; 267# source include/kill_query_and_diff_master_slave.inc; 268 269######## TRIGGER ######## 270 271# Make sure table t4 exists 272connection master; 273CREATE TABLE IF NOT EXISTS t4 (a int); 274connection master1; 275 276--let $rpl_diff_statement= SHOW TRIGGERS LIKE \'v%\' 277 278DELIMITER //; 279send CREATE TRIGGER tr2 BEFORE INSERT ON t4 280 FOR EACH ROW BEGIN 281 DELETE FROM t1 WHERE a=NEW.a; 282 END; 283 // 284DELIMITER ;// 285source include/kill_query_and_diff_master_slave.inc; 286 287send DROP TRIGGER tr1; 288source include/kill_query_and_diff_master_slave.inc; 289 290send DROP TRIGGER IF EXISTS tr2; 291source include/kill_query_and_diff_master_slave.inc; 292 293######## VIEW ######## 294 295--let $rpl_diff_statement= SHOW TABLES LIKE \'v%\' 296 297send CREATE VIEW v2 AS SELECT a FROM t1 WHERE a > 100; 298source include/kill_query_and_diff_master_slave.inc; 299 300send DROP VIEW v1; 301source include/kill_query_and_diff_master_slave.inc; 302 303send DROP VIEW IF EXISTS v2; 304source include/kill_query_and_diff_master_slave.inc; 305 306######## DROP TABLE ######## 307 308--let $rpl_diff_statement= SHOW TABLES LIKE \'t%\' 309 310send DROP TABLE t1; 311source include/kill_query_and_diff_master_slave.inc; 312 313send DROP TABLE IF EXISTS t2; 314source include/kill_query_and_diff_master_slave.inc; 315 316######## CLEAN UP ######## 317 318connection master; 319 320# The DROP statements above are killed during the process, so they 321# does not make sure the objects are dropped. 322 323disable_warnings; 324DROP DATABASE IF EXISTS d1; 325DROP DATABASE IF EXISTS d2; 326DROP DATABASE IF EXISTS d3; 327DROP DATABASE IF EXISTS d4; 328DROP EVENT IF EXISTS e1; 329DROP EVENT IF EXISTS e2; 330DROP EVENT IF EXISTS e3; 331DROP EVENT IF EXISTS e4; 332DROP FUNCTION IF EXISTS f1; 333DROP FUNCTION IF EXISTS f2; 334DROP FUNCTION IF EXISTS f3; 335DROP FUNCTION IF EXISTS f4; 336DROP SERVER IF EXISTS s1; 337DROP SERVER IF EXISTS s2; 338DROP SERVER IF EXISTS s3; 339DROP SERVER IF EXISTS s4; 340DROP TABLE IF EXISTS t1; 341DROP TABLE IF EXISTS t2; 342DROP TABLE IF EXISTS t3; 343DROP TABLE IF EXISTS t4; 344DROP PROCEDURE IF EXISTS p1; 345DROP PROCEDURE IF EXISTS p2; 346DROP PROCEDURE IF EXISTS p3; 347DROP PROCEDURE IF EXISTS p4; 348enable_warnings; 349--source include/rpl_end.inc 350