Debian Codegraph @

mysql-test/suite/ndb_rpl/t/log_transaction_dependency_with_indexes.test

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