summaryrefslogtreecommitdiff
path: root/mysql-test/suite/rpl
diff options
context:
space:
mode:
authorHe Zhenxing <zhenxing.he@sun.com>2009-04-09 07:42:51 +0800
committerHe Zhenxing <zhenxing.he@sun.com>2009-04-09 07:42:51 +0800
commit85cff45ef791c411abd0782fcae77e2da7f532ec (patch)
tree22e49a89755096df5a854d1016d5e19cc163820a /mysql-test/suite/rpl
parent440858b8167162e29d5f1619448768c93e870926 (diff)
parent51a91166387760546d563ebf72bfa037fcff24a8 (diff)
downloadmariadb-git-85cff45ef791c411abd0782fcae77e2da7f532ec.tar.gz
Manually merge BUG#37145 to 5.1-bugteam
Diffstat (limited to 'mysql-test/suite/rpl')
-rw-r--r--mysql-test/suite/rpl/r/rpl_killed_ddl.result169
-rw-r--r--mysql-test/suite/rpl/r/rpl_name_const.result28
-rw-r--r--mysql-test/suite/rpl/t/rpl_killed_ddl-master.opt1
-rw-r--r--mysql-test/suite/rpl/t/rpl_killed_ddl.test352
-rw-r--r--mysql-test/suite/rpl/t/rpl_name_const.test47
5 files changed, 597 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/r/rpl_killed_ddl.result b/mysql-test/suite/rpl/r/rpl_killed_ddl.result
new file mode 100644
index 00000000000..a15b3c30766
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_killed_ddl.result
@@ -0,0 +1,169 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+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;
+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;
+CREATE PROCEDURE p1 (OUT rows INT)
+BEGIN
+SELECT COUNT(*) INTO rows FROM t1;
+END;
+//
+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);
+CREATE TRIGGER tr1 BEFORE INSERT ON t1
+FOR EACH ROW BEGIN
+DELETE FROM t4 WHERE a=NEW.a;
+END;
+//
+CREATE INDEX i1 ON t1 (a);
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE a < 100;
+[on master]
+[on master1]
+CREATE DATABASE d2;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP DATABASE d1;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP DATABASE d2;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+CREATE EVENT e2
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
+DO INSERT INTO test.t1 VALUES (2);
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP EVENT e1;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP EVENT IF EXISTS e2;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+CREATE FUNCTION f2 () RETURNS INT DETERMINISTIC
+RETURN 1;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+ALTER FUNCTION f1 SQL SECURITY INVOKER;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP FUNCTION f1;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+CREATE PROCEDURE p2 (OUT rows INT)
+BEGIN
+SELECT COUNT(*) INTO rows FROM t2;
+END;
+//
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+ALTER PROCEDURE p1 SQL SECURITY INVOKER COMMENT 'return rows of table t1';
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP PROCEDURE p1;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+CREATE TABLE t2 (b int);
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+ALTER TABLE t1 ADD (d int);
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+RENAME TABLE t3 TO t4;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+CREATE INDEX i2 on t1 (a);
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP INDEX i1 on t1;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+CREATE TRIGGER tr2 BEFORE INSERT ON t4
+FOR EACH ROW BEGIN
+DELETE FROM t1 WHERE a=NEW.a;
+END;
+//
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP TRIGGER tr1;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP TRIGGER IF EXISTS tr2;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+CREATE VIEW v2 AS SELECT a FROM t1 WHERE a > 100;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP VIEW v1;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP VIEW IF EXISTS v2;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP TABLE t1;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+DROP TABLE IF EXISTS t2;
+source include/kill_query.inc;
+source include/diff_master_slave.inc;
+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;
diff --git a/mysql-test/suite/rpl/r/rpl_name_const.result b/mysql-test/suite/rpl/r/rpl_name_const.result
new file mode 100644
index 00000000000..acb2684d2c8
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_name_const.result
@@ -0,0 +1,28 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+==== Initialize ====
+[on master]
+create table t1 (id int);
+==== create a procedure that has a column aliase in a subquery ====
+drop procedure if exists test_procedure;
+create procedure test_procedure(_id int)
+begin
+insert into t1 (id)
+select a.id
+from
+( select _id as id ) a;
+end;$$
+==== enable the binary log, then call the procedure ====
+call test_procedure(1234);
+[on slave]
+select * from t1 order by id;
+id
+1234
+==== Clean up ====
+[on master]
+drop table t1;
+drop procedure test_procedure;
diff --git a/mysql-test/suite/rpl/t/rpl_killed_ddl-master.opt b/mysql-test/suite/rpl/t/rpl_killed_ddl-master.opt
new file mode 100644
index 00000000000..aaf2d8a4251
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_killed_ddl-master.opt
@@ -0,0 +1 @@
+--debug=d,debug_lock_before_query_log_event
diff --git a/mysql-test/suite/rpl/t/rpl_killed_ddl.test b/mysql-test/suite/rpl/t/rpl_killed_ddl.test
new file mode 100644
index 00000000000..26bd4957279
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_killed_ddl.test
@@ -0,0 +1,352 @@
+# ==== 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#44041
+# - BUG#43353
+# - BUG#25705
+# - BUG#44171
+
+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 INT)
+ BEGIN
+ SELECT COUNT(*) INTO rows 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;
+echo [on master];
+
+# This will block the execution of a statement at the DBUG_SYNC_POINT
+# with given lock name
+if (`SELECT '$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;
+echo [on master1];
+
+disable_warnings;
+
+######## DATABASE ########
+
+let $diff_statement= SHOW DATABASES LIKE 'd%';
+
+send CREATE DATABASE d2;
+source include/kill_query_and_diff_master_slave.inc;
+
+# Temporarily disabled, see BUG#44041, the ALTER DATABASE can affect the
+# collation of other database on slave
+#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 d2;
+source include/kill_query_and_diff_master_slave.inc;
+
+######## EVENT ########
+
+let $diff_statement= SELECT event_name, event_body, execute_at
+ FROM information_schema.events where event_name like 'e%';
+
+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 because of BUG#44171, killing ALTER EVENT can
+# crash the server
+#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 $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
+#
+# Temporarily disabled. Because of BUG#43353, KILL the query may
+# result in function not found, and for 5.1, DROP statements will be
+# logged if the function is not found on master, so the following DROP
+# FUNCTION statement may be interrupted and not drop the function on
+# master, but still get logged and executed on slave and cause
+# inconsistence. Also disable the following DROP PROCEDURE IF EXITS
+# below.
+#send DROP FUNCTION IF EXISTS f2;
+#source include/kill_query_and_diff_master_slave.inc;
+
+######## PROCEDURE ########
+
+let $diff_statement= SHOW PROCEDURE STATUS LIKE 'p%';
+
+DELIMITER //;
+send CREATE PROCEDURE p2 (OUT rows INT)
+ BEGIN
+ SELECT COUNT(*) INTO rows FROM t2;
+ END;
+ //
+DELIMITER ;//
+source include/kill_query_and_diff_master_slave.inc;
+
+send ALTER PROCEDURE p1 SQL SECURITY INVOKER COMMENT 'return rows 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;
+
+# Temporarily disabled, see comment above for DROP FUNCTION IF EXISTS
+#send DROP PROCEDURE IF EXISTS p2;
+#source include/kill_query_and_diff_master_slave.inc;
+
+######## TABLE ########
+
+let $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 $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 ########
+
+# Tempoarily disabled, see bug#25705
+
+# let $diff_statement= SELECT * FROM mysql.server WHERE 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 ########
+
+let $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 $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 $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;
diff --git a/mysql-test/suite/rpl/t/rpl_name_const.test b/mysql-test/suite/rpl/t/rpl_name_const.test
new file mode 100644
index 00000000000..adb71d452ef
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_name_const.test
@@ -0,0 +1,47 @@
+# ==== Purpose ====
+#
+# Test that aliases of variables in binary log aren't ignored with NAME_CONST.
+#
+# ==== Method ====
+#
+# Create a procedure with aliases of variables, then replicate it to slave.
+# BUG#35515 Aliases of variables in binary log are ignored with NAME_CONST.
+#
+
+source include/master-slave.inc;
+
+--echo ==== Initialize ====
+
+--echo [on master]
+--connection master
+
+create table t1 (id int);
+
+--echo ==== create a procedure that has a column aliase in a subquery ====
+--disable_warnings
+drop procedure if exists test_procedure;
+--enable_warnings
+delimiter $$;
+create procedure test_procedure(_id int)
+begin
+insert into t1 (id)
+select a.id
+from
+( select _id as id ) a;
+end;$$
+delimiter ;$$
+
+--echo ==== enable the binary log, then call the procedure ====
+call test_procedure(1234);
+
+
+--echo [on slave]
+sync_slave_with_master;
+select * from t1 order by id;
+
+--echo ==== Clean up ====
+
+--echo [on master]
+connection master;
+drop table t1;
+drop procedure test_procedure;