Debian Codegraph @

mysql-test/suite/group_replication/r/gr_foreign_key_on_unique_key.result

raw
1 include/group_replication.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 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.
5 [connection server1]
6
7 ############################################################
8 # Create two tables on server1.
9 # t1 : parent table with a primary key and unique key.
10 # t2 : child table with a primary key and foreign key referring
11 # t1's unique key.
12
13 [connection server1]
14 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT UNIQUE KEY NOT NULL);
15 CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t1(c2));
16 INSERT INTO t1 VALUES(1, 1);
17 INSERT INTO t1 VALUES(2, 2);
18 include/rpl_sync.inc
19
20 ############################################################
21 # Scenario 1: FOREIGN KEY referring an UNIQUE KEY
22 # conflicting case: updating referring tuple while
23 # the tuple with referring key is getting inserted
24 # in child table
25
26 ############################################################
27 # 0. Initial setup and checks.
28 [connection server1]
29 SET SESSION sql_log_bin= 0;
30 include/gtid_utils.inc
31 SET SESSION sql_log_bin= 1;
32 include/gtid_step_reset.inc
33
34 ############################################################
35 # 1. Set a debug sync before broadcast message to group on
36 # connection local_server_connection1.
37 [connection server1]
38 SET @debug_save= @@GLOBAL.DEBUG;
39 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
40
41 #####################################################################
42 # 2. Commit local_transaction that will be blocked before broadcast.
43 BEGIN;
44 INSERT INTO t2 VALUES (1, 1);
45 COMMIT;
46
47 ############################################################
48 # 3. Wait until local transaction reaches the
49 # group_replication_before_message_broadcast debug sync point.
50 [connection server_1]
51
52 ############################################################
53 # 4. Execute a transaction on remote server, that will reach first
54 # certification, since transaction on the local server
55 # is blocked before broadcast.
56 [connection server2]
57 BEGIN;
58 UPDATE t1 SET c2=3 WHERE c1=1;
59 COMMIT;
60
61 ############################################################
62 # 5. Signal the waiting thread on local server to resume the
63 # transaction.
64 [connection server_1]
65 SET DEBUG_SYNC='now SIGNAL waiting';
66 SET @@GLOBAL.DEBUG= @debug_save;
67
68 ############################################################
69 # 6. Wait for remote transaction to be executed succesfully
70 on local server.
71 [connection server2]
72 include/sync_slave_sql_with_master.inc
73 ############################################################
74 # 7. If the test case is conflict scenario, local transaction
75 # will end up in an error stating that it was aborted,
76 # since transactions are conflicting and transaction on
77 # remote server was ordered first. If the test case is
78 # is positive scenario, no error will be seen here.
79 [connection server1]
80 ERROR 40000: Plugin instructed the server to rollback the current transaction.
81 [connection server1]
82 SET @@GLOBAL.DEBUG=@debug_save;
83 ############################################################
84 # 8. Sync everything
85 include/rpl_sync.inc
86 ############################################################
87 # 9. Assert that number of certified transactions are the
88 # expected ones.
89 include/assert.inc [The value of Count_Transactions_Checked should be 6 after starting group replication]
90 include/assert.inc [The value of Count_Conflicts_Detected should be 1 after starting group replication]
91 ############################################################
92 # 10. Assert that GTID is increased as expected
93 include/gtid_step_assert.inc [count=1, only_count=1]
94 [connection server1]
95 SET SESSION sql_log_bin= 0;
96 include/gtid_utils_end.inc
97 SET SESSION sql_log_bin= 1;
98 ############################################################
99 # 11. Cleanup (restore the connection back to original)
100 [connection server1]
101 ############################################################
102 # check data sanity
103 [connection server1]
104 include/assert.inc [Table t1 will contain update row after the above execution]
105 include/assert.inc [Table t2 should have zero rows after the above conflict]
106
107 ############################################################
108 # Scenario 2: FOREIGN KEY referring an UNIQUE KEY
109 # non-conflicting case: inserting a tuple in child's table
110 # while updating a tuple in parent's table which is not getting
111 # referred
112
113 ############################################################
114 # 0. Initial setup and checks.
115 [connection server1]
116 SET SESSION sql_log_bin= 0;
117 include/gtid_utils.inc
118 SET SESSION sql_log_bin= 1;
119 include/gtid_step_reset.inc
120
121 ############################################################
122 # 1. Set a debug sync before broadcast message to group on
123 # connection local_server_connection1.
124 [connection server1]
125 SET @debug_save= @@GLOBAL.DEBUG;
126 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
127
128 #####################################################################
129 # 2. Commit local_transaction that will be blocked before broadcast.
130 BEGIN;
131 INSERT INTO t2 VALUES (1, 3);
132 COMMIT;
133
134 ############################################################
135 # 3. Wait until local transaction reaches the
136 # group_replication_before_message_broadcast debug sync point.
137 [connection server_1]
138
139 ############################################################
140 # 4. Execute a transaction on remote server, that will reach first
141 # certification, since transaction on the local server
142 # is blocked before broadcast.
143 [connection server2]
144 BEGIN;
145 UPDATE t1 SET c2=4 WHERE c1=2;
146 COMMIT;
147
148 ############################################################
149 # 5. Signal the waiting thread on local server to resume the
150 # transaction.
151 [connection server_1]
152 SET DEBUG_SYNC='now SIGNAL waiting';
153 SET @@GLOBAL.DEBUG= @debug_save;
154
155 ############################################################
156 # 6. Wait for remote transaction to be executed succesfully
157 on local server.
158 [connection server2]
159 include/sync_slave_sql_with_master.inc
160 ############################################################
161 # 7. If the test case is conflict scenario, local transaction
162 # will end up in an error stating that it was aborted,
163 # since transactions are conflicting and transaction on
164 # remote server was ordered first. If the test case is
165 # is positive scenario, no error will be seen here.
166 [connection server1]
167 [connection server1]
168 SET @@GLOBAL.DEBUG=@debug_save;
169 ############################################################
170 # 8. Sync everything
171 include/rpl_sync.inc
172 ############################################################
173 # 9. Assert that number of certified transactions are the
174 # expected ones.
175 include/assert.inc [The value of Count_Transactions_Checked should be 8 after starting group replication]
176 include/assert.inc [The value of Count_Conflicts_Detected should be 1 after starting group replication]
177 ############################################################
178 # 10. Assert that GTID is increased as expected
179 include/gtid_step_assert.inc [count=2, only_count=1]
180 [connection server1]
181 SET SESSION sql_log_bin= 0;
182 include/gtid_utils_end.inc
183 SET SESSION sql_log_bin= 1;
184 ############################################################
185 # 11. Cleanup (restore the connection back to original)
186 [connection server1]
187 ############################################################
188 # check data sanity
189 [connection server1]
190 include/assert.inc [Table t1 will contain update row after the above execution]
191 include/assert.inc [Table t2 will contain one tuple]
192
193 ############################################################
194 # Clean up.
195 DROP TABLE t2;
196 DROP TABLE t1;
197 include/rpl_sync.inc
198
199 ##################################################################
200 # Scenario: Check the group replication parallel applier indexes
201 # of the DMLs that involves foreign key which is referring
202 # an unique key.
203
204 [connection server2]
205 include/stop_group_replication.inc
206 include/start_group_replication.inc
207
208 #####################################################################
209 # Create two tables on server1.
210 # t1 : parent table with a primary key and unique key.
211 # t2 : child table with a primary key and foreign key referring
212 # t1's unique key.
213
214 [connection server1]
215 CREATE TABLE t1 (a INT,b INT UNIQUE NOT NULL, PRIMARY KEY(a));
216 CREATE TABLE t2 (c1 INT PRIMARY KEY,c2 INT,FOREIGN KEY(c2) REFERENCES t1(b));
217
218 #####################################################################
219 # Execute few DMLs on these two tables
220
221 INSERT INTO t1 VALUES(11,1);
222 INSERT INTO t1 VALUES(12,2);
223 INSERT INTO t2 VALUES(31,1);
224 INSERT INTO t2 VALUES(32,2);
225 UPDATE t2 SET c1=21 WHERE c2=1;
226 DELETE FROM t2 WHERE c2=2;
227 DELETE FROM t1 WHERE b=2;
228 DELETE FROM t2 WHERE c1=21;
229 DROP TABLE t2;
230 DROP TABLE t1;
231 include/rpl_sync.inc
232
233 #####################################################
234 # Dump Group Replication applier channel relay log
235
236 [connection server2]
237 Matching lines are:
238 #time# ##:##:## server id 2 end_log_pos ## GTID last_committed=0 sequence_number=0 rbr_only=no original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
239 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
240 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=2 sequence_number=3 rbr_only=no original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
241 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=3 sequence_number=4 rbr_only=yes original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
242 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=3 sequence_number=5 rbr_only=yes original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
243 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=4 sequence_number=6 rbr_only=yes original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
244 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=5 sequence_number=7 rbr_only=yes original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
245 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=6 sequence_number=8 rbr_only=yes original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
246 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=7 sequence_number=9 rbr_only=yes original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
247 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=9 sequence_number=10 rbr_only=yes original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
248 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=8 sequence_number=11 rbr_only=yes original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
249 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=11 sequence_number=12 rbr_only=no original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
250 #time# ##:##:## server id 1 end_log_pos ## GTID last_committed=12 sequence_number=13 rbr_only=no original_committed_timestamp=##:##:## immediate_commit_timestamp=##:##:## transaction_length=#
251 Occurrences of 'last_committed' in the input file: 13
252
253 ################################
254 # Validate indexes on member 2.
255 #
256 include/include/assert_logical_timestamps.inc [0 0;1 2;2 3;3 4;3 5;4 6;5 7;6 8;7 9;9 10;8 11;11 12;12 13]
257 include/group_replication_end.inc