Debian Codegraph @

mysql-test/suite/rpl/t/rpl_binlog_transaction_dependency_tracking_with_indexes.test

raw
1 # ==== Purpose ====
2 #
3 # Verify that logical timestamps are generated correctly for all types
4 # of indexes when binlog_transaction_dependency_tracking=WRITESET.
5 #
6 # The following properties are tested:
7 #
8 # P1. Any transaction that updates a table having no primary key, must
9 # (a) depend on the previous transaction, but (b) must not clear
10 # history.
11 #
12 # P1.1. This must hold if the transaction has no writeset.
13 # (e.g. it updates only the no-pk table; a no-pk table will
14 # not add to the writeset).
15 #
16 # P1.2. This must hold if the transaction has a writeset.
17 # (e.g. it updates some other table too).
18 #
19 # P2. Any transaction that only updates tables having primary keys
20 # must compute its dependencies using the writeset. (In contrast
21 # with P1).
22 #
23 # P3. A UNIQUE index with NOT NULL constraints on all columns counts
24 # as a primary key too.
25 #
26 # P4. If there is both a primary key and other keys, then uniqueness
27 # constraints due to the other keys generate dependencies too.
28 #
29 # P5. If there is both a primary key and nullable columns, then NULL
30 # values in the same column do not conflict.
31 #
32 # ==== Implementation ====
33 #
34 # Repeatedly define a table t1 with three columns a, b, c, using all
35 # combinations of the following parameters:
36 #
37 # A. Without/with explicit primary key on a.
38 #
39 # B. Without/with NOT NULL constraints on b and c.
40 #
41 # C. Without/with uniqueness constraints on b and/or c.
42 #
43 # D. Without index on b and c/with index on b/with index on c.
44 # (Skip the no-index case if uniqueness constraints are required).
45 #
46 # This gives 20 combinations. For each such combination, execute the
47 # following:
48 #
49 # INSERT (1, 1, 1)
50 # INSERT (2, 2, 2)
51 # -> should be independent
52 # DELETE (2, 2, 2)
53 # -> should depend on insert (2, 2, 2)
54 # UPDATE (1, 1, 1) to (1, 2, 2)
55 # -> if there are uniqueness constraints on b and/or c, this should
56 # depend on delete (2, 2, 2) (and that verifies P4). Otherwise, depend
57 # on insert (1, 1, 1). When there is a pk, this verifies P1.1, otherwise
58 # it verifies P2.
59 # if columns b and c are nullable:
60 # INSERT (3, NULL, 2)
61 # -> should be independent
62 # INSERT (4, NULL, 2)
63 # -> should be independent since multiple NULL values are allowed.
64 # This verifies P5.
65 # DELETE (3, NULL, 2)
66 # -> should depend on insert (3, NULL, 2) but not on insert (4, NULL, 2)
67 # This verifies P5.
68 #
69 # To verify P1(b), execute extra, independent transactions updating
70 # another table t2 after some of the statements above (e.g. after
71 # delete (2, 2, 2)). Verify that the extra transaction is marked as
72 # independent of all the previous ones.
73 #
74 # To verify P1.2, include a statement doing an independent insert into
75 # t2 within the same transaction that inserts (2, 2, 2) in t1.
76 #
77 # To distinguish the cases that DELETE (2, 2, 2) depends on INSERT (2,
78 # 2, 2) (when there is a pk) from the case that it is executed in
79 # isolation due to not having a pk, we precede it by an independent
80 # transaction updating another table, t2. Similar for UPDATE (1, 1,
81 # 1) to (2, 2, 2), for INSERT (4, NULL, 2), and for DELETE (3, NULL,
82 # 2).
83 #
84 # P3 is verified in the loop iterations where we use both NOT NULL and
85 # UNIQUE.
86 #
87 # ==== References ====
88 #
89 # WL#9556: Writeset-based MTS dependency tracking on master
90
91 --source include/have_binlog_format_row.inc
92 --source include/have_transaction_write_set_extraction.inc
93 --source include/master-slave.inc
94
95 --echo #### INITIALIZE ####
96
97 --source include/rpl_connection_slave.inc
98 --source include/stop_slave.inc
99 SET @save_replica_parallel_workers= @@GLOBAL.replica_parallel_workers;
100 SET @@GLOBAL.replica_parallel_workers= 4;
101 --source include/start_slave.inc
102
103 --source include/rpl_connection_master.inc
104 SET @save_binlog_transaction_dependency_tracking = @@GLOBAL.binlog_transaction_dependency_tracking;
105
106 SET GLOBAL binlog_transaction_dependency_tracking = COMMIT_ORDER;
107 SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;
108
109 # Tell assert_logical_timestamps to save the current position so that
110 # the next call will continue where the current ended.
111 --let $save_binlog_position= 1
112
113 # First without any explicit PRIMARY KEY, then with one on column a.
114 --let $explicit_pk= 0
115 while ($explicit_pk < 2)
116 {
117
118 # First with columns b and c NULL-able, then with NOT NULL on both.
119 --let $nonnull= 0
120 while ($nonnull < 2)
121 {
122
123 # First without uniqueness constraints on second index, then with.
124 --let $unique= 0
125 while ($unique < 2)
126 {
127
128 # First without index (unless $unique==1), then with 1-column
129 # index, then with 2-column index.
130 --let $colcount= $unique
131 while ($colcount < 3)
132 {
133
134 # Determine if there is a primary key. Unique non-null indexes
135 # count as primary if there is no explicit primary key.
136 --let $pk= 0
137 if ($explicit_pk)
138 {
139 --let $pk= 1
140 }
141 if ($unique)
142 {
143 if ($nonnull)
144 {
145 --let $pk= 1
146 }
147 }
148
149 # Compute table definition and header according to all the parameters.
150 --let $text= $explicit_pk.$nonnull.$unique.$colcount
151 if ($explicit_pk == 0)
152 {
153 --let $text= $text: WITHOUT PK
154 --let $first_column_def= a INT
155 }
156 if ($explicit_pk == 1)
157 {
158 --let $text= $text: WITH PK
159 --let $first_column_def= a INT PRIMARY KEY
160 }
161 if ($nonnull == 0)
162 {
163 --let $text= $text, NULLABLE
164 --let $not_null_def=
165 }
166 if ($nonnull == 1)
167 {
168 --let $text= $text, NOT NULL
169 --let $not_null_def= NOT NULL
170 }
171 if ($unique == 0)
172 {
173 --let $text= $text, NOT UNIQUE
174 --let $unique_def=
175 }
176 if ($unique == 1)
177 {
178 --let $text= $text, UNIQUE
179 --let $unique_def= UNIQUE
180 }
181 if ($colcount == 0)
182 {
183 --let $text= $text, NO INDEX
184 --let $second_index_def=
185 }
186 if ($colcount == 1)
187 {
188 --let $text= $text, 1-COLUMN INDEX
189 --let $second_index_def= , $unique_def INDEX(b)
190 }
191 if ($colcount == 2)
192 {
193 --let $text= $text, 2-COLUMN INDEX
194 --let $second_index_def= , $unique_def INDEX(b, c)
195 }
196
197 --echo
198 --echo #### $text ####
199 --echo
200
201 eval CREATE TABLE t1 ($first_column_def, b INT $not_null_def, c INT $not_null_def $second_index_def);
202 CREATE TABLE t2 (a INT PRIMARY KEY);
203
204 FLUSH LOGS;
205 --source include/save_binlog_position.inc
206
207 --echo # TRX1: independent
208 INSERT INTO t1 VALUES (1, 1, 1);
209
210 --echo # now t1 contains one row: (1, 1, 1)
211 --let $logical_timestamps= 0 1
212 --source include/assert_logical_timestamps.inc
213
214 --echo # TRX2: independent
215 BEGIN;
216 INSERT INTO t1 VALUES (2, 2, 2);
217 INSERT INTO t2 VALUES (1);
218 COMMIT;
219
220 --echo # now t1 contains two rows: (1, 1, 1), (2, 2, 2)
221 if ($pk) { --let $logical_timestamps= 1 2
222 }
223 if (!$pk) { --let $logical_timestamps= 1 2
224 }
225 --source include/assert_logical_timestamps.inc
226
227 --echo # TRX3: independent
228 INSERT INTO t2 VALUES (3);
229 --echo # TRX4: depends on TRX2
230 DELETE FROM t1 WHERE a = 2;
231
232 --echo # now t1 contains one row: (1, 1, 1)
233 if ($pk) { --let $logical_timestamps= 1 3;2 4
234 }
235 if (!$pk) { --let $logical_timestamps= 1 3;3 4
236 }
237 --source include/assert_logical_timestamps.inc
238
239 --echo # TRX5: independent
240 INSERT INTO t2 VALUES (5);
241 --echo # TRX6: conflict with TRX4 if unique, else with TRX1
242 UPDATE t1 SET b = 2, c = 2 WHERE a = 1;
243
244 --echo # now t1 contains one row: (1, 2, 2)
245 if ($pk) {
246 if ($unique) { --let $logical_timestamps= 1 5;4 6
247 }
248 if (!$unique) { --let $logical_timestamps= 1 5;1 6
249 }
250 }
251 if (!$pk) { --let $logical_timestamps= 1 5;5 6
252 }
253 --source include/assert_logical_timestamps.inc
254
255 if (!$nonnull)
256 {
257
258 --echo # TRX7: independent
259 INSERT INTO t1 VALUES (3, NULL, 2);
260
261 --echo # now t1 contains two rows: (1, 2, 2), (3, NULL, 2)
262 if ($pk) { --let $logical_timestamps= 1 7
263 }
264 if (!$pk) { --let $logical_timestamps= 6 7
265 }
266 --source include/assert_logical_timestamps.inc
267
268 --echo # TRX8: independent
269 INSERT INTO t2 VALUES (8);
270 --echo # TRX9: independent (no conflict with TRX7 since multiple NULLs are allowed)
271 INSERT INTO t1 VALUES (4, NULL, 2);
272
273 --echo # now t1 contains three rows: (1, 2, 2), (3, NULL, 2), (4, NULL, 2)
274 if ($pk) { --let $logical_timestamps= 1 8;1 9
275 }
276 if (!$pk) { --let $logical_timestamps= 1 8;8 9
277 }
278 --source include/assert_logical_timestamps.inc
279
280 --echo # TRX10: independent
281 INSERT INTO t2 VALUES (10);
282 --echo # TRX11: conflict with TRX7 (not with TRX9 since multiple NULLs are allowed)
283 DELETE FROM t1 WHERE a = 3;
284
285 --echo # now t1 contains two rows: (1, 2, 2), (4, NULL, 2)
286 if ($pk) { --let $logical_timestamps= 1 10;7 11
287 }
288 if (!$pk) { --let $logical_timestamps= 1 10;10 11
289 }
290 --source include/assert_logical_timestamps.inc
291
292 }
293
294 DROP TABLE t1;
295 DROP TABLE t2;
296
297 --inc $colcount
298 }
299 --inc $unique
300 }
301 --inc $nonnull
302 }
303 --inc $explicit_pk
304 }
305
306 --echo #### CLEAN UP ####
307 # Value of TWSE cannot be changed when binlog_transaction_dependency_tracking!= COMMIT_ORDER
308 SET @@GLOBAL.binlog_transaction_dependency_tracking= COMMIT_ORDER;
309 SET @@GLOBAL.binlog_transaction_dependency_tracking= @save_binlog_transaction_dependency_tracking;
310
311 --source include/sync_slave_sql_with_master.inc
312 --disable_warnings
313 SET @@GLOBAL.replica_parallel_workers= @save_replica_parallel_workers;
314 --enable_warnings
315
316 --source include/rpl_end.inc