mirror of
https://github.com/MariaDB/server.git
synced 2025-02-14 09:25:35 +01:00
c1846c4fcf
This commit has a mtr test where two two transactions delete a row from two separate tables, which will cascade a FK delete for the same row in a third table. Second replica node is configured with 2 applier threads, and the test will fail if these two transactions are applied in parallel. The actual fix, in this commit, is to mark a transaction as unsafe for parallel applying when it traverses into cascade delete operation. Reviewed-by: Jan Lindström <jan.lindstrom@mariadb.com>
160 lines
4.1 KiB
Text
160 lines
4.1 KiB
Text
connection node_2;
|
|
connection node_1;
|
|
#
|
|
# test phase with cascading foreign key through 3 tables
|
|
#
|
|
connection node_1;
|
|
CREATE TABLE grandparent (
|
|
id INT NOT NULL PRIMARY KEY
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE parent (
|
|
id INT NOT NULL PRIMARY KEY,
|
|
grandparent_id INT,
|
|
FOREIGN KEY (grandparent_id)
|
|
REFERENCES grandparent(id)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE child (
|
|
id INT NOT NULL PRIMARY KEY,
|
|
parent_id INT,
|
|
FOREIGN KEY (parent_id)
|
|
REFERENCES parent(id)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO grandparent VALUES (1),(2);
|
|
INSERT INTO parent VALUES (1,1), (2,2);
|
|
INSERT INTO child VALUES (1,1), (2,2);
|
|
connection node_2;
|
|
DELETE FROM grandparent WHERE id = 1;
|
|
SELECT * FROM grandparent;
|
|
id
|
|
2
|
|
SELECT * FROM parent;
|
|
id grandparent_id
|
|
2 2
|
|
SELECT * FROM child;
|
|
id parent_id
|
|
2 2
|
|
connection node_1;
|
|
SELECT * FROM grandparent;
|
|
id
|
|
2
|
|
SELECT * FROM parent;
|
|
id grandparent_id
|
|
2 2
|
|
SELECT * FROM child;
|
|
id parent_id
|
|
2 2
|
|
DROP TABLE child;
|
|
DROP TABLE parent;
|
|
DROP TABLE grandparent;
|
|
|
|
Scenario 2, testing PA applying with FK cascade delete
|
|
|
|
CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
|
|
CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
|
|
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
|
|
ON DELETE CASCADE);
|
|
connection node_2;
|
|
set global wsrep_slave_threads=DEFAULT;
|
|
SELECT * FROM p1;
|
|
f1 f2
|
|
SELECT * FROM p2;
|
|
f1 f2
|
|
SELECT * FROM c;
|
|
f1 p1_id p2_id f2
|
|
connection node_1;
|
|
DROP TABLE c;
|
|
DROP TABLE p1,p2;
|
|
|
|
Scenario 4, testing PA applying with FK cascade delete on
|
|
more than one level
|
|
|
|
CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
|
|
CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
|
|
CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1)
|
|
ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1)
|
|
ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
|
|
ON DELETE CASCADE) ENGINE=INNODB;
|
|
connection node_2;
|
|
set global wsrep_slave_threads=DEFAULT;
|
|
SELECT * FROM gp1;
|
|
f1 f2
|
|
SELECT * FROM gp2;
|
|
f1 f2
|
|
SELECT * FROM p1;
|
|
f1 p1_id p2_id f2
|
|
SELECT * FROM p2;
|
|
f1 p1_id p2_id f2
|
|
SELECT * FROM c;
|
|
f1 p1_id p2_id f2
|
|
connection node_1;
|
|
DROP TABLE c;
|
|
DROP TABLE p1,p2;
|
|
DROP TABLE gp1,gp2;
|
|
|
|
Scenario 3, testing PA applying with FK cascade delete on
|
|
more than one level in a diamond topology
|
|
|
|
CREATE TABLE ggp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
|
|
CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT pfk_6 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1)
|
|
ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT pfk_5 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1)
|
|
ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1)
|
|
ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1)
|
|
ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
|
|
ON DELETE CASCADE) ENGINE=INNODB;
|
|
connection node_2;
|
|
set global wsrep_slave_threads=DEFAULT;
|
|
SELECT * FROM ggp1;
|
|
f1 f2
|
|
SELECT * FROM gp2;
|
|
f1 p1_id p2_id f2
|
|
SELECT * FROM gp1;
|
|
f1 p1_id p2_id f2
|
|
SELECT * FROM p1;
|
|
f1 p1_id p2_id f2
|
|
SELECT * FROM p2;
|
|
f1 p1_id p2_id f2
|
|
SELECT * FROM c;
|
|
f1 p1_id p2_id f2
|
|
connection node_1;
|
|
DROP TABLE c;
|
|
DROP TABLE p1,p2;
|
|
DROP TABLE gp1,gp2;
|
|
DROP TABLE ggp1;
|