path: root/mysql-test/suite/rpl/t/rpl_killed_ddl.test

# ==== Purpose ====
#
# This test check if DDL statements are correctly binlogged when the
# thread is killed
#
# ==== Method ====
#
# Start a DDL query and kill it, check if the error code of the binlog
# event is correct.
#
# DDL statements tested:
# CREATE/ALTER/RENAME/DROP DATABASE
# CREATE/ALTER/DROP        EVENT
# CREATE/ALTER/DROP        FUNCTION
# CREATE/ALTER/DROP 	   PROCEDURE
# CREATE/ALTER/DROP        SERVER
# CREATE/ALTER/RENAME/DROP TABLE
# CREATE/DROP       	   TRIGGER
# CREATE/ALTER/DROP 	   VIEW
#
# ==== Bugs =====
#
# BUG#37145
#
# ==== TODO ====
#
# There are some part of the test are temporarily disabled because of
# the following bugs, please enable then once they get fixed:
#  - BUG#22473427
#  - Bug#22587377

# Temporarily disabled on Windows due to bug #47638 
--source include/not_windows.inc

source include/have_debug.inc;
source include/master-slave.inc;

# Use the DBUG_SYNC_POINT to make sure the thread running the DDL is
# waiting before creating the query log event

let $debug_lock= "debug_lock.before_query_log_event";

######## INITIALIZATION ########

disable_warnings;
DROP DATABASE IF EXISTS d1;
DROP DATABASE IF EXISTS d2;
DROP DATABASE IF EXISTS d3;
DROP DATABASE IF EXISTS d4;
DROP EVENT IF EXISTS e1;
DROP EVENT IF EXISTS e2;
DROP EVENT IF EXISTS e3;
DROP EVENT IF EXISTS e4;
DROP FUNCTION IF EXISTS f1;
DROP FUNCTION IF EXISTS f2;
DROP FUNCTION IF EXISTS f3;
DROP FUNCTION IF EXISTS f4;
DROP SERVER IF EXISTS s1;
DROP SERVER IF EXISTS s2;
DROP SERVER IF EXISTS s3;
DROP SERVER IF EXISTS s4;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p3;
DROP PROCEDURE IF EXISTS p4;
DROP TRIGGER IF EXISTS tr1;
DROP TRIGGER IF EXISTS tr2;
DROP TRIGGER IF EXISTS tr3;
DROP TRIGGER IF EXISTS tr4;
enable_warnings;

CREATE DATABASE d1;

CREATE EVENT e1
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
  DO INSERT INTO test.t1 VALUES (1);

CREATE FUNCTION f1 () RETURNS INT DETERMINISTIC
    RETURN 1;

DELIMITER //;
CREATE PROCEDURE p1 (OUT rows_cnt INT)
    BEGIN
        SELECT COUNT(*) INTO rows_cnt FROM t1;
    END;
    //
DELIMITER ;//

CREATE SERVER s1
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'user1', HOST '192.168.1.106', DATABASE 'test');

CREATE TABLE t1 (a int);
CREATE TABLE t3 (a int);

DELIMITER //;
CREATE TRIGGER tr1 BEFORE INSERT ON t1
    FOR EACH ROW BEGIN
        DELETE FROM t4 WHERE a=NEW.a;
    END;
    //
DELIMITER ;//

CREATE INDEX i1 ON t1 (a);

CREATE VIEW v1 AS SELECT a FROM t1 WHERE a < 100;

sync_slave_with_master;

connection master1;
let $connection_name= master1;
let $connection_id= `SELECT CONNECTION_ID()`;

connection master;

# This will block the execution of a statement at the DBUG_SYNC_POINT
# with given lock name
if ($debug_lock)
{
  disable_query_log;
  disable_result_log;
  eval SELECT IS_FREE_LOCK($debug_lock);
  eval SELECT GET_LOCK($debug_lock, 10);
  eval SELECT IS_FREE_LOCK($debug_lock);
  enable_query_log;
  enable_result_log;
}

######## START TEST ########

connection master1;

disable_warnings;

######## DATABASE ########

let $rpl_diff_statement= SELECT schema_name FROM information_schema.schemata
                         WHERE schema_name LIKE \'d%\' ORDER BY schema_name;

send CREATE DATABASE d2;
source include/kill_query_and_diff_master_slave.inc;

send ALTER DATABASE d1
       DEFAULT CHARACTER SET = 'utf8';
source include/kill_query_and_diff_master_slave.inc;

send DROP DATABASE d1;
source include/kill_query_and_diff_master_slave.inc;

send DROP DATABASE IF EXISTS d2;
source include/kill_query_and_diff_master_slave.inc;

######## EVENT ########

let $rpl_diff_statement= SELECT event_name, event_body, execute_at
                         FROM information_schema.events where event_name like \'e%\'
                         ORDER BY event_name;

send CREATE EVENT e2
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
  DO INSERT INTO test.t1 VALUES (2);
source include/kill_query_and_diff_master_slave.inc;

# Temporarily disabled,see Bug#22587377-RPL.RPL_KILLED_DDL
# FAILS SPORADICALLY ON PB2 IN 5.5 AND 5.6
#send ALTER EVENT e1
#  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY;
#source include/kill_query_and_diff_master_slave.inc;

send DROP EVENT e1;
source include/kill_query_and_diff_master_slave.inc;

send DROP EVENT IF EXISTS e2;
source include/kill_query_and_diff_master_slave.inc;

######## FUNCTION ########

