1
#########################################
2
# Author: Serge Kozlov skozlov@mysql.com
4
# Purpose: testing the replication in mixed mode
5
# Requirements: define binlog format for mysqld as in example below:
6
# ./mysql-test-run.pl --mysqld=--binlog-format=mixed
7
#########################################
9
--source include/master-slave.inc
11
# Check MIXED on both master and slave
13
--echo ==========MASTER==========
14
--source suite/rpl/include/rpl_mixed_show_binlog_format.inc
16
--echo ==========SLAVE===========
17
--source suite/rpl/include/rpl_mixed_show_binlog_format.inc
21
CREATE DATABASE test_rpl;
24
--echo ******************** PREPARE TESTING ********************
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;
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');
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
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
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
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
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
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
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
101
--echo ******************** TRUNCATE ********************
102
INSERT INTO t1 VALUES(1, 't1, text 1');
103
--source suite/rpl/include/rpl_mixed_check_select.inc
105
--source suite/rpl/include/rpl_mixed_check_select.inc
106
--source suite/rpl/include/rpl_mixed_clear_tables.inc
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
121
--echo ******************** DESCRIBE ********************
127
--echo ******************** USE ********************
132
--echo ******************** TRANSACTION ********************
134
INSERT INTO t1 VALUES (1, 'start');
136
--source suite/rpl/include/rpl_mixed_check_select.inc
138
INSERT INTO t1 VALUES (2, 'rollback');
140
--source suite/rpl/include/rpl_mixed_check_select.inc
142
INSERT INTO t1 VALUES (3, 'before 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
148
INSERT INTO t1 VALUES (5, 'before 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;
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
160
--echo ******************** LOCK TABLES ********************
161
LOCK TABLES t1 READ , t2 READ;
164
# TRANSACTION ISOLATION LEVEL
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;
177
--echo ******************** CREATE USER ********************
178
CREATE USER 'user_test_rpl'@'localhost' IDENTIFIED BY PASSWORD '*1111111111111111111111111111111111111111';
179
--source suite/rpl/include/rpl_mixed_check_user.inc
183
--echo ******************** GRANT ********************
184
GRANT SELECT ON *.* TO 'user_test_rpl'@'localhost';
185
--source suite/rpl/include/rpl_mixed_check_user.inc
189
--echo ******************** REVOKE ********************
190
REVOKE SELECT ON *.* FROM 'user_test_rpl'@'localhost';
191
--source suite/rpl/include/rpl_mixed_check_user.inc
195
--echo ******************** SET PASSWORD ********************
196
SET PASSWORD FOR 'user_test_rpl'@'localhost' = '*0000000000000000000000000000000000000000';
197
--source suite/rpl/include/rpl_mixed_check_user.inc
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
207
--echo ******************** DROP USER ********************
208
DROP USER 'user_test_rpl_2'@'localhost';
209
--source suite/rpl/include/rpl_mixed_check_user.inc
211
# Prepring for some following operations
212
INSERT INTO t1 VALUES(100, 'test');
216
--echo ******************** ANALYZE ********************
220
# skipped because deprecated
224
--echo ******************** CHECK TABLE ********************
229
--echo ******************** CHECKSUM TABLE ********************
234
--echo ******************** OPTIMIZE TABLE ********************
239
--echo ******************** REPAIR TABLE ********************
244
--echo ******************** SET VARIABLE ********************
245
SET @test_rpl_var = 1;
246
SHOW VARIABLES LIKE 'test_rpl_var';
250
--echo ******************** SHOW ********************
251
--source suite/rpl/include/rpl_mixed_check_db.inc
256
--echo ******************** PROCEDURE ********************
258
CREATE PROCEDURE p1 ()
260
UPDATE t1 SET b = 'test' WHERE a = 201;
262
CREATE PROCEDURE p2 ()
264
UPDATE t1 SET b = UUID() WHERE a = 202;
267
INSERT INTO t1 VALUES(201, 'test 201');
269
INSERT INTO t1 VALUES(202, 'test 202');
271
DELETE FROM t1 WHERE a = 202;
272
--source suite/rpl/include/rpl_mixed_check_select.inc
273
ALTER PROCEDURE p1 COMMENT 'p1';
276
--source suite/rpl/include/rpl_mixed_clear_tables.inc
280
--echo ******************** TRIGGER ********************
282
CREATE TRIGGER tr1 BEFORE INSERT ON t1
284
INSERT INTO t2 SET a = NEW.a, b = NEW.b;
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
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
302
--source suite/rpl/include/rpl_mixed_check_select.inc
303
ALTER EVENT e1 RENAME TO e2;
305
--source suite/rpl/include/rpl_mixed_check_event.inc
306
--source suite/rpl/include/rpl_mixed_check_select.inc
308
--source suite/rpl/include/rpl_mixed_check_event.inc
309
--source suite/rpl/include/rpl_mixed_clear_tables.inc
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
323
--source suite/rpl/include/rpl_mixed_clear_tables.inc
328
--echo ******************** SHOW BINLOG EVENTS ********************
329
--replace_column 2 # 5 #
330
--replace_regex /Server ver: .+/Server ver: #/ /table_id: [0-9]+/table_id: #/ /COMMIT.+xid=[0-9]+.+/#/ /file_id=[0-9]+/file_id=#/ /block_len=[0-9]+/block_len=#/
331
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
332
show binlog events from 1;
333
sync_slave_with_master;
334
# as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID
335
--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
336
--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
339
drop database test_rpl;
340
sync_slave_with_master;
342
# Let's compare. Note: If they match test will pass, if they do not match
343
# the test will show that the diff statement failed and not reject file
344
# will be created. You will need to go to the mysql-test dir and diff
345
# the files your self to see what is not matching
347
--diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql