228 lines
5.9 KiB
Plaintext
228 lines
5.9 KiB
Plaintext
#########################################
|
|
# Author: Serge Kozlov skozlov@mysql.com
|
|
# Date: 04/25/2007
|
|
# Purpose: Testing Invocation and Invoked
|
|
# Features for Replication.
|
|
#########################################
|
|
|
|
--source include/master-slave.inc
|
|
# Needed since test uses CREATE TABLE ... SELECT
|
|
|
|
disable_query_log;
|
|
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
|
|
enable_query_log;
|
|
|
|
# --disable_warnings/--enable_warnings added before/after query
|
|
# if one uses UUID() function because we need to avoid warnings
|
|
# for STATEMENT binlog format
|
|
|
|
# Transactional engine
|
|
--let $engine_type=XENGINE
|
|
|
|
|
|
#
|
|
# Clean up
|
|
#
|
|
|
|
USE test;
|
|
--disable_warnings
|
|
DROP VIEW IF EXISTS v11;
|
|
DROP TABLE IF EXISTS t11,t12,t13;
|
|
DROP PROCEDURE IF EXISTS p11;
|
|
DROP FUNCTION IF EXISTS f1;
|
|
DROP FUNCTION IF EXISTS f2;
|
|
DROP EVENT IF EXISTS e11;
|
|
--enable_warnings
|
|
|
|
|
|
#
|
|
# Prepare objects (tables etc)
|
|
#
|
|
|
|
# Create tables
|
|
eval CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type;
|
|
INSERT INTO t11 VALUES (1,1,'1');
|
|
--disable_warnings
|
|
INSERT INTO t11 VALUES (2,2,UUID());
|
|
--enable_warnings
|
|
eval CREATE TABLE t12 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type;
|
|
INSERT INTO t12 VALUES (1,1,'1');
|
|
--disable_warnings
|
|
INSERT INTO t12 VALUES (2,2,UUID());
|
|
--enable_warnings
|
|
|
|
# Create invoked features
|
|
--echo
|
|
CREATE VIEW v11 AS SELECT * FROM t11;
|
|
|
|
DELIMITER |;
|
|
|
|
CREATE TRIGGER t11_tr1 BEFORE INSERT ON t11 FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO t12 VALUES (NEW.a, NEW.b, NEW.c);
|
|
INSERT INTO t13 VALUES (NEW.a, NEW.b, NEW.c);
|
|
END|
|
|
|
|
CREATE TRIGGER t11_tr2 BEFORE UPDATE ON t11 FOR EACH ROW
|
|
BEGIN
|
|
UPDATE t12 SET c = '';
|
|
UPDATE t13 SET c = '';
|
|
END|
|
|
|
|
# Create events which will run every 1 sec
|
|
CREATE EVENT e11 ON SCHEDULE EVERY 1 SECOND DISABLE DO
|
|
BEGIN
|
|
ALTER EVENT e11 DISABLE;
|
|
CALL p11(10, '');
|
|
END|
|
|
|
|
# Create functions and procedures used for events
|
|
CREATE FUNCTION f1 (x INT) RETURNS VARCHAR(64)
|
|
BEGIN
|
|
IF x > 5 THEN
|
|
RETURN UUID();
|
|
END IF;
|
|
RETURN '';
|
|
END|
|
|
|
|
CREATE FUNCTION f2 (x INT) RETURNS VARCHAR(64)
|
|
BEGIN
|
|
RETURN f1(x);
|
|
END|
|
|
|
|
CREATE PROCEDURE p11 (IN x INT, IN y VARCHAR(64))
|
|
BEGIN
|
|
INSERT IGNORE INTO t11 VALUES (x,x,y);
|
|
END|
|
|
|
|
DELIMITER ;|
|
|
|
|
|
|
#
|
|
# Start test case
|
|
#
|
|
# Do some actions for transactional tables
|
|
--echo
|
|
--disable_warnings
|
|
CREATE TABLE t13 SELECT * FROM v11;
|
|
INSERT INTO t11 VALUES (3,3,'');
|
|
UPDATE t11 SET c='2' WHERE a = 1;
|
|
INSERT INTO t11 VALUES(4,4,f1(4));
|
|
INSERT INTO t11 VALUES (100,100,'');
|
|
CALL p11(5, UUID());
|
|
INSERT INTO t11 VALUES (101,101,'');
|
|
INSERT INTO t11 VALUES(6,6,f1(6));
|
|
INSERT INTO t11 VALUES (102,102,'');
|
|
INSERT INTO t11 VALUES(7,7,f2(7));
|
|
INSERT INTO t11 VALUES (103,103,'');
|
|
--enable_warnings
|
|
|
|
# Scheduler is on
|
|
--echo
|
|
# Temporally events fire sequentally due Bug#29020.
|
|
SET @old_event_scheduler= @@global.event_scheduler;
|
|
SET GLOBAL EVENT_SCHEDULER = on;
|
|
# Wait while events will executed
|
|
ALTER EVENT e11 ENABLE;
|
|
let $wait_condition= SELECT COUNT(*) = 1 FROM t11 WHERE t11.a = 10;
|
|
--source include/wait_condition_or_abort.inc
|
|
SET @@global.event_scheduler= @old_event_scheduler;
|
|
|
|
# Check original objects
|
|
--echo
|
|
--sorted_result
|
|
SHOW TABLES LIKE 't%';
|
|
--sorted_result
|
|
SELECT table_name FROM information_schema.views WHERE table_schema='test';
|
|
--sorted_result
|
|
SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
|
|
--sorted_result
|
|
SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
|
|
--sorted_result
|
|
SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
|
|
|
|
# Check original data
|
|
--echo
|
|
SELECT COUNT(*) FROM t11;
|
|
SELECT a,b FROM t11 ORDER BY a;
|
|
SELECT COUNT(*) FROM t12;
|
|
SELECT a,b FROM t12 ORDER BY a;
|
|
SELECT COUNT(*) FROM t13;
|
|
SELECT a,b FROM t13 ORDER BY a;
|
|
SELECT a,b FROM v11 ORDER BY a;
|
|
|
|
--let $sync_slave_connection= slave
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
# Check replicated objects
|
|
--echo
|
|
--sorted_result
|
|
SHOW TABLES LIKE 't%';
|
|
--sorted_result
|
|
SELECT table_name FROM information_schema.views WHERE table_schema='test';
|
|
--sorted_result
|
|
SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
|
|
--sorted_result
|
|
SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
|
|
--sorted_result
|
|
SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
|
|
|
|
# Check replicated data
|
|
--echo
|
|
SELECT COUNT(*) FROM t11;
|
|
SELECT a,b FROM t11 ORDER BY a;
|
|
SELECT COUNT(*) FROM t12;
|
|
SELECT a,b FROM t12 ORDER BY a;
|
|
SELECT COUNT(*) FROM t13;
|
|
SELECT a,b FROM t13 ORDER BY a;
|
|
SELECT a,b FROM v11 ORDER BY a;
|
|
|
|
# Remove UUID() before comparing and sort tables
|
|
|
|
--connection master
|
|
--echo
|
|
--disable_warnings
|
|
UPDATE t11 SET c='';
|
|
--enable_warnings
|
|
UPDATE t12 SET c='';
|
|
UPDATE t13 SET c='';
|
|
|
|
ALTER TABLE t13 ORDER BY a;
|
|
|
|
--let $sync_slave_connection= slave
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
# Compare a data from master and slave
|
|
--echo
|
|
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql
|
|
--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql
|
|
|
|
|
|
#
|
|
# Clean up
|
|
#
|
|
|
|
# Remove dumps
|
|
--echo
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql
|
|
|
|
# Remove tables,views,procedures,functions
|
|
--connection master
|
|
--echo
|
|
DROP VIEW IF EXISTS v11;
|
|
DROP TABLE IF EXISTS t11,t12,t13;
|
|
DROP PROCEDURE IF EXISTS p11;
|
|
DROP FUNCTION IF EXISTS f1;
|
|
DROP FUNCTION IF EXISTS f2;
|
|
DROP EVENT IF EXISTS e11;
|
|
|
|
--let $sync_slave_connection= slave
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
# End 5.1 test case
|
|
--source include/rpl_end.inc
|
|
|
|
--source suite/xengine/include/check_xengine_log_error.inc
|