Debian Codegraph @

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