Debian Codegraph @

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