Debian Codegraph @

mysql-test/suite/rpl/include/rpl_mixed_dml.inc

raw
1 #########################################
2 # Purpose: testing the replication in mixed mode
3 # Requirements: define binlog format for mysqld as in example below:
4 # ./mysql-test-run.pl --mysqld=--binlog-format=mixed
5 #########################################
6
7 --let $rpl_privilege_checks_user_grant_option = 1
8 --source include/set_privilege_checks_user_as_system_user.inc
9 --source include/master-slave.inc
10
11 # Check MIXED on both master and slave
12 connection master;
13 --echo ==========MASTER==========
14 --source suite/rpl/include/rpl_mixed_show_binlog_format.inc
15 connection slave;
16 --echo ==========SLAVE===========
17 --source suite/rpl/include/rpl_mixed_show_binlog_format.inc
18 connection master;
19
20
21 CREATE DATABASE test_rpl;
22
23 --echo
24 --echo ******************** PREPARE TESTING ********************
25 USE test_rpl;
26 eval CREATE TABLE t1 (a int auto_increment not null, b char(254), PRIMARY KEY(a)) ENGINE=$engine_type;
27 eval CREATE TABLE t2 (a int auto_increment not null, b char(254), PRIMARY KEY(a)) ENGINE=$engine_type;
28
29 # DELETE
30 INSERT INTO t1 VALUES(1, 't1, text 1');
31 INSERT INTO t1 VALUES(2, 't1, text 2');
32 INSERT INTO t2 VALUES(1, 't2, text 1');
33 --echo
34 --echo ******************** DELETE ********************
35 DELETE FROM t1 WHERE a = 1;
36 DELETE FROM t2 WHERE b <> UUID();
37 --source suite/rpl/include/rpl_mixed_check_select.inc
38 --source suite/rpl/include/rpl_mixed_clear_tables.inc
39
40 # INSERT
41 --echo
42 --echo ******************** INSERT ********************
43 INSERT INTO t1 VALUES(1, 't1, text 1');
44 INSERT INTO t1 VALUES(2, UUID());
45 INSERT INTO t2 SELECT * FROM t1;
46 INSERT INTO t2 VALUES (1, 't1, text 1') ON DUPLICATE KEY UPDATE b = 't2, text 1';
47 DELETE FROM t1 WHERE a = 2;
48 DELETE FROM t2 WHERE a = 2;
49 --source suite/rpl/include/rpl_mixed_check_select.inc
50 --source suite/rpl/include/rpl_mixed_clear_tables.inc
51
52 --echo
53 --echo ******************** LOAD DATA INFILE ********************
54 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
55 eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/std_data/rpl_mixed.dat' INTO TABLE t1 FIELDS TERMINATED BY '|' ;
56 SELECT * FROM t1 ORDER BY a;
57 --source suite/rpl/include/rpl_mixed_check_select.inc
58 --source suite/rpl/include/rpl_mixed_clear_tables.inc
59
60 # REPLACE
61 --echo
62 --echo ******************** REPLACE ********************
63 INSERT INTO t1 VALUES(1, 't1, text 1');
64 INSERT INTO t1 VALUES(2, 't1, text 2');
65 INSERT INTO t1 VALUES(3, 't1, text 3');
66 REPLACE INTO t1 VALUES(1, 't1, text 11');
67 REPLACE INTO t1 VALUES(2, UUID());
68 REPLACE INTO t1 SET a=3, b='t1, text 33';
69 DELETE FROM t1 WHERE a = 2;
70 --source suite/rpl/include/rpl_mixed_check_select.inc
71 --source suite/rpl/include/rpl_mixed_clear_tables.inc
72
73 # SELECT
74 --echo
75 --echo ******************** SELECT ********************
76 INSERT INTO t1 VALUES(1, 't1, text 1');
77 SELECT * FROM t1 WHERE b <> UUID() ORDER BY a;
78 --source suite/rpl/include/rpl_mixed_clear_tables.inc
79
80 # JOIN
81 --echo
82 --echo ******************** JOIN ********************
83 INSERT INTO t1 VALUES(1, 'CCC');
84 INSERT INTO t1 VALUES(2, 'DDD');
85 INSERT INTO t2 VALUES(1, 'DDD');
86 INSERT INTO t2 VALUES(2, 'CCC');
87 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a ORDER BY t1.a,t2.a;
88 SELECT * FROM t1 INNER JOIN t2 ON t1.b = t2.b ORDER BY t1.a,t2.a;
89 --source suite/rpl/include/rpl_mixed_clear_tables.inc
90
91 # UNION
92 --echo
93 --echo ******************** UNION ********************
94 INSERT INTO t1 VALUES(1, 't1, text 1');
95 INSERT INTO t2 VALUES(1, 't2, text 1');
96 SELECT * FROM t1 UNION SELECT * FROM t2 WHERE t2.b <> UUID();
97 --source suite/rpl/include/rpl_mixed_clear_tables.inc
98
99 # TRUNCATE
100 --echo
101 --echo ******************** TRUNCATE ********************
102 INSERT INTO t1 VALUES(1, 't1, text 1');
103 --source suite/rpl/include/rpl_mixed_check_select.inc
104 TRUNCATE t1;
105 --source suite/rpl/include/rpl_mixed_check_select.inc
106 --source suite/rpl/include/rpl_mixed_clear_tables.inc
107
108 # UPDATE
109 --echo
110 --echo ******************** UPDATE ********************
111 INSERT INTO t1 VALUES(1, 't1, text 1');
112 INSERT INTO t2 VALUES(1, 't2, text 1');
113 UPDATE t1 SET b = 't1, text 1 updated' WHERE a = 1;
114 --source suite/rpl/include/rpl_mixed_check_select.inc
115 UPDATE t1, t2 SET t1.b = 'test', t2.b = 'test';
116 --source suite/rpl/include/rpl_mixed_check_select.inc
117 --source suite/rpl/include/rpl_mixed_clear_tables.inc
118
119 # DESCRIBE
120 --echo
121 --echo ******************** DESCRIBE ********************
122 DESCRIBE t1;
123 DESCRIBE t2 b;
124
125 # USE
126 --echo
127 --echo ******************** USE ********************
128 USE test_rpl;
129
130 # TRANSACTION
131 --echo
132 --echo ******************** TRANSACTION ********************
133 START TRANSACTION;
134 INSERT INTO t1 VALUES (1, 'start');
135 COMMIT;
136 --source suite/rpl/include/rpl_mixed_check_select.inc
137 START TRANSACTION;
138 INSERT INTO t1 VALUES (2, 'rollback');
139 ROLLBACK;
140 --source suite/rpl/include/rpl_mixed_check_select.inc
141 START TRANSACTION;
142 INSERT INTO t1 VALUES (3, 'before savepoint s1');
143 SAVEPOINT s1;
144 INSERT INTO t1 VALUES (4, 'after savepoint s1');
145 ROLLBACK TO SAVEPOINT s1;
146 --source suite/rpl/include/rpl_mixed_check_select.inc
147 START TRANSACTION;
148 INSERT INTO t1 VALUES (5, 'before savepoint s2');
149 SAVEPOINT s2;
150 INSERT INTO t1 VALUES (6, 'after savepoint s2');
151 INSERT INTO t1 VALUES (7, CONCAT('with UUID() ',UUID()));
152 RELEASE SAVEPOINT s2;
153 COMMIT;
154 DELETE FROM t1 WHERE a = 7;
155 --source suite/rpl/include/rpl_mixed_check_select.inc
156 --source suite/rpl/include/rpl_mixed_clear_tables.inc
157
158 # LOCK TABLES
159 --echo
160 --echo ******************** LOCK TABLES ********************
161 LOCK TABLES t1 READ , t2 READ;
162 UNLOCK TABLES;
163
164 # TRANSACTION ISOLATION LEVEL
165 --echo
166 --echo ******************** TRANSACTION ISOLATION LEVEL ********************
167 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
168 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
169 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
170 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
171
172 # XA
173 # skipped
174
175 # CREATE USER
176 --echo
177 --echo ******************** CREATE USER ********************
178 CREATE USER 'user_test_rpl'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*1111111111111111111111111111111111111111';
179 --source suite/rpl/include/rpl_mixed_check_user.inc
180
181 # GRANT
182 --echo
183 --echo ******************** GRANT ********************
184 GRANT SELECT ON *.* TO 'user_test_rpl'@'localhost';
185 --source suite/rpl/include/rpl_mixed_check_user.inc
186
187 # REVOKE
188 --echo
189 --echo ******************** REVOKE ********************
190 REVOKE SELECT ON *.* FROM 'user_test_rpl'@'localhost';
191 --source suite/rpl/include/rpl_mixed_check_user.inc
192
193 # SET PASSWORD
194 --echo
195 --echo ******************** SET PASSWORD ********************
196 SET PASSWORD FOR 'user_test_rpl'@'localhost' = '*0000000000000000000000000000000000000000';
197 --source suite/rpl/include/rpl_mixed_check_user.inc
198
199 # RENAME USER
200 --echo
201 --echo ******************** RENAME USER ********************
202 RENAME USER 'user_test_rpl'@'localhost' TO 'user_test_rpl_2'@'localhost';
203 --source suite/rpl/include/rpl_mixed_check_user.inc
204
205 # DROP USER
206 --echo
207 --echo ******************** DROP USER ********************
208 DROP USER 'user_test_rpl_2'@'localhost';
209 --source suite/rpl/include/rpl_mixed_check_user.inc
210
211 # Prepring for some following operations
212 INSERT INTO t1 VALUES(100, 'test');
213
214 # ANALYZE
215 --echo
216 --echo ******************** ANALYZE ********************
217 ANALYZE TABLE t1;
218
219 # BACKUP TABLE
220 # skipped because deprecated
221
222 # CHECK TABLE
223 --echo
224 --echo ******************** CHECK TABLE ********************
225 CHECK TABLE t1;
226
227 # CHECKSUM TABLE
228 --echo
229 --echo ******************** CHECKSUM TABLE ********************
230 CHECKSUM TABLE t1;
231
232 # OPTIMIZE TABLE
233 --echo
234 --echo ******************** OPTIMIZE TABLE ********************
235 OPTIMIZE TABLE t1;
236
237 # REPAIR TABLE
238 --echo
239 --echo ******************** REPAIR TABLE ********************
240 REPAIR TABLE t1;
241
242 # SET VARIABLE
243 --echo
244 --echo ******************** SET VARIABLE ********************
245 SET @test_rpl_var = 1;
246 SHOW VARIABLES LIKE 'test_rpl_var';
247
248 # SHOW
249 --echo
250 --echo ******************** SHOW ********************
251 --source suite/rpl/include/rpl_mixed_check_db.inc
252
253
254 # PROCEDURE
255 --echo
256 --echo ******************** PROCEDURE ********************
257 DELIMITER |;
258 CREATE PROCEDURE p1 ()
259 BEGIN
260 UPDATE t1 SET b = 'test' WHERE a = 201;
261 END|
262 CREATE PROCEDURE p2 ()
263 BEGIN
264 UPDATE t1 SET b = UUID() WHERE a = 202;
265 END|
266 DELIMITER ;|
267 INSERT INTO t1 VALUES(201, 'test 201');
268 CALL p1();
269 INSERT INTO t1 VALUES(202, 'test 202');
270 CALL p2();
271 DELETE FROM t1 WHERE a = 202;
272 --source suite/rpl/include/rpl_mixed_check_select.inc
273 ALTER PROCEDURE p1 COMMENT 'p1';
274 DROP PROCEDURE p1;
275 DROP PROCEDURE p2;
276 --source suite/rpl/include/rpl_mixed_clear_tables.inc
277
278 # TRIGGER
279 --echo
280 --echo ******************** TRIGGER ********************
281 DELIMITER |;
282 CREATE TRIGGER tr1 BEFORE INSERT ON t1
283 FOR EACH ROW BEGIN
284 INSERT INTO t2 SET a = NEW.a, b = NEW.b;
285 END|
286 DELIMITER ;|
287 INSERT INTO t1 VALUES (1, 'test');
288 --source suite/rpl/include/rpl_mixed_check_select.inc
289 --source suite/rpl/include/rpl_mixed_clear_tables.inc
290 DROP TRIGGER tr1;
291
292 # EVENTS
293 --echo
294 --echo
295 --echo ******************** EVENTS ********************
296 GRANT EVENT ON *.* TO 'root'@'localhost';
297 INSERT INTO t1 VALUES(1, 'test1');
298 CREATE EVENT e1 ON SCHEDULE EVERY '1' SECOND COMMENT 'e_second_comment' DO DELETE FROM t1;
299 --source suite/rpl/include/rpl_mixed_check_event.inc
300 --source suite/rpl/include/rpl_mixed_check_select.inc
301 --sleep 2
302 --source suite/rpl/include/rpl_mixed_check_select.inc
303 ALTER EVENT e1 RENAME TO e2;
304 --sleep 2
305 --source suite/rpl/include/rpl_mixed_check_event.inc
306 --source suite/rpl/include/rpl_mixed_check_select.inc
307 DROP EVENT e2;
308 --source suite/rpl/include/rpl_mixed_check_event.inc
309 --source suite/rpl/include/rpl_mixed_clear_tables.inc
310
311 # VIEWS
312 --echo
313 --echo ******************** VIEWS ********************
314 INSERT INTO t1 VALUES(1, 'test1');
315 INSERT INTO t1 VALUES(2, 'test2');
316 CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1;
317 CREATE VIEW v2 AS SELECT * FROM t1 WHERE b <> UUID();
318 --source suite/rpl/include/rpl_mixed_check_view.inc
319 ALTER VIEW v1 AS SELECT * FROM t1 WHERE a = 2;
320 --source suite/rpl/include/rpl_mixed_check_view.inc
321 DROP VIEW v1;
322 DROP VIEW v2;
323 --source suite/rpl/include/rpl_mixed_clear_tables.inc
324
325 # BINLOG EVENTS
326 --echo
327 --echo
328 --echo ******************** SHOW BINLOG EVENTS ********************
329 --let $mask_grant_as_events=1
330 --source include/show_binlog_events.inc
331 sync_slave_with_master;
332 # as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID
333 --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test_rpl > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
334 --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test_rpl > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql
335
336 connection master;
337 drop database test_rpl;
338 sync_slave_with_master;
339
340 # Let's compare. Note: If they match test will pass, if they do not match
341 # the test will show that the diff statement failed and not reject file
342 # will be created. You will need to go to the mysql-test dir and diff
343 # the files your self to see what is not matching
344
345 --diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql
346 # Cleanup
347 --remove_file $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
348 --remove_file $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql
349 --source include/rpl_end.inc