Debian Codegraph @

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

raw
1 ################################################################################
2 # ==== Purpose ====
3 # Test referential actions of the foreign keys with different
4 # binlog_transaction_dependency_tracking modes.
5 # Referential actions:
6 # RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT
7 # Note* Table definition containing SET DEFAULT gets rejected thus not tested.
8 # Modes:
9 # COMMIT_ORDER | WRITESET | WRITESET_SESSION
10 # Note* COMMIT_ORDER is purposefully not tested with this test.
11 #
12 # ==== Implementation ====
13 # 1. FK parent table:
14 # 1.1. Marked as conflicting with previous transactions.
15 # 1.2. Clears history.
16 # 1.3. Marked as conflicting with later transactions.
17 # 1.4. Falls back to COMMIT_ORDER, so *not* marked as conflicting with previous transactions
18 # committing in parallel.
19 # 1.5. Later transactions fall back to COMMIT_ORDER, so they are *not* marked as conflicting with
20 # this transaction if executing in parallel with it.
21 #
22 # 2. FK child table:
23 # 2.1. Can run in parallel with previous transactions.
24 # 2.2. Does not clear history.
25 # 2.3. Can run in parallel with subsequent transactions.
26 #
27 # 3. All the above is tested with INSERT, UPDATE, and DELETEs.
28 #
29 # 4. All the above is tested with RESTRICT, NO ACTION, CASCADE, SET NULL.
30 #
31 # 5. All the above is tested with ON UPDATE and ON DELETE.
32 #
33 # 6. All the above is tested with WRITESET_SESSION and WRITESET.
34 #
35 # ==== References ====
36 # WL#9556: Writeset-based MTS dependency tracking on master
37 ################################################################################
38 --let $rpl_extra_connections_per_server= 8
39 --source include/master-slave.inc
40 --source include/have_binlog_format_row.inc
41
42 --connection master
43 SET @save_binlog_transaction_dependency_tracking = @@GLOBAL.binlog_transaction_dependency_tracking;
44
45 --let $row_count= 1
46 --let $referential_action= 0
47 # RESTRICT, NO ACTION, CASCADE, SET NULL
48 while ($referential_action < 4)
49 {
50 # WRITESET, WRITESET_SESSION
51 --let $dependency_tracking= 0
52 while ($dependency_tracking < 2)
53 {
54 --connection master
55 if ($referential_action == 0)
56 {
57 --let $ref_action= RESTRICT
58 --let $error_success= ER_ROW_IS_REFERENCED_2
59 }
60 if ($referential_action == 1)
61 {
62 --let $ref_action= NO ACTION
63 --let $error_success= ER_ROW_IS_REFERENCED_2
64 }
65 if ($referential_action == 2)
66 {
67 --let $ref_action= CASCADE
68 --let $error_success= 0
69 }
70 if ($referential_action == 3)
71 {
72 --let $ref_action= SET NULL
73 --let $error_success= 0
74 }
75
76 --echo
77 if ($dependency_tracking == 0)
78 {
79 SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;
80 --let $text= $row_count. Test referential action $ref_action with WRITESET.
81 }
82 if ($dependency_tracking == 1)
83 {
84 SET GLOBAL binlog_transaction_dependency_tracking = WRITESET_SESSION;
85 --let $text= $row_count. Test referential action $ref_action with WRITESET_SESSION.
86 }
87
88 # Header according to all the parameters.
89 --echo ######## $text ########
90 SELECT @@binlog_transaction_dependency_tracking;
91
92 # In all these 8 cases, behavior should be identical.
93 CREATE TABLE parent1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50));
94 CREATE TABLE parent2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50));
95 CREATE TABLE t1 (a INT PRIMARY KEY);
96 --eval CREATE TABLE child1 (a INT PRIMARY KEY, b INT, FOREIGN KEY(b) REFERENCES parent1 (a) ON UPDATE $ref_action)
97 --eval CREATE TABLE child2 (a INT PRIMARY KEY, b INT, FOREIGN KEY(b) REFERENCES parent2 (a) ON DELETE $ref_action)
98 INSERT INTO parent1 VALUES (1, 1), (2, 2);
99 INSERT INTO parent2 VALUES (1, 1), (2, 2);
100 FLUSH LOGS;
101
102 --echo # Case 2.1, 2.2, 2.3
103 # [0 1]
104 --connection server_1_1
105 INSERT INTO child1 VALUES (1, 1);
106
107 # [1 2]
108 --connection server_1_2
109 INSERT INTO child2 VALUES (1, 1);
110
111 # [1 3]
112 --connection server_1_1
113 UPDATE child1 SET a = 2;
114
115 # [2 4]
116 --connection server_1_2
117 UPDATE child2 SET a = 2;
118
119 # [3 5]
120 --connection server_1_1
121 DELETE FROM child1;
122
123 # [4 6]
124 --connection server_1_2
125 DELETE FROM child2;
126
127 --echo # Case 1.1, 1.2, 1.3
128 # [1 7]
129 --connection server_1_3
130 INSERT INTO t1 VALUES (1);
131
132 # [7 8]
133 --connection server_1_4
134 INSERT INTO parent1 VALUES (3, 3);
135
136 # [8 9]
137 --connection server_1_5
138 INSERT INTO t1 VALUES (2);
139
140 --echo # Case 1.4, 1.5
141 # [8 10]
142 --connection server_1_6
143 BEGIN;
144 INSERT INTO t1 VALUES (3);
145 INSERT INTO child1 VALUES (1, 1);
146 # [9 11]
147 --connection server_1_7
148 BEGIN;
149 INSERT INTO parent1 VALUEs (4, 4);
150 INSERT INTO child2 VALUES (1, 1);
151 # [9 12]
152 --connection server_1_8
153 BEGIN;
154 INSERT INTO t1 VALUES (4);
155 # Commit the above.
156 --connection server_1_6
157 COMMIT;
158 --connection server_1_7
159 COMMIT;
160 --connection server_1_8
161 COMMIT;
162
163 # [11 13] Verify again that history is cleared
164 --connection server_1_1
165 INSERT INTO t1 VALUES (5);
166
167 # [13 14] With CASCADE and SET NULL
168 --connection server_1_2
169 --error $error_success
170 UPDATE parent1 SET a=5 WHERE a=1;
171
172 # [14 15] With CASCADE and SET NULL
173 --connection server_1_3
174 --error $error_success
175 DELETE FROM parent2 WHERE a=1;
176
177 --echo # Drop table and flush logs to force binlog to rotate
178 --connection master
179 DROP TABLE child2, parent2, child1, parent1, t1;
180
181 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
182 FLUSH LOGS;
183
184 --echo # Processing binlog...
185 # For RESTRICT and NO ACTION
186 --let $logical_timestamps=0 1;1 2;1 3;2 4;3 5;4 6;1 7;7 8;8 9;8 10;9 11;9 12;11 13;13 14
187 if ($referential_action >= 2)
188 {
189 # For CASCADE and SET NULL
190 --let $logical_timestamps=0 1;1 2;1 3;2 4;3 5;4 6;1 7;7 8;8 9;8 10;9 11;9 12;11 13;13 14;14 15;15 16
191 }
192 --source include/assert_logical_timestamps.inc
193
194 --echo # Verify that replication is correct
195 --source include/sync_slave_sql_with_master.inc
196
197 --inc $dependency_tracking
198 --inc $row_count
199 }
200 --inc $referential_action
201 }
202
203 --echo
204 --echo # Clean up
205 --connection master
206 SET @@GLOBAL.binlog_transaction_dependency_tracking= @save_binlog_transaction_dependency_tracking;
207
208 --source include/rpl_end.inc