Debian Codegraph @

mysql-test/suite/group_replication/t/gr_invoked_features.test

raw
1 ################################################################################
2 # Purpose: Testing the behavior of execution of Invoked Features for
3 # Group Replication.
4 #
5 # Test:
6 # 0. The test requires two servers: M1 and M2.
7 # 1. With both members ONLINE. Create tables and add some data.
8 # 2. Create invoked features in M1 and M2.
9 # - On M1, create view for table.
10 # - On M1, create trigger.
11 # - On M2, create event.
12 # - On M1, create function.
13 # - On M2, create procedure for event.
14 # - Sync everything.
15 # 3. Do some actions to check the effect of the invoked features on the
16 # transactional tables.
17 # 4. Check original objects.
18 # 5. Sync the two members and check replicated objects in the group.
19 # 6. Clean up.
20 ################################################################################
21
22 --source include/set_privilege_checks_user_as_system_user.inc
23 --source include/have_group_replication_plugin.inc
24 --source include/group_replication.inc
25
26 #
27 # Prepare objects (tables etc)
28 #
29 # Create tables
30 --eval CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=innodb
31 INSERT INTO t11 VALUES (1,1,'1');
32 --disable_warnings
33 INSERT INTO t11 VALUES (2,2,UUID());
34 --enable_warnings
35 --eval CREATE TABLE t12 (a INT PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=innodb
36 INSERT INTO t12 VALUES (1,1,'1');
37 --disable_warnings
38 INSERT INTO t12 VALUES (2,2,UUID());
39 --enable_warnings
40
41 # Create invoked features on member1 and member2 and see its behavior
42 --echo
43 # Create view for tables t11
44 CREATE VIEW v11 AS SELECT * FROM t11;
45
46 # Creating some invoked features on group members and check its behavior on the group.
47 --connection server1
48 DELIMITER |;
49
50 CREATE TRIGGER t11_tr1 BEFORE INSERT ON t11 FOR EACH ROW
51 BEGIN
52 INSERT INTO t12 VALUES (NEW.a, NEW.b, NEW.c);
53 INSERT INTO t13 VALUES (NEW.a, NEW.b, NEW.c);
54 END|
55
56 DELIMITER ;|
57
58 --connection server2
59
60 DELIMITER |;
61 CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO
62 BEGIN
63 ALTER EVENT e1 DISABLE;
64 CALL p11(10, '');
65 END|
66
67 DELIMITER ;|
68
69 # Create functions and procedures used for events
70 --connection server1
71
72 DELIMITER |;
73
74 CREATE FUNCTION f1 (x INT) RETURNS VARCHAR(64)
75 BEGIN
76 IF x > 5 THEN
77 RETURN UUID();
78 END IF;
79 RETURN '';
80 END|
81
82 DELIMITER ;|
83
84 --connection server2
85
86 DELIMITER |;
87
88 CREATE PROCEDURE p11 (IN x INT, IN y VARCHAR(64))
89 BEGIN
90 INSERT IGNORE INTO t11 VALUES (x,x,y);
91 END|
92
93 DELIMITER ;|
94
95 # Syncing the second member with the first one
96 --source include/rpl_sync.inc
97 --connection server1
98
99 #
100 # Start test case
101 #
102 # Do some actions to check the effect of the invoked features on the
103 # transactional tables.
104
105 --echo
106 --disable_warnings
107 CREATE TABLE t13(a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) engine=InnoDB;
108 INSERT INTO t11 VALUES (3,3,'');
109 UPDATE t11 SET c='2' WHERE a = 1;
110 --source include/rpl_sync.inc
111
112 --connection server2
113 INSERT INTO t11 VALUES(4,4,f1(4));
114 --connection server1
115 INSERT INTO t11 VALUES(7,7,f1(7));
116
117 --connection server1
118 CALL p11(5, UUID());
119 --connection server2
120 CALL p11(10, UUID());
121
122 --enable_warnings
123 --connection server1
124 # Scheduler is on
125 SET @old_event_scheduler= @@global.event_scheduler;
126 SET GLOBAL EVENT_SCHEDULER = on;
127 # Wait while events will executed
128 ALTER EVENT e1 ENABLE;
129 --let $wait_condition= SELECT COUNT(*) = 1 FROM t11 WHERE t11.a = 10
130 --source include/wait_condition_or_abort.inc
131 #
132 # Wait for the event to have been disabled.
133 #
134 # There may be a race here, since it looks like the
135 # event thread might not have the chance to disable
136 # the event before it is shutdown. Note that the
137 # act of disabling the event is done inside the
138 # event itself!
139 #
140 --let $wait_condition= SELECT status="DISABLED" FROM information_schema.events WHERE event_schema='test' AND event_name="e1"
141 --source include/wait_condition_or_abort.inc
142 SET GLOBAL EVENT_SCHEDULER = @old_event_scheduler;
143
144 --source include/rpl_sync.inc
145 # Check original objects
146 --echo
147 --sorted_result
148 SHOW TABLES LIKE 't%';
149 --sorted_result
150 SELECT table_name FROM information_schema.views WHERE table_schema='test';
151 --sorted_result
152 SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
153 --sorted_result
154 SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
155 --sorted_result
156 SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
157
158 # Syncing the two members involved in the group
159 --source include/rpl_sync.inc
160
161 --let $diff_tables= server1:t11, server2:t11
162 --source include/diff_tables.inc
163
164 --let $diff_tables= server1:t12, server2:t12
165 --source include/diff_tables.inc
166
167 --let $diff_tables= server1:t13, server2:t13
168 --source include/diff_tables.inc
169
170 # Check replicated objects in the group
171 --echo
172 --sorted_result
173 SHOW TABLES LIKE 't%';
174 --sorted_result
175 SELECT table_name FROM information_schema.views WHERE table_schema='test';
176 --sorted_result
177 SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
178 --sorted_result
179 SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
180 --sorted_result
181 SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
182
183 #
184 # Clean up
185 #
186 # Remove tables,views,procedures,functions
187 --connection server1
188 DROP VIEW IF EXISTS v11;
189 DROP TABLE IF EXISTS t11,t12,t13;
190 DROP PROCEDURE IF EXISTS p11;
191 DROP FUNCTION IF EXISTS f1;
192 DROP EVENT IF EXISTS e1;
193
194 --source include/group_replication_end.inc