mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 09:55:33 +01:00
608b0ee52e
Problem:
=======
Upon deleting or updating a row in a parent table (with primary key), if
the child table has virtual column and an associated key with ON UPDATE
CASCADE/ON DELETE CASCADE, it will result in slave crash.
Analysis:
========
Tables which are related through foreign key require prelocking similar to
triggers. i.e If a table has triggers/foreign keys we should add all tables
and routines used by them to the prelocking set. This prelocking happens
during 'open_and_lock_tables' call. Each table being opened is checked for
foreign key references. If foreign key reference exists then the child
table is opened and it is linked to the table_list. Upon any modification
to parent table its corresponding child tables are retried from table_list
and they are updated accordingly. This prelocking work fine on master.
On slave prelocking works for following cases.
- Statement/mixed based replication
- In row based replication when trigger execution is enabled through
'slave_run_triggers_for_rbr=YES/LOGGING/ENFORCE'
Otherwise it results in an assert/crash, as the parent table will not find
the corresponding child table and it will be NULL. Dereferencing NULL
pointer leads to slave server exit.
Fix:
===
Introduce a new 'slave_fk_event_map' flag similar to 'trg_event_map'. This
flag will ensure that when foreign key is enabled in row based replication
all the parent and child tables are prelocked, so that parent is able to
locate the child table.
Note: This issue is specific to slave, hence only slave needs to be
upgraded.
380 lines
12 KiB
Text
380 lines
12 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
#
|
|
# Test case 1: KEY on a virtual column with ON DELETE CASCADE
|
|
#
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY,
|
|
t1_id INT NOT NULL,
|
|
v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
|
|
CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES (90,1,NULL);
|
|
INSERT INTO t2 VALUES (91,2,default);
|
|
DELETE FROM t1 WHERE id=1;
|
|
connection slave;
|
|
#
|
|
# Verify data consistency on slave
|
|
#
|
|
include/diff_tables.inc [master:test.t1, slave:test.t1]
|
|
include/diff_tables.inc [master:test.t2, slave:test.t2]
|
|
connection master;
|
|
DROP TABLE t2,t1;
|
|
connection slave;
|
|
#
|
|
# Test case 2: Verify "ON DELETE CASCADE" for parent->child->child scenario
|
|
# Parent table: users
|
|
# Child tables: matchmaking_groups, matchmaking_group_users
|
|
# Parent table: matchmaking_groups
|
|
# Child tables: matchmaking_group_users, matchmaking_group_maps
|
|
#
|
|
# Deleting a row from parent table should be reflected in
|
|
# child tables.
|
|
# matchmaking_groups->matchmaking_group_users->matchmaking_group_maps
|
|
# users->matchmaking_group_users->matchmaking_group_maps
|
|
#
|
|
connection master;
|
|
CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(32) NOT NULL DEFAULT ''
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
CREATE TABLE matchmaking_groups (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
host_user_id INT UNSIGNED NOT NULL UNIQUE,
|
|
v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col),
|
|
CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
CREATE TABLE matchmaking_group_users (
|
|
matchmaking_group_id BIGINT UNSIGNED NOT NULL,
|
|
user_id INT UNSIGNED NOT NULL,
|
|
v_col1 int as (user_id+1) virtual, KEY (v_col1),
|
|
PRIMARY KEY (matchmaking_group_id,user_id),
|
|
UNIQUE KEY user_id (user_id),
|
|
CONSTRAINT FOREIGN KEY (matchmaking_group_id)
|
|
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT FOREIGN KEY (user_id)
|
|
REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
CREATE TABLE matchmaking_group_maps (
|
|
matchmaking_group_id BIGINT UNSIGNED NOT NULL,
|
|
map_id TINYINT UNSIGNED NOT NULL,
|
|
v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2),
|
|
PRIMARY KEY (matchmaking_group_id,map_id),
|
|
CONSTRAINT FOREIGN KEY (matchmaking_group_id)
|
|
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
connection slave;
|
|
connection master;
|
|
INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
|
|
INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default);
|
|
INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default);
|
|
INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default);
|
|
DELETE FROM matchmaking_groups WHERE id = 10;
|
|
connection slave;
|
|
#
|
|
# No rows should be returned as ON DELETE CASCASE should have removed
|
|
# corresponding rows from child tables. There should not any mismatch
|
|
# of 'id' field between parent->child.
|
|
#
|
|
SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
|
|
matchmaking_group_id user_id v_col1
|
|
SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
|
|
matchmaking_group_id map_id v_col2
|
|
#
|
|
# Rows with id=11 should be present
|
|
#
|
|
SELECT * FROM matchmaking_group_users;
|
|
matchmaking_group_id user_id v_col1
|
|
11 2 3
|
|
SELECT * FROM matchmaking_group_maps;
|
|
matchmaking_group_id map_id v_col2
|
|
11 66 67
|
|
connection master;
|
|
DELETE FROM users WHERE id = 2;
|
|
connection slave;
|
|
#
|
|
# No rows should be present in both the child tables
|
|
#
|
|
SELECT * FROM matchmaking_group_users;
|
|
matchmaking_group_id user_id v_col1
|
|
SELECT * FROM matchmaking_group_maps;
|
|
matchmaking_group_id map_id v_col2
|
|
connection master;
|
|
DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
|
|
connection slave;
|
|
#
|
|
# Test case 3: KEY on a virtual column with ON UPDATE CASCADE
|
|
#
|
|
connection master;
|
|
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1, 80);
|
|
CREATE TABLE t2 (a INT KEY, b INT,
|
|
v_col int as (b+1) virtual, KEY (v_col),
|
|
CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES (51, 1, default);
|
|
connection slave;
|
|
connection master;
|
|
UPDATE t1 SET a = 50 WHERE a = 1;
|
|
#
|
|
# Master: Verify that ON UPDATE CASCADE works fine
|
|
# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
|
|
#
|
|
SELECT * FROM t2 WHERE b=50;
|
|
a b v_col
|
|
51 50 51
|
|
connection slave;
|
|
#
|
|
# Slave: Verify that ON UPDATE CASCADE works fine
|
|
# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
|
|
#
|
|
SELECT * FROM t2 WHERE b=50;
|
|
a b v_col
|
|
51 50 51
|
|
connection master;
|
|
DROP TABLE t2, t1;
|
|
connection slave;
|
|
#
|
|
# Test case 4: Define triggers on master, their results should be
|
|
# replicated as part of row events and they should be
|
|
# applied on slave with the default
|
|
# slave_run_triggers_for_rbr=NO
|
|
#
|
|
connection master;
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB;
|
|
CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1);
|
|
INSERT INTO t1 VALUES (2),(3);
|
|
connection slave;
|
|
SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr';
|
|
Variable_name Value
|
|
slave_run_triggers_for_rbr NO
|
|
#
|
|
# As two rows are inserted in table 't1', two rows should get inserted
|
|
# into table 't2' as part of trigger.
|
|
#
|
|
include/assert.inc [Table t2 should have two rows.]
|
|
connection master;
|
|
DROP TABLE t1,t2;
|
|
connection slave;
|
|
#
|
|
# Test case 5: Define triggers + Foreign Keys on master, their results
|
|
# should be replicated as part of row events and master
|
|
# and slave should be in sync.
|
|
#
|
|
connection master;
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (t1_id INT NOT NULL,
|
|
v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
|
|
CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
|
|
CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1);
|
|
INSERT INTO t1 VALUES (2),(3);
|
|
INSERT INTO t2 VALUES (2, default), (3, default);
|
|
connection slave;
|
|
#
|
|
# As two rows are inserted in table 't1', two rows should get inserted
|
|
# into table 't3' as part of trigger.
|
|
#
|
|
include/assert.inc [Table t3 should have two rows.]
|
|
#
|
|
# Verify ON DELETE CASCASE correctness
|
|
#
|
|
connection master;
|
|
DELETE FROM t1 WHERE id=2;
|
|
connection slave;
|
|
connection master;
|
|
include/diff_tables.inc [master:test.t1, slave:test.t1]
|
|
include/diff_tables.inc [master:test.t2, slave:test.t2]
|
|
include/diff_tables.inc [master:test.t3, slave:test.t3]
|
|
DROP TABLE t3,t2,t1;
|
|
connection slave;
|
|
#
|
|
# Test case 6: Triggers are present only on slave and
|
|
# 'slave_run_triggers_for_rbr=NO'
|
|
#
|
|
connection slave;
|
|
SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
|
|
SET GLOBAL slave_run_triggers_for_rbr= NO;;
|
|
SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
|
|
Variable_name Value
|
|
slave_run_triggers_for_rbr NO
|
|
connection master;
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (t1_id INT NOT NULL,
|
|
v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
|
|
KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
|
|
connection slave;
|
|
CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
|
|
connection master;
|
|
INSERT INTO t1 VALUES (2),(3);
|
|
INSERT INTO t2 VALUES (2, default), (3, default);
|
|
connection slave;
|
|
#
|
|
# Count must be 0
|
|
#
|
|
include/assert.inc [Table t3 should have zero rows.]
|
|
connection master;
|
|
DELETE FROM t1 WHERE id=2;
|
|
connection slave;
|
|
SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
|
|
#
|
|
# Verify t1, t2 are consistent on slave.
|
|
#
|
|
include/diff_tables.inc [master:test.t1, slave:test.t1]
|
|
include/diff_tables.inc [master:test.t2, slave:test.t2]
|
|
connection master;
|
|
DROP TABLE t3,t2,t1;
|
|
connection slave;
|
|
#
|
|
# Test case 7: Triggers are present only on slave and
|
|
# 'slave_run_triggers_for_rbr=YES'
|
|
#
|
|
connection slave;
|
|
SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
|
|
SET GLOBAL slave_run_triggers_for_rbr= YES;;
|
|
SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
|
|
Variable_name Value
|
|
slave_run_triggers_for_rbr YES
|
|
connection master;
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (t1_id INT NOT NULL,
|
|
v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
|
|
KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
|
|
connection slave;
|
|
CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
|
|
connection master;
|
|
INSERT INTO t1 VALUES (2),(3);
|
|
INSERT INTO t2 VALUES (2, default), (3, default);
|
|
connection slave;
|
|
#
|
|
# Count must be 2
|
|
#
|
|
include/assert.inc [Table t3 should have two rows.]
|
|
connection master;
|
|
DELETE FROM t1 WHERE id=2;
|
|
connection slave;
|
|
SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
|
|
#
|
|
# Verify t1, t2 are consistent on slave.
|
|
#
|
|
include/diff_tables.inc [master:test.t1, slave:test.t1]
|
|
include/diff_tables.inc [master:test.t2, slave:test.t2]
|
|
connection master;
|
|
DROP TABLE t3,t2,t1;
|
|
connection slave;
|
|
#
|
|
# Test case 8: Triggers and Foreign Keys are present only on slave and
|
|
# 'slave_run_triggers_for_rbr=NO'
|
|
#
|
|
connection slave;
|
|
SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
|
|
SET GLOBAL slave_run_triggers_for_rbr= NO;;
|
|
SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
|
|
Variable_name Value
|
|
slave_run_triggers_for_rbr NO
|
|
connection master;
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
SET sql_log_bin=0;
|
|
CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
|
|
SET sql_log_bin=1;
|
|
CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
|
|
connection slave;
|
|
CREATE TABLE t2 (t1_id INT NOT NULL,
|
|
v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
|
|
CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
|
|
connection master;
|
|
INSERT INTO t1 VALUES (2),(3);
|
|
INSERT INTO t2 VALUES (2, default), (3, default);
|
|
connection slave;
|
|
#
|
|
# Count must be 0
|
|
#
|
|
include/assert.inc [Table t3 should have zero rows.]
|
|
connection master;
|
|
DELETE FROM t1 WHERE id=2;
|
|
# t1: Should have one row
|
|
SELECT * FROM t1;
|
|
id
|
|
3
|
|
# t2: Should have two rows
|
|
SELECT * FROM t2;
|
|
t1_id v_col
|
|
2 3
|
|
3 4
|
|
connection slave;
|
|
# t1: Should have one row
|
|
SELECT * FROM t1;
|
|
id
|
|
3
|
|
# t2: Should have one row on slave due to ON DELETE CASCASE
|
|
SELECT * FROM t2;
|
|
t1_id v_col
|
|
3 4
|
|
SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
|
|
connection master;
|
|
DROP TABLE t3,t2,t1;
|
|
connection slave;
|
|
#
|
|
# Test case 9: Triggers are Foreign Keys are present only on slave and
|
|
# 'slave_run_triggers_for_rbr=YES'
|
|
#
|
|
connection slave;
|
|
SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
|
|
SET GLOBAL slave_run_triggers_for_rbr= YES;;
|
|
SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
|
|
Variable_name Value
|
|
slave_run_triggers_for_rbr YES
|
|
connection master;
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
SET sql_log_bin=0;
|
|
CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
|
|
SET sql_log_bin=1;
|
|
CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
|
|
connection slave;
|
|
CREATE TABLE t2 (t1_id INT NOT NULL,
|
|
v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
|
|
CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
|
|
connection master;
|
|
INSERT INTO t1 VALUES (2),(3);
|
|
INSERT INTO t2 VALUES (2, default), (3, default);
|
|
connection slave;
|
|
#
|
|
# Count must be 2
|
|
#
|
|
include/assert.inc [Table t3 should have two rows.]
|
|
connection master;
|
|
DELETE FROM t1 WHERE id=2;
|
|
# t1: Should have one row
|
|
SELECT * FROM t1;
|
|
id
|
|
3
|
|
# t2: Should have two rows
|
|
SELECT * FROM t2;
|
|
t1_id v_col
|
|
2 3
|
|
3 4
|
|
connection slave;
|
|
# t1: Should have one row
|
|
SELECT * FROM t1;
|
|
id
|
|
3
|
|
# t2: Should have one row on slave due to ON DELETE CASCASE
|
|
SELECT * FROM t2;
|
|
t1_id v_col
|
|
3 4
|
|
SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
|
|
connection master;
|
|
DROP TABLE t3,t2,t1;
|
|
connection slave;
|
|
include/rpl_end.inc
|