include/master-slave.inc
[connection master]
USE test;
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;

CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=myisam;
INSERT INTO t1 VALUES (1,1,'1');
INSERT INTO t1 VALUES (2,2,UUID());
CREATE TABLE t2 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=myisam;
INSERT INTO t2 VALUES (1,1,'1');
INSERT INTO t2 VALUES (2,2,UUID());
CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=innodb;
INSERT INTO t11 VALUES (1,1,'1');
INSERT INTO t11 VALUES (2,2,UUID());
CREATE TABLE t12 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=innodb;
INSERT INTO t12 VALUES (1,1,'1');
INSERT INTO t12 VALUES (2,2,UUID());

CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v11 AS SELECT * FROM t11;
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 EVENT e1 ON SCHEDULE EVERY 10 SECOND DISABLE DO
BEGIN
ALTER EVENT e1 DISABLE;
CALL p1(10, '');  
END|
Warnings:
Warning	1105	Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
CREATE EVENT e11 ON SCHEDULE EVERY 10 SECOND DISABLE DO
BEGIN
ALTER EVENT e11 DISABLE;
CALL p11(10, '');  
END|
Warnings:
Warning	1105	Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
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|

CREATE TABLE t3 SELECT * FROM v1;
INSERT INTO t1 VALUES (3,3,'');
UPDATE t1 SET c='2' WHERE a = 1;
INSERT INTO t1 VALUES(4,4,f1(4));
INSERT INTO t1 VALUES (100,100,'');
CALL p1(5, UUID());
INSERT INTO t1 VALUES (101,101,'');
INSERT INTO t1 VALUES(6,6,f1(6));
INSERT INTO t1 VALUES (102,102,'');
INSERT INTO t1 VALUES(7,7,f2(7));
INSERT INTO t1 VALUES (103,103,'');

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,'');

SET GLOBAL EVENT_SCHEDULER = on;
ALTER EVENT e1 ENABLE;
ALTER EVENT e11 ENABLE;
SET GLOBAL EVENT_SCHEDULER = off;

SHOW TABLES LIKE 't%';
Tables_in_test (t%)
t1
t11
t12
t13
t2
t3
SELECT table_name FROM information_schema.views WHERE table_schema='test';
table_name
v1
v11
SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name	event_manipulation	event_object_table
t11_tr1	INSERT	t11
t11_tr2	UPDATE	t11
t1_tr1	INSERT	t1
t1_tr2	UPDATE	t1
SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
routine_type	routine_name
FUNCTION	f1
FUNCTION	f2
PROCEDURE	p1
PROCEDURE	p11
SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
event_name	status
e1	DISABLED
e11	DISABLED

SELECT COUNT(*) FROM t1;
COUNT(*)
12
SELECT a,b FROM t1 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t2;
COUNT(*)
12
SELECT a,b FROM t2 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t3;
COUNT(*)
12
SELECT a,b FROM t3 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT a,b FROM v1 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t11;
COUNT(*)
12
SELECT a,b FROM t11 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t12;
COUNT(*)
12
SELECT a,b FROM t12 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t13;
COUNT(*)
12
SELECT a,b FROM t13 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT a,b FROM v11 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
connection slave;

SHOW TABLES LIKE 't%';
Tables_in_test (t%)
t1
t11
t12
t13
t2
t3
SELECT table_name FROM information_schema.views WHERE table_schema='test';
table_name
v1
v11
SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
trigger_name	event_manipulation	event_object_table
t11_tr1	INSERT	t11
t11_tr2	UPDATE	t11
t1_tr1	INSERT	t1
t1_tr2	UPDATE	t1
SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
routine_type	routine_name
FUNCTION	f1
FUNCTION	f2
PROCEDURE	p1
PROCEDURE	p11
SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
event_name	status
e1	SLAVESIDE_DISABLED
e11	SLAVESIDE_DISABLED

SELECT COUNT(*) FROM t1;
COUNT(*)
12
SELECT a,b FROM t1 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t2;
COUNT(*)
12
SELECT a,b FROM t2 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t3;
COUNT(*)
12
SELECT a,b FROM t3 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT a,b FROM v1 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t11;
COUNT(*)
12
SELECT a,b FROM t11 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t12;
COUNT(*)
12
SELECT a,b FROM t12 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT COUNT(*) FROM t13;
COUNT(*)
12
SELECT a,b FROM t13 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
SELECT a,b FROM v11 ORDER BY a;
a	b
1	1
2	2
3	3
4	4
5	5
6	6
7	7
10	10
100	100
101	101
102	102
103	103
connection master;

UPDATE t1 SET c='';
UPDATE t2 SET c='';
UPDATE t3 SET c='';
UPDATE t11 SET c='';
UPDATE t12 SET c='';
UPDATE t13 SET c='';
ALTER TABLE t3 ORDER BY a;
ALTER TABLE t13 ORDER BY a;
connection slave;


connection master;

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;
connection slave;
include/rpl_end.inc