| 1 |
|
| 2 |
############################################################ |
| 3 |
# 1. Setup a new group with three members. |
| 4 |
# |
| 5 |
include/group_replication.inc [rpl_server_count=3] |
| 6 |
Warnings: |
| 7 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 8 |
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. |
| 9 |
[connection server1] |
| 10 |
|
| 11 |
# Create four connections to server 1 so that transactions executed |
| 12 |
# on server 1 can be parallel, what would not happen if we only use |
| 13 |
# one client. |
| 14 |
# |
| 15 |
|
| 16 |
# Suppress auto increment warnings, we want the same configuration |
| 17 |
# on all servers. |
| 18 |
# |
| 19 |
[connection server1] |
| 20 |
SET SESSION sql_log_bin= 0; |
| 21 |
call mtr.add_suppression("Plugin group_replication reported: 'Group contains 3 members which is greater than group_replication_auto_increment_increment value of 1. This can lead to a higher transactional abort rate."); |
| 22 |
SET SESSION sql_log_bin= 1; |
| 23 |
[connection server2] |
| 24 |
SET SESSION sql_log_bin= 0; |
| 25 |
call mtr.add_suppression("Plugin group_replication reported: 'Group contains 2 members which is greater than group_replication_auto_increment_increment value of 1. This can lead to a higher transactional abort rate."); |
| 26 |
SET SESSION sql_log_bin= 1; |
| 27 |
|
| 28 |
# Create a table on all members. |
| 29 |
# |
| 30 |
[connection server1] |
| 31 |
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; |
| 32 |
include/rpl_sync.inc |
| 33 |
|
| 34 |
# Force a binary log rotate on remote servers: 2 and 3. |
| 35 |
# |
| 36 |
[connection server2] |
| 37 |
FLUSH BINARY LOGS; |
| 38 |
[connection server3] |
| 39 |
FLUSH BINARY LOGS; |
| 40 |
|
| 41 |
############################################################ |
| 42 |
# 2. Commit 4 groups of 4 transactions, we have 4 configured |
| 43 |
# parallel workers. |
| 44 |
# All these transactions will be able to execute in parallel. |
| 45 |
# |
| 46 |
[connection server2] |
| 47 |
LOCK TABLE t1 WRITE; |
| 48 |
[connection server3] |
| 49 |
LOCK TABLE t1 WRITE; |
| 50 |
[connection server1_1] |
| 51 |
INSERT INTO t1 VALUES (1); |
| 52 |
[connection server1_2] |
| 53 |
INSERT INTO t1 VALUES (2); |
| 54 |
[connection server1_3] |
| 55 |
INSERT INTO t1 VALUES (3); |
| 56 |
[connection server1_4] |
| 57 |
INSERT INTO t1 VALUES (4); |
| 58 |
[connection server2] |
| 59 |
UNLOCK TABLES; |
| 60 |
[connection server3] |
| 61 |
UNLOCK TABLES; |
| 62 |
[connection server2] |
| 63 |
LOCK TABLE t1 WRITE; |
| 64 |
[connection server3] |
| 65 |
LOCK TABLE t1 WRITE; |
| 66 |
[connection server1_1] |
| 67 |
INSERT INTO t1 VALUES (5); |
| 68 |
[connection server1_2] |
| 69 |
INSERT INTO t1 VALUES (6); |
| 70 |
[connection server1_3] |
| 71 |
INSERT INTO t1 VALUES (7); |
| 72 |
[connection server1_4] |
| 73 |
INSERT INTO t1 VALUES (8); |
| 74 |
[connection server2] |
| 75 |
UNLOCK TABLES; |
| 76 |
[connection server3] |
| 77 |
UNLOCK TABLES; |
| 78 |
[connection server2] |
| 79 |
LOCK TABLE t1 WRITE; |
| 80 |
[connection server3] |
| 81 |
LOCK TABLE t1 WRITE; |
| 82 |
[connection server1_1] |
| 83 |
INSERT INTO t1 VALUES (9); |
| 84 |
[connection server1_2] |
| 85 |
INSERT INTO t1 VALUES (10); |
| 86 |
[connection server1_3] |
| 87 |
INSERT INTO t1 VALUES (11); |
| 88 |
[connection server1_4] |
| 89 |
INSERT INTO t1 VALUES (12); |
| 90 |
[connection server2] |
| 91 |
UNLOCK TABLES; |
| 92 |
[connection server3] |
| 93 |
UNLOCK TABLES; |
| 94 |
[connection server2] |
| 95 |
LOCK TABLE t1 WRITE; |
| 96 |
[connection server3] |
| 97 |
LOCK TABLE t1 WRITE; |
| 98 |
[connection server1_1] |
| 99 |
INSERT INTO t1 VALUES (13); |
| 100 |
[connection server1_2] |
| 101 |
INSERT INTO t1 VALUES (14); |
| 102 |
[connection server1_3] |
| 103 |
INSERT INTO t1 VALUES (15); |
| 104 |
[connection server1_4] |
| 105 |
INSERT INTO t1 VALUES (16); |
| 106 |
[connection server2] |
| 107 |
UNLOCK TABLES; |
| 108 |
[connection server3] |
| 109 |
UNLOCK TABLES; |
| 110 |
|
| 111 |
# Wait for group synchronization. |
| 112 |
# |
| 113 |
[connection server1] |
| 114 |
include/rpl_sync.inc |
| 115 |
|
| 116 |
############################################################ |
| 117 |
# 3. Verify that above transactions are committed on server |
| 118 |
# 2 and 3 on the same order they were on server 1. |
| 119 |
# |
| 120 |
|
| 121 |
# include/gr_mts_indexes_commit_order_validation.inc |
| 122 |
# Parameters: server_number: 2 |
| 123 |
[connection server2] |
| 124 |
|
| 125 |
# Assert parallel indexes, the 4 groups of transactions |
| 126 |
# must be parallel. |
| 127 |
include/include/assert_logical_timestamps.inc [0 0;1 2;2 3;2 4;2 5;2 6;2 7;2 8;2 9;2 10;2 11;2 12;2 13;2 14;2 15;2 16;2 17;2 18] |
| 128 |
include/stop_group_replication.inc |
| 129 |
|
| 130 |
# Binary log will be reapplied one by one later through mysql. The |
| 131 |
# table is recreated with an extra auto increment field. The new |
| 132 |
# field 'id' will be filled by server automatically when reapplying |
| 133 |
# binary log. So it exactly records the binlogging order of the |
| 134 |
# transactions. |
| 135 |
# |
| 136 |
DROP TABLE t1; |
| 137 |
CREATE TABLE t1 (a INT, id INT AUTO_INCREMENT KEY) ENGINE = InnoDB; |
| 138 |
|
| 139 |
# Clear GTID_EXECUTED to avoid that transactions reapplied |
| 140 |
# from binary log are skipped. |
| 141 |
# |
| 142 |
RESET MASTER; |
| 143 |
|
| 144 |
# Replay binary log. |
| 145 |
# |
| 146 |
|
| 147 |
# The transactions should be binlogged in the same order as |
| 148 |
# they are binlogged on server 1. |
| 149 |
# Because the transactions: |
| 150 |
# * insert numbers starting from 1; |
| 151 |
# * id starts from 1; |
| 152 |
# * both are increased by 1 for each transaction; |
| 153 |
# the table's data should look like: |
| 154 |
# id a |
| 155 |
# 1 1 |
| 156 |
# 2 2 |
| 157 |
# ... ... |
| 158 |
# n n |
| 159 |
# It means the values of field 'id' are always same to the |
| 160 |
# values of field 'a'. |
| 161 |
SELECT * FROM t1 WHERE id <> a; |
| 162 |
a id |
| 163 |
include/assert.inc ['There are no mismatched rows'] |
| 164 |
include/assert.inc ['There are no row which is null'] |
| 165 |
include/assert.inc ['There are 16 rows'] |
| 166 |
DROP TABLE t1; |
| 167 |
|
| 168 |
# include/gr_mts_indexes_commit_order_validation.inc |
| 169 |
# Parameters: server_number: 3 |
| 170 |
[connection server3] |
| 171 |
|
| 172 |
# Assert parallel indexes, the 4 groups of transactions |
| 173 |
# must be parallel. |
| 174 |
include/include/assert_logical_timestamps.inc [0 0;0 0;1 2;2 3;2 4;2 5;2 6;2 7;2 8;2 9;2 10;2 11;2 12;2 13;2 14;2 15;2 16;2 17;2 18] |
| 175 |
include/stop_group_replication.inc |
| 176 |
|
| 177 |
# Binary log will be reapplied one by one later through mysql. The |
| 178 |
# table is recreated with an extra auto increment field. The new |
| 179 |
# field 'id' will be filled by server automatically when reapplying |
| 180 |
# binary log. So it exactly records the binlogging order of the |
| 181 |
# transactions. |
| 182 |
# |
| 183 |
DROP TABLE t1; |
| 184 |
CREATE TABLE t1 (a INT, id INT AUTO_INCREMENT KEY) ENGINE = InnoDB; |
| 185 |
|
| 186 |
# Clear GTID_EXECUTED to avoid that transactions reapplied |
| 187 |
# from binary log are skipped. |
| 188 |
# |
| 189 |
RESET MASTER; |
| 190 |
|
| 191 |
# Replay binary log. |
| 192 |
# |
| 193 |
|
| 194 |
# The transactions should be binlogged in the same order as |
| 195 |
# they are binlogged on server 1. |
| 196 |
# Because the transactions: |
| 197 |
# * insert numbers starting from 1; |
| 198 |
# * id starts from 1; |
| 199 |
# * both are increased by 1 for each transaction; |
| 200 |
# the table's data should look like: |
| 201 |
# id a |
| 202 |
# 1 1 |
| 203 |
# 2 2 |
| 204 |
# ... ... |
| 205 |
# n n |
| 206 |
# It means the values of field 'id' are always same to the |
| 207 |
# values of field 'a'. |
| 208 |
SELECT * FROM t1 WHERE id <> a; |
| 209 |
a id |
| 210 |
include/assert.inc ['There are no mismatched rows'] |
| 211 |
include/assert.inc ['There are no row which is null'] |
| 212 |
include/assert.inc ['There are 16 rows'] |
| 213 |
DROP TABLE t1; |
| 214 |
|
| 215 |
############################################################ |
| 216 |
# 4. Clean up. |
| 217 |
# |
| 218 |
[connection server1] |
| 219 |
DROP TABLE t1; |
| 220 |
include/group_replication_end.inc |