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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
|
################################################################################
# Test case to verify that concurrent transactions with intersecting
# write set, on table with multiple primary and unique key.
#
# Scenario 8.1: on WL#6834 page
#
# T1: WS={t1.a,d=(1,2), t1.b=3, t1.c=4}
# {t1.a,d=(5,6), t1.b=3, t1.c=4}
#
# T2: WS={t1.a,d=(1,2), t1.b=3, t1.c=4}
# {t1.a,d=(1,2), t1.b=6, t1.c=4}
#
# Outcome: T1 must abort, T2 must commit.
#
# Full details on WL#6834 QA notes.
#
# Test:
# 0. The test requires two servers: M1 and M2.
# 1. With both the members ONLINE. Create a table on M1.
# 2. Set a DEBUG sync before broadcast message to group on connection M1.
# Commit a transaction that will be block before broadcast.
# 3. Wait until connection reaches DEBUG sync point.
# 4. Execute a transaction on M2, that will reach first certification,
# since M1 is blocked before broadcast.
# 5. Signal the waiting thread on M1 to resume.
# 6. It will end up in an error stating that it was aborted, since
# transactions are conflicting and M2 was ordered first.
# 7. Assert that number of certified transactions are the expected one.
#
# Checking the positive case in which there is no conflict.
# 8. Set a DEBUG sync before broadcast message to group on connection M1.
# Commit a transaction that will be block before broadcast.
# 9. Wait until connection reaches DEBUG sync point.
# 10. Execute a transaction on M2, that will reach first certification,
# since M1 is blocked before broadcast.
# 11. Signal the waiting thread on M1 to resume.
# 12. It will execute without error as the conflicting transactions have been
# removed.
# 13. Assert that number of certified transactions are the expected one.
# 14. Clean up.
################################################################################
--source include/have_debug_sync.inc
--source include/have_group_replication_plugin.inc
--source include/group_replication.inc
--echo
--echo ############################################################
--echo # 1. Create a table on server1.
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
CREATE TABLE t1 (a INT, d INT, b INT UNIQUE KEY, c INT UNIQUE KEY, PRIMARY KEY(a, d));
INSERT INTO t1 VALUE(1, 2, 3, 4);
--source include/rpl_sync.inc
--echo
--echo ############################################################
--echo # 2. Set a debug sync before broadcast message to group on
--echo # connection server_1.
--echo # Commit a transaction that will be block before broadcast.
--let $rpl_connection_name= server_1
--source include/rpl_connection.inc
SET @debug_save= @@GLOBAL.DEBUG;
SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
BEGIN;
UPDATE t1 SET a=5 AND d=6 WHERE c=4;
--send COMMIT
--echo
--echo ############################################################
--echo # 3. Wait until server_1 connection reaches the
--echo # group_replication_before_message_broadcast debug sync point.
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
--let $wait_condition=SELECT COUNT(*)=1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'debug sync point: now'
--source include/wait_condition.inc
--echo
--echo ############################################################
--echo # 4. Execute a transaction on server2, that will reach first
--echo # certification, since server_1 is blocked before broadcast.
--let $rpl_connection_name= server2
--source include/rpl_connection.inc
UPDATE t1 SET b=6 WHERE c=4;
--echo
--echo ############################################################
--echo # 5. Signal the waiting thread on server_1 to resume.
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
SET DEBUG_SYNC='now SIGNAL waiting';
SET @@GLOBAL.DEBUG= @debug_save;
--echo
--echo ############################################################
--echo # 6. It will end up in an error stating that it was aborted,
--echo # since transactions are conflicting and server2 was
--echo # ordered first.
--let $rpl_connection_name= server2
--source include/rpl_connection.inc
--let $sync_slave_connection= server1
--source include/sync_slave_sql_with_master.inc
--let $rpl_connection_name= server_1
--source include/rpl_connection.inc
--error ER_TRANSACTION_ROLLBACK_DURING_COMMIT
--reap
--echo
--echo ############################################################
--echo # 7. Assert that number of certified transactions are the
--echo # expected ones.
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
--let $certified_transactions= query_get_value(SELECT Count_Transactions_Checked from performance_schema.replication_group_member_stats where member_id in (SELECT @@server_uuid), Count_Transactions_Checked, 1)
--let $assert_text= The value of Count_Transactions_Checked should be 4 after starting group replication
--let $assert_cond= "$certified_transactions" = 4
--source include/assert.inc
--let $negatively_certified= query_get_value(SELECT Count_Conflicts_Detected from performance_schema.replication_group_member_stats where member_id in (SELECT @@server_uuid), Count_Conflicts_Detected, 1)
--let $assert_text= The value of Count_Conflicts_Detected should be 1 after starting group replication
--let $assert_cond= "$negatively_certified" = 1
--source include/assert.inc
--echo
--echo ############################################################
--echo # Checking the positive case in which there is no conflict.
--echo ############################################################
--echo
--echo ############################################################
--echo # 2. Set a debug sync before broadcast message to group on
--echo # connection server_1.
--echo # Commit a transaction that will be block before broadcast.
--let $rpl_connection_name= server_1
--source include/rpl_connection.inc
SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
BEGIN;
INSERT INTO t1 VALUE(10, 11, 12, 13);
--send COMMIT
--echo
--echo ############################################################
--echo # 3. Wait until server_1 connection reaches the
--echo # group_replication_before_message_broadcast debug sync point.
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
--let $wait_condition=SELECT COUNT(*)=1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'debug sync point: now'
--source include/wait_condition.inc
--echo
--echo ############################################################
--echo # 4. Execute a transaction on server2, that will reach first
--echo # certification, since server_1 is blocked before broadcast.
--let $rpl_connection_name= server2
--source include/rpl_connection.inc
INSERT INTO t1 VALUE(14, 15, 16, 17);
--let $sync_slave_connection= server1
--source include/sync_slave_sql_with_master.inc
--echo
--echo ############################################################
--echo # 5. Signal the waiting thread on server_1 to resume.
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
SET DEBUG_SYNC='now SIGNAL waiting';
SET @@GLOBAL.DEBUG= @debug_save;
--echo
--echo ########################################################################
--echo # 5. It will execute without error as the conflicting transactions have
--echo # been removed.
--let $rpl_connection_name= server_1
--source include/rpl_connection.inc
--reap
--source include/rpl_sync.inc
--echo
--echo ############################################################
--echo # 6. Assert that number of certified transactions are the
--echo # expected ones.
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
--let $assert_text= Table t1 will contain row after the above execution
--let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.a = 1 AND t1.d = 2 AND t1.b = 6 AND t1.c = 4, count, 1]" = "1"
--source include/assert.inc
--let $assert_text= Table t1 will contain row after the above execution
--let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.a = 10 AND t1.d = 11 AND t1.b = 12 AND t1.c = 13, count, 1]" = "1"
--source include/assert.inc
--let $assert_text= Table t1 will contain row after the above execution
--let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.a = 14 AND t1.d = 15 AND t1.b = 16 AND t1.c = 17, count, 1]" = "1"
--source include/assert.inc
--let $assert_text= Table t1 will contain 3 rows after the above execution
--let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "3"
--source include/assert.inc
--let $certified_transactions= query_get_value(SELECT Count_Transactions_Checked from performance_schema.replication_group_member_stats where member_id in (SELECT @@server_uuid), Count_Transactions_Checked, 1)
--let $assert_text= The value of Count_Transactions_Checked should be 6 after starting group replication
--let $assert_cond= "$certified_transactions" = 6
--source include/assert.inc
--let $negatively_certified= query_get_value(SELECT Count_Conflicts_Detected from performance_schema.replication_group_member_stats where member_id in (SELECT @@server_uuid), Count_Conflicts_Detected, 1)
--let $assert_text= The value of Count_Conflicts_Detected should be 1 after starting group replication
--let $assert_cond= "$negatively_certified" = 1
--source include/assert.inc
--echo
--echo ############################################################
--echo # Clean up.
DROP TABLE t1;
--source include/group_replication_end.inc
|
