Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # Test verifies that, slave doesn't report any assert on UPDATE or DELETE of
4 # row which tries to update the virtual columns with associated KEYs.
5 #
6 # Test scenarios are listed below.
7 # 1) KEY on a virtual column with ON DELETE CASCADE
8 # 2) Verify "ON DELETE CASCADE" for parent->child->child scenario
9 # 3) KEY on a virtual column with ON UPDATE CASCADE
10 # 4) Define triggers on master, their results should be replicated
11 # as part of row events and they should be applied on slave with
12 # the default slave_run_triggers_for_rbr=NO
13 # 5) Define triggers + Foreign Keys on master, their results should be
14 # replicated as part of row events and master and slave should be in sync.
15 # 6) Triggers are present only on slave and 'slave_run_triggers_for_rbr=NO'
16 # 7) Triggers are present only on slave and 'slave_run_triggers_for_rbr=YES'
17 # 8) Triggers and Foreign Keys are present only on slave and
18 # 'slave_run_triggers_for_rbr=NO'
19 # 9) Triggers are Foreign Keys are present only on slave and
20 # 'slave_run_triggers_for_rbr=YES'
21 #
22 # ==== References ====
23 #
24 # MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11
25 #
26
27 --source include/have_binlog_format_row.inc
28 --source include/have_innodb.inc
29 --source include/master-slave.inc
30
31
32 --echo #
33 --echo # Test case 1: KEY on a virtual column with ON DELETE CASCADE
34 --echo #
35 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
36 INSERT INTO t1 VALUES (1),(2),(3);
37
38 CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY,
39 t1_id INT NOT NULL,
40 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
41 CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
42 ) ENGINE=InnoDB;
43
44 INSERT INTO t2 VALUES (90,1,NULL);
45 INSERT INTO t2 VALUES (91,2,default);
46
47 # Following query results in an assert on slave
48 DELETE FROM t1 WHERE id=1;
49 --sync_slave_with_master
50
51 --echo #
52 --echo # Verify data consistency on slave
53 --echo #
54 --let $diff_tables= master:test.t1, slave:test.t1
55 --source include/diff_tables.inc
56 --let $diff_tables= master:test.t2, slave:test.t2
57 --source include/diff_tables.inc
58
59 --connection master
60 DROP TABLE t2,t1;
61 --sync_slave_with_master
62
63 --echo #
64 --echo # Test case 2: Verify "ON DELETE CASCADE" for parent->child->child scenario
65 --echo # Parent table: users
66 --echo # Child tables: matchmaking_groups, matchmaking_group_users
67 --echo # Parent table: matchmaking_groups
68 --echo # Child tables: matchmaking_group_users, matchmaking_group_maps
69 --echo #
70 --echo # Deleting a row from parent table should be reflected in
71 --echo # child tables.
72 --echo # matchmaking_groups->matchmaking_group_users->matchmaking_group_maps
73 --echo # users->matchmaking_group_users->matchmaking_group_maps
74 --echo #
75
76 --connection master
77 CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
78 name VARCHAR(32) NOT NULL DEFAULT ''
79 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
80
81 CREATE TABLE matchmaking_groups (
82 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
83 host_user_id INT UNSIGNED NOT NULL UNIQUE,
84 v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col),
85 CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
86 ON DELETE CASCADE ON UPDATE CASCADE
87 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
88
89 CREATE TABLE matchmaking_group_users (
90 matchmaking_group_id BIGINT UNSIGNED NOT NULL,
91 user_id INT UNSIGNED NOT NULL,
92 v_col1 int as (user_id+1) virtual, KEY (v_col1),
93 PRIMARY KEY (matchmaking_group_id,user_id),
94 UNIQUE KEY user_id (user_id),
95 CONSTRAINT FOREIGN KEY (matchmaking_group_id)
96 REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
97 CONSTRAINT FOREIGN KEY (user_id)
98 REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
99 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
100
101 CREATE TABLE matchmaking_group_maps (
102 matchmaking_group_id BIGINT UNSIGNED NOT NULL,
103 map_id TINYINT UNSIGNED NOT NULL,
104 v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2),
105 PRIMARY KEY (matchmaking_group_id,map_id),
106 CONSTRAINT FOREIGN KEY (matchmaking_group_id)
107 REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
109 --sync_slave_with_master
110
111 --connection master
112 INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
113 INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default);
114 INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default);
115 INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default);
116
117 DELETE FROM matchmaking_groups WHERE id = 10;
118 --sync_slave_with_master
119
120 --echo #
121 --echo # No rows should be returned as ON DELETE CASCASE should have removed
122 --echo # corresponding rows from child tables. There should not any mismatch
123 --echo # of 'id' field between parent->child.
124 --echo #
125 SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
126 SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
127
128 --echo #
129 --echo # Rows with id=11 should be present
130 --echo #
131 SELECT * FROM matchmaking_group_users;
132 SELECT * FROM matchmaking_group_maps;
133
134 --connection master
135 DELETE FROM users WHERE id = 2;
136 --sync_slave_with_master
137
138 --echo #
139 --echo # No rows should be present in both the child tables
140 --echo #
141 SELECT * FROM matchmaking_group_users;
142 SELECT * FROM matchmaking_group_maps;
143
144 --connection master
145 DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
146 --sync_slave_with_master
147
148 --echo #
149 --echo # Test case 3: KEY on a virtual column with ON UPDATE CASCADE
150 --echo #
151
152 --connection master
153 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
154 INSERT INTO t1 VALUES (1, 80);
155
156 CREATE TABLE t2 (a INT KEY, b INT,
157 v_col int as (b+1) virtual, KEY (v_col),
158 CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE
159 ) ENGINE=InnoDB;
160 INSERT INTO t2 VALUES (51, 1, default);
161 --sync_slave_with_master
162
163 --connection master
164 UPDATE t1 SET a = 50 WHERE a = 1;
165
166 --echo #
167 --echo # Master: Verify that ON UPDATE CASCADE works fine
168 --echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
169 --echo #
170 SELECT * FROM t2 WHERE b=50;
171 --sync_slave_with_master
172
173 --echo #
174 --echo # Slave: Verify that ON UPDATE CASCADE works fine
175 --echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
176 --echo #
177 SELECT * FROM t2 WHERE b=50;
178
179 --connection master
180 DROP TABLE t2, t1;
181 --sync_slave_with_master
182
183 --echo #
184 --echo # Test case 4: Define triggers on master, their results should be
185 --echo # replicated as part of row events and they should be
186 --echo # applied on slave with the default
187 --echo # slave_run_triggers_for_rbr=NO
188 --echo #
189
190 # In row-based replication, the binary log contains row changes. It will have
191 # both the changes made by the statement itself, and the changes made by the
192 # triggers that were invoked by the statement. Slave server(s) do not need to
193 # run triggers for row changes they are applying. Hence verify that this
194 # property remains the same and data should be available as if trigger was
195 # executed. Please note by default slave_run_triggers_for_rbr=NO.
196
197 --connection master
198 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
199 CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB;
200 CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1);
201 INSERT INTO t1 VALUES (2),(3);
202 --sync_slave_with_master
203
204 SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr';
205 --echo #
206 --echo # As two rows are inserted in table 't1', two rows should get inserted
207 --echo # into table 't2' as part of trigger.
208 --echo #
209 --let $assert_cond= COUNT(*) = 2 FROM t2
210 --let $assert_text= Table t2 should have two rows.
211 --source include/assert.inc
212
213 --connection master
214 DROP TABLE t1,t2;
215 --sync_slave_with_master
216
217 --echo #
218 --echo # Test case 5: Define triggers + Foreign Keys on master, their results
219 --echo # should be replicated as part of row events and master
220 --echo # and slave should be in sync.
221 --echo #
222 --connection master
223 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
224 CREATE TABLE t2 (t1_id INT NOT NULL,
225 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
226 CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
227 ) ENGINE=InnoDB;
228 CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
229 CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1);
230
231 INSERT INTO t1 VALUES (2),(3);
232 INSERT INTO t2 VALUES (2, default), (3, default);
233 --sync_slave_with_master
234
235 --echo #
236 --echo # As two rows are inserted in table 't1', two rows should get inserted
237 --echo # into table 't3' as part of trigger.
238 --echo #
239 --let $assert_cond= COUNT(*) = 2 FROM t3
240 --let $assert_text= Table t3 should have two rows.
241 --source include/assert.inc
242
243 --echo #
244 --echo # Verify ON DELETE CASCASE correctness
245 --echo #
246 --connection master
247 DELETE FROM t1 WHERE id=2;
248 --sync_slave_with_master
249
250 --connection master
251 --let $diff_tables= master:test.t1, slave:test.t1
252 --source include/diff_tables.inc
253 --let $diff_tables= master:test.t2, slave:test.t2
254 --source include/diff_tables.inc
255 --let $diff_tables= master:test.t3, slave:test.t3
256 --source include/diff_tables.inc
257
258 DROP TABLE t3,t2,t1;
259 --sync_slave_with_master
260
261 #
262 # Test case: Triggers only on slave
263 #
264 --write_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc PROCEDURE
265 if ($slave_run_triggers_for_rbr == '') {
266 --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr
267 }
268
269 --connection slave
270 SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
271 --eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr;
272 SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
273
274 --connection master
275 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
276 CREATE TABLE t2 (t1_id INT NOT NULL,
277 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
278 KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
279 ) ENGINE=InnoDB;
280 CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
281 --sync_slave_with_master
282
283 CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
284
285 --connection master
286 INSERT INTO t1 VALUES (2),(3);
287 INSERT INTO t2 VALUES (2, default), (3, default);
288 --sync_slave_with_master
289
290 if ($slave_run_triggers_for_rbr == 'NO') {
291 --echo #
292 --echo # Count must be 0
293 --echo #
294 --let $assert_cond= COUNT(*) = 0 FROM t3
295 --let $assert_text= Table t3 should have zero rows.
296 --source include/assert.inc
297 }
298 if ($slave_run_triggers_for_rbr == 'YES') {
299 --echo #
300 --echo # Count must be 2
301 --echo #
302 --let $assert_cond= COUNT(*) = 2 FROM t3
303 --let $assert_text= Table t3 should have two rows.
304 --source include/assert.inc
305 }
306
307 --connection master
308 DELETE FROM t1 WHERE id=2;
309 --sync_slave_with_master
310 SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
311
312 --echo #
313 --echo # Verify t1, t2 are consistent on slave.
314 --echo #
315 --let $diff_tables= master:test.t1, slave:test.t1
316 --source include/diff_tables.inc
317 --let $diff_tables= master:test.t2, slave:test.t2
318 --source include/diff_tables.inc
319
320 --connection master
321 DROP TABLE t3,t2,t1;
322 --sync_slave_with_master
323 #END OF
324 PROCEDURE
325
326 --echo #
327 --echo # Test case 6: Triggers are present only on slave and
328 --echo # 'slave_run_triggers_for_rbr=NO'
329 --echo #
330 --let $slave_run_triggers_for_rbr=NO
331 --source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc
332
333 --echo #
334 --echo # Test case 7: Triggers are present only on slave and
335 --echo # 'slave_run_triggers_for_rbr=YES'
336 --echo #
337 --let $slave_run_triggers_for_rbr=YES
338 --source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc
339 --remove_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc
340
341 #
342 # Test case: Trigger and Foreign Key are present only on slave
343 #
344 --write_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc PROCEDURE
345 if ($slave_run_triggers_for_rbr == '') {
346 --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr
347 }
348
349 --connection slave
350 SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
351 --eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr;
352 SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
353
354 --connection master
355 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
356 SET sql_log_bin=0;
357 CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
358 SET sql_log_bin=1;
359 CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
360 --sync_slave_with_master
361
362 # Have foreign key and trigger on slave.
363 CREATE TABLE t2 (t1_id INT NOT NULL,
364 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
365 CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
366 ) ENGINE=InnoDB;
367 CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
368
369 --connection master
370 INSERT INTO t1 VALUES (2),(3);
371 INSERT INTO t2 VALUES (2, default), (3, default);
372 --sync_slave_with_master
373
374 if ($slave_run_triggers_for_rbr == 'NO') {
375 --echo #
376 --echo # Count must be 0
377 --echo #
378 --let $assert_cond= COUNT(*) = 0 FROM t3
379 --let $assert_text= Table t3 should have zero rows.
380 --source include/assert.inc
381 }
382 if ($slave_run_triggers_for_rbr == 'YES') {
383 --echo #
384 --echo # Count must be 2
385 --echo #
386 --let $assert_cond= COUNT(*) = 2 FROM t3
387 --let $assert_text= Table t3 should have two rows.
388 --source include/assert.inc
389 }
390
391 --connection master
392 DELETE FROM t1 WHERE id=2;
393 --echo # t1: Should have one row
394 SELECT * FROM t1;
395 --echo # t2: Should have two rows
396 SELECT * FROM t2;
397 --sync_slave_with_master
398 --echo # t1: Should have one row
399 SELECT * FROM t1;
400 --echo # t2: Should have one row on slave due to ON DELETE CASCASE
401 SELECT * FROM t2;
402 SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
403
404 --connection master
405 DROP TABLE t3,t2,t1;
406 --sync_slave_with_master
407 #END OF
408 PROCEDURE
409
410 --echo #
411 --echo # Test case 8: Triggers and Foreign Keys are present only on slave and
412 --echo # 'slave_run_triggers_for_rbr=NO'
413 --echo #
414 --let $slave_run_triggers_for_rbr=NO
415 --source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc
416
417 --echo #
418 --echo # Test case 9: Triggers are Foreign Keys are present only on slave and
419 --echo # 'slave_run_triggers_for_rbr=YES'
420 --echo #
421 --let $slave_run_triggers_for_rbr=YES
422 --source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc
423 --remove_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc
424
425 --source include/rpl_end.inc