Debian Codegraph @

mysql-test/suite/rpl/t/rpl_invoked_features.test

raw
1 #########################################
2 # Author: Serge Kozlov [email protected]
3 # Date: 04/25/2007
4 # Purpose: Testing Invocation and Invoked
5 # Features for Replication.
6 #########################################
7
8 --source include/have_innodb.inc
9 --source include/master-slave.inc
10
11 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
12 call mtr.add_suppression("Event Scheduler: .* Duplicate entry '10' for key 'a'");
13
14 # --disable_warnings/--enable_warnings added before/after query
15 # if one uses UUID() function because we need to avoid warnings
16 # for STATEMENT binlog format
17
18 # Non-transactional engine
19 --let $engine_type= myisam
20
21 # Transactional engine
22 --let $engine_type2= innodb
23
24 #
25 # Prepare objects (tables etc)
26 #
27
28 --echo # Create tables
29
30 eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type;
31 INSERT INTO t1 VALUES (1,1,'1');
32 --disable_warnings ONCE
33 INSERT INTO t1 VALUES (2,2,UUID());
34 eval CREATE TABLE t2 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type;
35 INSERT INTO t2 VALUES (1,1,'1');
36 --disable_warnings ONCE
37 INSERT INTO t2 VALUES (2,2,UUID());
38
39 eval CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type2;
40 INSERT INTO t11 VALUES (1,1,'1');
41 --disable_warnings ONCE
42 INSERT INTO t11 VALUES (2,2,UUID());
43 eval CREATE TABLE t12 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type2;
44 INSERT INTO t12 VALUES (1,1,'1');
45 --disable_warnings ONCE
46 INSERT INTO t12 VALUES (2,2,UUID());
47
48 --echo # Create invoked features
49
50 CREATE VIEW v1 AS SELECT * FROM t1;
51 CREATE VIEW v11 AS SELECT * FROM t11;
52
53 DELIMITER |;
54
55 CREATE TRIGGER t1_tr1 BEFORE INSERT ON t1 FOR EACH ROW
56 BEGIN
57 INSERT INTO t2 VALUES (NEW.a, NEW.b, NEW.c);
58 INSERT INTO t3 VALUES (NEW.a, NEW.b, NEW.c);
59 END|
60
61 CREATE TRIGGER t1_tr2 BEFORE UPDATE ON t1 FOR EACH ROW
62 BEGIN
63 UPDATE t2 SET c = '';
64 UPDATE t3 SET c = '';
65 END|
66
67 CREATE TRIGGER t11_tr1 BEFORE INSERT ON t11 FOR EACH ROW
68 BEGIN
69 INSERT INTO t12 VALUES (NEW.a, NEW.b, NEW.c);
70 INSERT INTO t13 VALUES (NEW.a, NEW.b, NEW.c);
71 END|
72
73 CREATE TRIGGER t11_tr2 BEFORE UPDATE ON t11 FOR EACH ROW
74 BEGIN
75 UPDATE t12 SET c = '';
76 UPDATE t13 SET c = '';
77 END|
78
79 CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO
80 BEGIN
81 ALTER EVENT e1 DISABLE;
82 CALL p1(10, '');
83 END|
84
85 CREATE EVENT e11 ON SCHEDULE EVERY 10 SECOND DISABLE DO
86 BEGIN
87 ALTER EVENT e11 DISABLE;
88 CALL p11(10, '');
89 END|
90
91 CREATE FUNCTION f1 (x INT) RETURNS VARCHAR(64)
92 BEGIN
93 IF x > 5 THEN
94 RETURN UUID();
95 END IF;
96 RETURN '';
97 END|
98
99 CREATE FUNCTION f2 (x INT) RETURNS VARCHAR(64)
100 BEGIN
101 RETURN f1(x);
102 END|
103
104 CREATE PROCEDURE p1 (IN x INT, IN y VARCHAR(64))
105 BEGIN
106 INSERT IGNORE INTO t1 VALUES (x,x,y);
107 END|
108
109 CREATE PROCEDURE p11 (IN x INT, IN y VARCHAR(64))
110 BEGIN
111 INSERT IGNORE INTO t11 VALUES (x,x,y);
112 END|
113
114 DELIMITER ;|
115
116
117 #
118 # Start test case
119 #
120
121 --echo # Do some actions for non-transactional tables
122 CREATE TABLE t3 SELECT * FROM v1;
123 INSERT INTO t1 VALUES (3,3,'');
124 UPDATE t1 SET c='2' WHERE a = 1;
125 --disable_warnings ONCE
126 INSERT INTO t1 VALUES(4,4,f1(4));
127 INSERT INTO t1 VALUES (100,100,'');
128 CALL p1(5, UUID());
129 INSERT INTO t1 VALUES (101,101,'');
130 --disable_warnings ONCE
131 INSERT INTO t1 VALUES(6,6,f1(6));
132 INSERT INTO t1 VALUES (102,102,'');
133 --disable_warnings ONCE
134 INSERT INTO t1 VALUES(7,7,f2(7));
135 INSERT INTO t1 VALUES (103,103,'');
136
137 --echo # Do some actions for transactional tables
138 CREATE TABLE t13 SELECT * FROM v11;
139 INSERT INTO t11 VALUES (3,3,'');
140 UPDATE t11 SET c='2' WHERE a = 1;
141 --disable_warnings ONCE
142 INSERT INTO t11 VALUES(4,4,f1(4));
143 INSERT INTO t11 VALUES (100,100,'');
144 CALL p11(5, UUID());
145 INSERT INTO t11 VALUES (101,101,'');
146 --disable_warnings ONCE
147 INSERT INTO t11 VALUES(6,6,f1(6));
148 INSERT INTO t11 VALUES (102,102,'');
149 --disable_warnings ONCE
150 INSERT INTO t11 VALUES(7,7,f2(7));
151 INSERT INTO t11 VALUES (103,103,'');
152
153 --echo # Scheduler is on
154 # Temporally events fire sequentally due Bug#29020.
155 SET GLOBAL EVENT_SCHEDULER = on;
156 # Wait while events will executed
157 ALTER EVENT e1 ENABLE;
158 let $wait_condition= SELECT COUNT(*) = 1 FROM t1 WHERE t1.a = 10;
159 --source include/wait_condition.inc
160 ALTER EVENT e11 ENABLE;
161 let $wait_condition= SELECT COUNT(*) = 1 FROM t11 WHERE t11.a = 10;
162 --source include/wait_condition.inc
163 SET GLOBAL EVENT_SCHEDULER = off;
164
165 --echo # Check original objects
166 --sorted_result
167 SHOW TABLES LIKE 't%';
168 --sorted_result
169 SELECT table_name FROM information_schema.views WHERE table_schema='test';
170 --sorted_result
171 SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
172 --sorted_result
173 SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
174 --sorted_result
175 SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
176
177 # Check original data
178 --echo
179 SELECT COUNT(*) FROM t1;
180 SELECT a,b FROM t1 ORDER BY a;
181 SELECT COUNT(*) FROM t2;
182 SELECT a,b FROM t2 ORDER BY a;
183 SELECT COUNT(*) FROM t3;
184 SELECT a,b FROM t3 ORDER BY a;
185 SELECT a,b FROM v1 ORDER BY a;
186 SELECT COUNT(*) FROM t11;
187 SELECT a,b FROM t11 ORDER BY a;
188 SELECT COUNT(*) FROM t12;
189 SELECT a,b FROM t12 ORDER BY a;
190 SELECT COUNT(*) FROM t13;
191 SELECT a,b FROM t13 ORDER BY a;
192 SELECT a,b FROM v11 ORDER BY a;
193
194 --sync_slave_with_master slave
195
196 --echo # Check replicated objects
197 --sorted_result
198 SHOW TABLES LIKE 't%';
199 --sorted_result
200 SELECT table_name FROM information_schema.views WHERE table_schema='test';
201 --sorted_result
202 SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
203 --sorted_result
204 SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
205 --sorted_result
206 SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
207
208 --echo # Check replicated data
209 SELECT COUNT(*) FROM t1;
210 SELECT a,b FROM t1 ORDER BY a;
211 SELECT COUNT(*) FROM t2;
212 SELECT a,b FROM t2 ORDER BY a;
213 SELECT COUNT(*) FROM t3;
214 SELECT a,b FROM t3 ORDER BY a;
215 SELECT a,b FROM v1 ORDER BY a;
216 SELECT COUNT(*) FROM t11;
217 SELECT a,b FROM t11 ORDER BY a;
218 SELECT COUNT(*) FROM t12;
219 SELECT a,b FROM t12 ORDER BY a;
220 SELECT COUNT(*) FROM t13;
221 SELECT a,b FROM t13 ORDER BY a;
222 SELECT a,b FROM v11 ORDER BY a;
223
224 --connection master
225 --echo # Remove UUID() before comparing and sort tables
226 UPDATE t1 SET c='';
227 UPDATE t2 SET c='';
228 UPDATE t3 SET c='';
229 --disable_warnings
230 UPDATE t11 SET c='';
231 --enable_warnings
232 UPDATE t12 SET c='';
233 UPDATE t13 SET c='';
234
235 ALTER TABLE t3 ORDER BY a;
236 ALTER TABLE t13 ORDER BY a;
237
238 --sync_slave_with_master slave
239
240 --echo # Compare a data from master and slave
241 --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql
242 --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql
243 --diff_files $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql
244
245 #
246 # Clean up
247 #
248
249 --remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql
250 --remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql
251
252 --connection master
253 DROP VIEW IF EXISTS v1,v11;
254 DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;
255 DROP PROCEDURE IF EXISTS p1;
256 DROP PROCEDURE IF EXISTS p11;
257 DROP FUNCTION IF EXISTS f1;
258 DROP FUNCTION IF EXISTS f2;
259 DROP EVENT IF EXISTS e1;
260 DROP EVENT IF EXISTS e11;
261
262 --sync_slave_with_master slave
263
264 # End 5.1 test case
265 --source include/rpl_end.inc