--let $rpl_diff_statement= SHOW FUNCTION STATUS LIKE \'f%\'

send CREATE FUNCTION f2 () RETURNS INT DETERMINISTIC
     RETURN 1;
source include/kill_query_and_diff_master_slave.inc;

send ALTER FUNCTION f1 SQL SECURITY INVOKER;
source include/kill_query_and_diff_master_slave.inc;

# function f1 probably does not exist because the ALTER query was
# killed
send DROP FUNCTION f1;
source include/kill_query_and_diff_master_slave.inc;

# function f2 probably does not exist because the CREATE query was
# killed
send DROP FUNCTION IF EXISTS f2;
source include/kill_query_and_diff_master_slave.inc;

######## PROCEDURE ########

--let $rpl_diff_statement= SHOW PROCEDURE STATUS LIKE \'p%\'

DELIMITER //;
send CREATE PROCEDURE p2 (OUT rows_cnt INT)
     BEGIN
       SELECT COUNT(*) INTO rows_cnt FROM t2;
     END;
     //
DELIMITER ;//
source include/kill_query_and_diff_master_slave.inc;

send ALTER PROCEDURE p1 SQL SECURITY INVOKER COMMENT 'return rows_cnt of table t1';
source include/kill_query_and_diff_master_slave.inc;

send DROP PROCEDURE p1;
source include/kill_query_and_diff_master_slave.inc;

send DROP PROCEDURE IF EXISTS p2;
source include/kill_query_and_diff_master_slave.inc;

######## TABLE ########

--let $rpl_diff_statement= SHOW TABLES LIKE \'t%\'

send CREATE TABLE t2 (b int);
source include/kill_query_and_diff_master_slave.inc;

send ALTER TABLE t1 ADD (d int);
source include/kill_query_and_diff_master_slave.inc;

send RENAME TABLE t3 TO t4;
source include/kill_query_and_diff_master_slave.inc;

######## INDEX ########

--let $rpl_diff_statement= SHOW INDEX FROM t1

send CREATE INDEX i2 on t1 (a);
source include/kill_query_and_diff_master_slave.inc;

send DROP INDEX i1 on t1;
source include/kill_query_and_diff_master_slave.inc;


######## SERVER ########

# Temporarily disabled, see Bug #22473427 - DROP SERVER FAILS
# AFTER ALTER SERVER+KILL QUERY

# --let $rpl_diff_statement= SELECT * FROM mysql.servers WHERE Server_name like \'s%\'

# send CREATE SERVER s2
#     FOREIGN DATA WRAPPER mysql
#     OPTIONS (USER 'user2', HOST '192.168.1.108', DATABASE 'test');
# source include/kill_query_and_diff_master_slave.inc;
    
# send ALTER SERVER s1
#     OPTIONS (DATABASE 'test1');
# source include/kill_query_and_diff_master_slave.inc;

# send DROP SERVER s1;
# source include/kill_query_and_diff_master_slave.inc;

# send DROP SERVER IF EXIST s1;
# source include/kill_query_and_diff_master_slave.inc;

######## TRIGGER ########

# Make sure table t4 exists
connection master;
CREATE TABLE IF NOT EXISTS t4 (a int);
connection master1;

--let $rpl_diff_statement= SHOW TRIGGERS LIKE \'v%\'

DELIMITER //;
send CREATE TRIGGER tr2 BEFORE INSERT ON t4
       FOR EACH ROW BEGIN
         DELETE FROM t1 WHERE a=NEW.a;
       END;
       //
DELIMITER ;//
source include/kill_query_and_diff_master_slave.inc;

send DROP TRIGGER tr1;
source include/kill_query_and_diff_master_slave.inc;

send DROP TRIGGER IF EXISTS tr2;
source include/kill_query_and_diff_master_slave.inc;

######## VIEW ########

--let $rpl_diff_statement= SHOW TABLES LIKE \'v%\'

send CREATE VIEW v2 AS SELECT a FROM t1 WHERE a > 100;
source include/kill_query_and_diff_master_slave.inc;

send DROP VIEW v1;
source include/kill_query_and_diff_master_slave.inc;

send DROP VIEW IF EXISTS v2;
source include/kill_query_and_diff_master_slave.inc;

######## DROP TABLE ########

--let $rpl_diff_statement= SHOW TABLES LIKE \'t%\'

send DROP TABLE t1;
source include/kill_query_and_diff_master_slave.inc;

send DROP TABLE IF EXISTS t2;
source include/kill_query_and_diff_master_slave.inc;

######## CLEAN UP ########

connection master;

# The DROP statements above are killed during the process, so they
# does not make sure the objects are dropped.

disable_warnings;
DROP DATABASE IF EXISTS d1;
DROP DATABASE IF EXISTS d2;
DROP DATABASE IF EXISTS d3;
DROP DATABASE IF EXISTS d4;
DROP EVENT IF EXISTS e1;
DROP EVENT IF EXISTS e2;
DROP EVENT IF EXISTS e3;
DROP EVENT IF EXISTS e4;
DROP FUNCTION IF EXISTS f1;
DROP FUNCTION IF EXISTS f2;
DROP FUNCTION IF EXISTS f3;
DROP FUNCTION IF EXISTS f4;
DROP SERVER IF EXISTS s1;
DROP SERVER IF EXISTS s2;
DROP SERVER IF EXISTS s3;
DROP SERVER IF EXISTS s4;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p3;
DROP PROCEDURE IF EXISTS p4;
enable_warnings;
--source include/rpl_end.inc