| 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 |