1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
include/group_replication.inc [rpl_server_count=3]
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection server1]
############################################################
# 01. Create two tables on the group.
[connection server1]
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY);
include/rpl_sync.inc
############################################################
# 02. Lock table t1 on server2 to block a future update.
[connection server_2]
LOCK table t1 READ;
############################################################
# 03. Execute transaction T1, the transaction will block on
# server2 due to the table lock.
[connection server1]
BEGIN;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (100);
COMMIT;
############################################################
# 04. Wait until T1 is delivered to server2.
[connection server2]
############################################################
# 05. Execute a read transaction on server2 with
# group_replication_consistency= 'BEFORE' , it will wait
# for T1 commit.
[connection server_2_1]
SET @@SESSION.group_replication_consistency= 'BEFORE';
SELECT COUNT(*) AS count FROM t2 WHERE c1 >= 99;
[connection server2]
############################################################
# 06. Execute a write transaction on server2 with
# group_replication_consistency= 'BEFORE' , it will wait
# for T1 commit.
[connection slave]
SET @@SESSION.group_replication_consistency= 'BEFORE';
UPDATE t2 SET c1=99 WHERE c1=100;
[connection server2]
############################################################
# 07. Execute transaction T2 on server1, it will execute
# on servers 1 and 3.
# server2 will apply it in order not because of the
# consistency but due to --replica_preserve_commit_order
# or single thread applier which will make T2 be
# committed after the UPDATE.
[connection server1]
INSERT INTO t2 VALUES (2);
include/assert.inc ['There is a 2 in table t2']
[connection server3]
############################################################
# 08. Unblock T1 on server2.
[connection server_2]
UNLOCK TABLES;
[connection server_2_1]
count
1
SET @@SESSION.group_replication_consistency= DEFAULT;
[connection slave]
SET @@SESSION.group_replication_consistency= DEFAULT;
############################################################
# 09. Validate data.
[connection server2]
include/assert.inc ['There is a 1 in table t1']
include/assert.inc ['There is a 99 in table t2']
include/rpl_sync.inc
[connection server2]
include/assert.inc ['There is a 2 in table t2']
[connection server1]
include/assert.inc ['There is a 1 in table t1']
include/assert.inc ['There is a 99 in table t2']
include/assert.inc ['There is a 2 in table t2']
[connection server3]
include/assert.inc ['There is a 1 in table t1']
include/assert.inc ['There is a 99 in table t2']
include/assert.inc ['There is a 2 in table t2']
include/diff_tables.inc [server1:t1, server2:t1, server3:t1]
include/diff_tables.inc [server1:t2, server2:t2, server3:t2]
############################################################
# 10. Clean up.
[connection server1]
DROP TABLE t1;
DROP TABLE t2;
include/group_replication_end.inc
|
