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