mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
446 lines
7.3 KiB
Text
446 lines
7.3 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
|
|
call mtr.add_suppression("Event Scheduler: .* Duplicate entry '10' for key 'a'");
|
|
# Create tables
|
|
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 invoked features
|
|
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 1 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|
|
|
# Do some actions for non-transactional tables
|
|
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,'');
|
|
# Do some actions for transactional tables
|
|
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,'');
|
|
# Scheduler is on
|
|
SET GLOBAL EVENT_SCHEDULER = on;
|
|
ALTER EVENT e1 ENABLE;
|
|
ALTER EVENT e11 ENABLE;
|
|
SET GLOBAL EVENT_SCHEDULER = off;
|
|
# Check original objects
|
|
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;
|
|
# Check replicated objects
|
|
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
|
|
# Check replicated data
|
|
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;
|
|
# Remove UUID() before comparing and sort tables
|
|
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;
|
|
# Compare a data from master and 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
|