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
10connection master;
11--source suite/rpl/include/rpl_mixed_show_binlog_format.inc
12connection slave;
13--source suite/rpl/include/rpl_mixed_show_binlog_format.inc
14connection master;
15
16
17CREATE DATABASE test_rpl;
18
19--echo
20--echo ******************** PREPARE TESTING ********************
21USE test_rpl;
22eval CREATE TABLE t1 (a int auto_increment not null, b char(254), PRIMARY KEY(a)) ENGINE=$engine_type;
23eval CREATE TABLE t2 (a int auto_increment not null, b char(254), PRIMARY KEY(a)) ENGINE=$engine_type;
24
25# DELETE
26INSERT INTO t1 VALUES(1, 't1, text 1');
27INSERT INTO t1 VALUES(2, 't1, text 2');
28INSERT INTO t2 VALUES(1, 't2, text 1');
29--echo
30--echo ******************** DELETE ********************
31DELETE FROM t1 WHERE a = 1;
32DELETE FROM t2 WHERE b <> UUID();
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 ********************
39INSERT INTO t1 VALUES(1, 't1, text 1');
40INSERT INTO t1 VALUES(2, UUID());
41INSERT INTO t2 SELECT * FROM t1;
42INSERT INTO t2 VALUES (1, 't1, text 1') ON DUPLICATE KEY UPDATE b = 't2, text 1';
43DELETE FROM t1 WHERE a = 2;
44DELETE 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
51eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/std_data/rpl_mixed.dat' INTO TABLE t1 FIELDS TERMINATED BY '|' ;
52SELECT * 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 ********************
59INSERT INTO t1 VALUES(1, 't1, text 1');
60INSERT INTO t1 VALUES(2, 't1, text 2');
61INSERT INTO t1 VALUES(3, 't1, text 3');
62REPLACE INTO t1 VALUES(1, 't1, text 11');
63REPLACE INTO t1 VALUES(2, UUID());
64REPLACE INTO t1 SET a=3, b='t1, text 33';
65DELETE 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 ********************
72INSERT INTO t1 VALUES(1, 't1, text 1');
73SELECT * FROM t1 WHERE b <> UUID() ORDER BY a;
74--source suite/rpl/include/rpl_mixed_clear_tables.inc
75
76# JOIN
77--echo
78--echo ******************** JOIN ********************
79INSERT INTO t1 VALUES(1, 'CCC');
80INSERT INTO t1 VALUES(2, 'DDD');
81INSERT INTO t2 VALUES(1, 'DDD');
82INSERT INTO t2 VALUES(2, 'CCC');
83SELECT * FROM t1 LEFT JOIN  t2 ON t1.a = t2.a ORDER BY t1.a,t2.a;
84SELECT * 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 ********************
90INSERT INTO t1 VALUES(1, 't1, text 1');
91INSERT INTO t2 VALUES(1, 't2, text 1');
92SELECT * FROM t1 UNION SELECT * FROM t2 WHERE t2.b <> UUID();
93--source suite/rpl/include/rpl_mixed_clear_tables.inc
94
95# TRUNCATE
96--echo
97--echo ******************** TRUNCATE ********************
98INSERT INTO t1 VALUES(1, 't1, text 1');
99--source suite/rpl/include/rpl_mixed_check_select.inc
100TRUNCATE 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 ********************
107INSERT INTO t1 VALUES(1, 't1, text 1');
108INSERT INTO t2 VALUES(1, 't2, text 1');
109UPDATE t1 SET b = 't1, text 1 updated' WHERE a = 1;
110--source suite/rpl/include/rpl_mixed_check_select.inc
111UPDATE 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 ********************
118DESCRIBE t1;
119DESCRIBE t2 b;
120
121# USE
122--echo
123--echo ******************** USE ********************
124USE test_rpl;
125
126# TRANSACTION
127--echo
128--echo ******************** TRANSACTION ********************
129START TRANSACTION;
130INSERT INTO t1 VALUES (1, 'start');
131COMMIT;
132--source suite/rpl/include/rpl_mixed_check_select.inc
133START TRANSACTION;
134INSERT INTO t1 VALUES (2, 'rollback');
135ROLLBACK;
136--source suite/rpl/include/rpl_mixed_check_select.inc
137START TRANSACTION;
138INSERT INTO t1 VALUES (3, 'before savepoint s1');
139SAVEPOINT s1;
140INSERT INTO t1 VALUES (4, 'after savepoint s1');
141ROLLBACK TO SAVEPOINT s1;
142--source suite/rpl/include/rpl_mixed_check_select.inc
143START TRANSACTION;
144INSERT INTO t1 VALUES (5, 'before savepoint s2');
145SAVEPOINT s2;
146INSERT INTO t1 VALUES (6, 'after savepoint s2');
147INSERT INTO t1 VALUES (7, CONCAT('with UUID() ',UUID()));
148RELEASE SAVEPOINT s2;
149COMMIT;
150DELETE 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 ********************
157LOCK TABLES t1 READ , t2 READ;
158UNLOCK TABLES;
159
160# TRANSACTION ISOLATION LEVEL
161--echo
162--echo ******************** TRANSACTION ISOLATION LEVEL ********************
163SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
164SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
165SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
166SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
167
168# XA
169# skipped
170
171# CREATE USER
172--echo
173--echo ******************** CREATE USER ********************
174CREATE 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 ********************
180GRANT 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 ********************
186REVOKE 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 ********************
192SET 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 ********************
198RENAME 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 ********************
204DROP USER 'user_test_rpl_2'@'localhost';
205--source suite/rpl/include/rpl_mixed_check_user.inc
206
207# Prepring for some following operations
208INSERT INTO t1 VALUES(100, 'test');
209
210# ANALYZE
211--echo
212--echo ******************** ANALYZE ********************
213ANALYZE TABLE t1;
214
215# BACKUP TABLE
216# skipped because deprecated
217
218# CHECK TABLE
219--echo
220--echo ******************** CHECK TABLE ********************
221CHECK TABLE t1;
222
223# CHECKSUM TABLE
224--echo
225--echo ******************** CHECKSUM TABLE ********************
226CHECKSUM TABLE t1;
227
228# OPTIMIZE TABLE
229--echo
230--echo ******************** OPTIMIZE TABLE ********************
231OPTIMIZE TABLE t1;
232
233# REPAIR TABLE
234--echo
235--echo ******************** REPAIR TABLE ********************
236REPAIR TABLE t1;
237
238# SET VARIABLE
239--echo
240--echo ******************** SET VARIABLE ********************
241SET @test_rpl_var = 1;
242SHOW 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 ********************
253DELIMITER |;
254CREATE PROCEDURE p1 ()
255BEGIN
256  UPDATE t1 SET b = 'test' WHERE a = 201;
257END|
258CREATE PROCEDURE p2 ()
259BEGIN
260  UPDATE t1 SET b = UUID() WHERE a = 202;
261END|
262DELIMITER ;|
263INSERT  INTO t1 VALUES(201, 'test 201');
264CALL p1();
265INSERT  INTO t1 VALUES(202, 'test 202');
266CALL p2();
267DELETE FROM t1 WHERE a = 202;
268--source suite/rpl/include/rpl_mixed_check_select.inc
269ALTER PROCEDURE p1 COMMENT 'p1';
270DROP PROCEDURE p1;
271DROP PROCEDURE p2;
272--source suite/rpl/include/rpl_mixed_clear_tables.inc
273
274# TRIGGER
275--echo
276--echo ******************** TRIGGER ********************
277DELIMITER |;
278CREATE TRIGGER tr1 BEFORE INSERT ON t1
279FOR EACH ROW BEGIN
280  INSERT INTO t2 SET a = NEW.a, b = NEW.b;
281END|
282DELIMITER ;|
283INSERT 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
286DROP TRIGGER tr1;
287
288# EVENTS
289--echo
290--echo
291--echo ******************** EVENTS ********************
292INSERT INTO t1 VALUES(1, 'test1');
293CREATE 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
298ALTER 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
302DROP 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 ********************
309INSERT INTO t1 VALUES(1, 'test1');
310INSERT INTO t1 VALUES(2, 'test2');
311CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1;
312CREATE VIEW v2 AS SELECT * FROM t1 WHERE b <> UUID();
313--source suite/rpl/include/rpl_mixed_check_view.inc
314ALTER VIEW v1 AS SELECT * FROM t1 WHERE a = 2;
315--source suite/rpl/include/rpl_mixed_check_view.inc
316DROP VIEW v1;
317DROP 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
325sync_slave_with_master;
326# as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID
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
330connection master;
331drop database test_rpl;
332sync_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
341