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

#########################################
# Author:  Serge Kozlov skozlov@mysql.com
# Date:    04/25/2007
# Purpose: Testing Invocation and Invoked
#          Features for Replication.
#########################################

--source include/have_innodb.inc
--source include/master-slave.inc

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

# Non-transactional engine
--let $engine_type= myisam

# Transactional engine
--let $engine_type2= innodb


#
# Clean up
#

USE test;
--disable_warnings
DROP VIEW IF EXISTS v1,v11;
DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p11;
DROP FUNCTION IF EXISTS f1;
DROP FUNCTION IF EXISTS f2;
DROP EVENT IF EXISTS e1;
DROP EVENT IF EXISTS e11;
--enable_warnings


#
# Prepare objects (tables etc)
#

# Create tables

--echo
eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type;
INSERT INTO t1 VALUES (1,1,'1');
--disable_warnings
INSERT INTO t1 VALUES (2,2,UUID());
--enable_warnings
eval CREATE TABLE t2 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type;
INSERT INTO t2 VALUES (1,1,'1');
--disable_warnings
INSERT INTO t2 VALUES (2,2,UUID());
--enable_warnings

eval CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type2;
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_type2;
INSERT INTO t12 VALUES (1,1,'1');
--disable_warnings
INSERT INTO t12 VALUES (2,2,UUID());
--enable_warnings

# Create invoked features
--echo
# Create view for tables t1,t11
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v11 AS SELECT * FROM t11;

# Create triggers for t1,t11
DELIMITER |;

CREATE TRIGGER t1_tr1 BEFORE INSERT ON t1 FOR EACH ROW 
BEGIN
  INSERT INTO t2 VALUES (NEW.a, NEW.b, NEW.c);
  INSERT INTO t3 VALUES (NEW.a, NEW.b, NEW.c);
END|

CREATE TRIGGER t1_tr2 BEFORE UPDATE ON t1 FOR EACH ROW 
BEGIN
  UPDATE t2 SET c = '';
  UPDATE t3 SET c = '';
END|

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 e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO
BEGIN
  ALTER EVENT e1 DISABLE;
  CALL p1(10, '');  
END|

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 p1 (IN x INT, IN y VARCHAR(64))
BEGIN
  INSERT IGNORE INTO t1 VALUES (x,x,y);
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 non-transactional tables
--echo
CREATE TABLE t3 SELECT * FROM v1;
INSERT INTO t1 VALUES (3,3,'');
UPDATE t1 SET c='2' WHERE a = 1;
--disable_warnings
INSERT INTO t1 VALUES(4,4,f1(4));
--enable_warnings
INSERT INTO t1 VALUES (100,100,'');
--disable_warnings
CALL p1(5, UUID());
--enable_warnings
INSERT INTO t1 VALUES (101,101,'');
--disable_warnings
INSERT INTO t1 VALUES(6,6,f1(6));
--enable_warnings
INSERT INTO t1 VALUES (102,102,'');
--disable_warnings
INSERT INTO t1 VALUES(7,7,f2(7));
--enable_warnings
INSERT INTO t1 VALUES (103,103,'');

# 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 GLOBAL EVENT_SCHEDULER = on;
# Wait while events will executed
ALTER EVENT e1 ENABLE;
let $wait_condition= SELECT COUNT(*) = 1 FROM t1 WHERE t1.a = 10;
--source include/wait_condition.inc
ALTER EVENT e11 ENABLE;
let $wait_condition= SELECT COUNT(*) = 1 FROM t11 WHERE t11.a = 10;
--source include/wait_condition.inc
SET GLOBAL EVENT_SCHEDULER = off;

# 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 t1;
SELECT a,b FROM t1 ORDER BY a;
SELECT COUNT(*) FROM t2;
SELECT a,b FROM t2 ORDER BY a;
SELECT COUNT(*) FROM t3;
SELECT a,b FROM t3 ORDER BY a;
SELECT a,b FROM v1 ORDER BY a;
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;

--sync_slave_with_master slave

# 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 t1;
SELECT a,b FROM t1 ORDER BY a;
SELECT COUNT(*) FROM t2;
SELECT a,b FROM t2 ORDER BY a;
SELECT COUNT(*) FROM t3;
SELECT a,b FROM t3 ORDER BY a;
SELECT a,b FROM v1 ORDER BY a;
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
UPDATE t1 SET c='';
UPDATE t2 SET c='';
UPDATE t3 SET c='';
--disable_warnings
UPDATE t11 SET c='';
--enable_warnings
UPDATE t12 SET c='';
UPDATE t13 SET c='';

ALTER TABLE t3 ORDER BY a;
ALTER TABLE t13 ORDER BY a;

--sync_slave_with_master slave

# 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 v1,v11;
DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p11;
DROP FUNCTION IF EXISTS f1;
DROP FUNCTION IF EXISTS f2;
DROP EVENT IF EXISTS e1;
DROP EVENT IF EXISTS e11;

--sync_slave_with_master slave

# End 5.1 test case
--source include/rpl_end.inc