Debian Codegraph @

mysql-test/suite/rpl/r/rpl_row_vcol_crash.result

raw
1 include/master-slave.inc
2 [connection master]
3 #
4 # Test case 1: KEY on a virtual column with ON DELETE CASCADE
5 #
6 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
7 INSERT INTO t1 VALUES (1),(2),(3);
8 CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY,
9 t1_id INT NOT NULL,
10 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
11 CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
12 ) ENGINE=InnoDB;
13 INSERT INTO t2 VALUES (90,1,NULL);
14 INSERT INTO t2 VALUES (91,2,default);
15 DELETE FROM t1 WHERE id=1;
16 connection slave;
17 #
18 # Verify data consistency on slave
19 #
20 include/diff_tables.inc [master:test.t1, slave:test.t1]
21 include/diff_tables.inc [master:test.t2, slave:test.t2]
22 connection master;
23 DROP TABLE t2,t1;
24 connection slave;
25 #
26 # Test case 2: Verify "ON DELETE CASCADE" for parent->child->child scenario
27 # Parent table: users
28 # Child tables: matchmaking_groups, matchmaking_group_users
29 # Parent table: matchmaking_groups
30 # Child tables: matchmaking_group_users, matchmaking_group_maps
31 #
32 # Deleting a row from parent table should be reflected in
33 # child tables.
34 # matchmaking_groups->matchmaking_group_users->matchmaking_group_maps
35 # users->matchmaking_group_users->matchmaking_group_maps
36 #
37 connection master;
38 CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
39 name VARCHAR(32) NOT NULL DEFAULT ''
40 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
41 CREATE TABLE matchmaking_groups (
42 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
43 host_user_id INT UNSIGNED NOT NULL UNIQUE,
44 v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col),
45 CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
46 ON DELETE CASCADE ON UPDATE CASCADE
47 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
48 CREATE TABLE matchmaking_group_users (
49 matchmaking_group_id BIGINT UNSIGNED NOT NULL,
50 user_id INT UNSIGNED NOT NULL,
51 v_col1 int as (user_id+1) virtual, KEY (v_col1),
52 PRIMARY KEY (matchmaking_group_id,user_id),
53 UNIQUE KEY user_id (user_id),
54 CONSTRAINT FOREIGN KEY (matchmaking_group_id)
55 REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
56 CONSTRAINT FOREIGN KEY (user_id)
57 REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
58 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
59 CREATE TABLE matchmaking_group_maps (
60 matchmaking_group_id BIGINT UNSIGNED NOT NULL,
61 map_id TINYINT UNSIGNED NOT NULL,
62 v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2),
63 PRIMARY KEY (matchmaking_group_id,map_id),
64 CONSTRAINT FOREIGN KEY (matchmaking_group_id)
65 REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
66 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
67 connection slave;
68 connection master;
69 INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
70 INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default);
71 INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default);
72 INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default);
73 DELETE FROM matchmaking_groups WHERE id = 10;
74 connection slave;
75 #
76 # No rows should be returned as ON DELETE CASCASE should have removed
77 # corresponding rows from child tables. There should not any mismatch
78 # of 'id' field between parent->child.
79 #
80 SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
81 matchmaking_group_id user_id v_col1
82 SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
83 matchmaking_group_id map_id v_col2
84 #
85 # Rows with id=11 should be present
86 #
87 SELECT * FROM matchmaking_group_users;
88 matchmaking_group_id user_id v_col1
89 11 2 3
90 SELECT * FROM matchmaking_group_maps;
91 matchmaking_group_id map_id v_col2
92 11 66 67
93 connection master;
94 DELETE FROM users WHERE id = 2;
95 connection slave;
96 #
97 # No rows should be present in both the child tables
98 #
99 SELECT * FROM matchmaking_group_users;
100 matchmaking_group_id user_id v_col1
101 SELECT * FROM matchmaking_group_maps;
102 matchmaking_group_id map_id v_col2
103 connection master;
104 DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
105 connection slave;
106 #
107 # Test case 3: KEY on a virtual column with ON UPDATE CASCADE
108 #
109 connection master;
110 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
111 INSERT INTO t1 VALUES (1, 80);
112 CREATE TABLE t2 (a INT KEY, b INT,
113 v_col int as (b+1) virtual, KEY (v_col),
114 CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE
115 ) ENGINE=InnoDB;
116 INSERT INTO t2 VALUES (51, 1, default);
117 connection slave;
118 connection master;
119 UPDATE t1 SET a = 50 WHERE a = 1;
120 #
121 # Master: Verify that ON UPDATE CASCADE works fine
122 # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
123 #
124 SELECT * FROM t2 WHERE b=50;
125 a b v_col
126 51 50 51
127 connection slave;
128 #
129 # Slave: Verify that ON UPDATE CASCADE works fine
130 # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
131 #
132 SELECT * FROM t2 WHERE b=50;
133 a b v_col
134 51 50 51
135 connection master;
136 DROP TABLE t2, t1;
137 connection slave;
138 #
139 # Test case 4: Define triggers on master, their results should be
140 # replicated as part of row events and they should be
141 # applied on slave with the default
142 # slave_run_triggers_for_rbr=NO
143 #
144 connection master;
145 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
146 CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB;
147 CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1);
148 INSERT INTO t1 VALUES (2),(3);
149 connection slave;
150 SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr';
151 Variable_name Value
152 slave_run_triggers_for_rbr NO
153 #
154 # As two rows are inserted in table 't1', two rows should get inserted
155 # into table 't2' as part of trigger.
156 #
157 include/assert.inc [Table t2 should have two rows.]
158 connection master;
159 DROP TABLE t1,t2;
160 connection slave;
161 #
162 # Test case 5: Define triggers + Foreign Keys on master, their results
163 # should be replicated as part of row events and master
164 # and slave should be in sync.
165 #
166 connection master;
167 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
168 CREATE TABLE t2 (t1_id INT NOT NULL,
169 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
170 CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
171 ) ENGINE=InnoDB;
172 CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
173 CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1);
174 INSERT INTO t1 VALUES (2),(3);
175 INSERT INTO t2 VALUES (2, default), (3, default);
176 connection slave;
177 #
178 # As two rows are inserted in table 't1', two rows should get inserted
179 # into table 't3' as part of trigger.
180 #
181 include/assert.inc [Table t3 should have two rows.]
182 #
183 # Verify ON DELETE CASCASE correctness
184 #
185 connection master;
186 DELETE FROM t1 WHERE id=2;
187 connection slave;
188 connection master;
189 include/diff_tables.inc [master:test.t1, slave:test.t1]
190 include/diff_tables.inc [master:test.t2, slave:test.t2]
191 include/diff_tables.inc [master:test.t3, slave:test.t3]
192 DROP TABLE t3,t2,t1;
193 connection slave;
194 #
195 # Test case 6: Triggers are present only on slave and
196 # 'slave_run_triggers_for_rbr=NO'
197 #
198 connection slave;
199 SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
200 SET GLOBAL slave_run_triggers_for_rbr= NO;;
201 SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
202 Variable_name Value
203 slave_run_triggers_for_rbr NO
204 connection master;
205 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
206 CREATE TABLE t2 (t1_id INT NOT NULL,
207 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
208 KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
209 ) ENGINE=InnoDB;
210 CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
211 connection slave;
212 CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
213 connection master;
214 INSERT INTO t1 VALUES (2),(3);
215 INSERT INTO t2 VALUES (2, default), (3, default);
216 connection slave;
217 #
218 # Count must be 0
219 #
220 include/assert.inc [Table t3 should have zero rows.]
221 connection master;
222 DELETE FROM t1 WHERE id=2;
223 connection slave;
224 SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
225 #
226 # Verify t1, t2 are consistent on slave.
227 #
228 include/diff_tables.inc [master:test.t1, slave:test.t1]
229 include/diff_tables.inc [master:test.t2, slave:test.t2]
230 connection master;
231 DROP TABLE t3,t2,t1;
232 connection slave;
233 #
234 # Test case 7: Triggers are present only on slave and
235 # 'slave_run_triggers_for_rbr=YES'
236 #
237 connection slave;
238 SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
239 SET GLOBAL slave_run_triggers_for_rbr= YES;;
240 SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
241 Variable_name Value
242 slave_run_triggers_for_rbr YES
243 connection master;
244 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
245 CREATE TABLE t2 (t1_id INT NOT NULL,
246 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
247 KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
248 ) ENGINE=InnoDB;
249 CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
250 connection slave;
251 CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
252 connection master;
253 INSERT INTO t1 VALUES (2),(3);
254 INSERT INTO t2 VALUES (2, default), (3, default);
255 connection slave;
256 #
257 # Count must be 2
258 #
259 include/assert.inc [Table t3 should have two rows.]
260 connection master;
261 DELETE FROM t1 WHERE id=2;
262 connection slave;
263 SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
264 #
265 # Verify t1, t2 are consistent on slave.
266 #
267 include/diff_tables.inc [master:test.t1, slave:test.t1]
268 include/diff_tables.inc [master:test.t2, slave:test.t2]
269 connection master;
270 DROP TABLE t3,t2,t1;
271 connection slave;
272 #
273 # Test case 8: Triggers and Foreign Keys are present only on slave and
274 # 'slave_run_triggers_for_rbr=NO'
275 #
276 connection slave;
277 SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
278 SET GLOBAL slave_run_triggers_for_rbr= NO;;
279 SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
280 Variable_name Value
281 slave_run_triggers_for_rbr NO
282 connection master;
283 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
284 SET sql_log_bin=0;
285 CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
286 SET sql_log_bin=1;
287 CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
288 connection slave;
289 CREATE TABLE t2 (t1_id INT NOT NULL,
290 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
291 CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
292 ) ENGINE=InnoDB;
293 CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
294 connection master;
295 INSERT INTO t1 VALUES (2),(3);
296 INSERT INTO t2 VALUES (2, default), (3, default);
297 connection slave;
298 #
299 # Count must be 0
300 #
301 include/assert.inc [Table t3 should have zero rows.]
302 connection master;
303 DELETE FROM t1 WHERE id=2;
304 # t1: Should have one row
305 SELECT * FROM t1;
306 id
307 3
308 # t2: Should have two rows
309 SELECT * FROM t2;
310 t1_id v_col
311 2 3
312 3 4
313 connection slave;
314 # t1: Should have one row
315 SELECT * FROM t1;
316 id
317 3
318 # t2: Should have one row on slave due to ON DELETE CASCASE
319 SELECT * FROM t2;
320 t1_id v_col
321 3 4
322 SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
323 connection master;
324 DROP TABLE t3,t2,t1;
325 connection slave;
326 #
327 # Test case 9: Triggers are Foreign Keys are present only on slave and
328 # 'slave_run_triggers_for_rbr=YES'
329 #
330 connection slave;
331 SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
332 SET GLOBAL slave_run_triggers_for_rbr= YES;;
333 SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
334 Variable_name Value
335 slave_run_triggers_for_rbr YES
336 connection master;
337 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
338 SET sql_log_bin=0;
339 CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
340 SET sql_log_bin=1;
341 CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
342 connection slave;
343 CREATE TABLE t2 (t1_id INT NOT NULL,
344 v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
345 CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
346 ) ENGINE=InnoDB;
347 CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
348 connection master;
349 INSERT INTO t1 VALUES (2),(3);
350 INSERT INTO t2 VALUES (2, default), (3, default);
351 connection slave;
352 #
353 # Count must be 2
354 #
355 include/assert.inc [Table t3 should have two rows.]
356 connection master;
357 DELETE FROM t1 WHERE id=2;
358 # t1: Should have one row
359 SELECT * FROM t1;
360 id
361 3
362 # t2: Should have two rows
363 SELECT * FROM t2;
364 t1_id v_col
365 2 3
366 3 4
367 connection slave;
368 # t1: Should have one row
369 SELECT * FROM t1;
370 id
371 3
372 # t2: Should have one row on slave due to ON DELETE CASCASE
373 SELECT * FROM t2;
374 t1_id v_col
375 3 4
376 SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
377 connection master;
378 DROP TABLE t3,t2,t1;
379 connection slave;
380 include/rpl_end.inc