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