mirror of
https://github.com/MariaDB/server.git
synced 2025-02-09 23:24:11 +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>
256 lines
7.2 KiB
Text
256 lines
7.2 KiB
Text
#
|
|
# Test Foreign Key Cascading DELETEs
|
|
#
|
|
|
|
--source include/galera_cluster.inc
|
|
|
|
--echo #
|
|
--echo # test phase with cascading foreign key through 3 tables
|
|
--echo #
|
|
|
|
--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
|
|
--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'grandparent'
|
|
--source include/wait_condition.inc
|
|
--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'parent'
|
|
--source include/wait_condition.inc
|
|
--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'child'
|
|
--source include/wait_condition.inc
|
|
--let $wait_condition = SELECT COUNT(*) = 2 FROM child;
|
|
--source include/wait_condition.inc
|
|
--let $wait_condition = SELECT COUNT(*) = 2 FROM parent;
|
|
--source include/wait_condition.inc
|
|
--let $wait_condition = SELECT COUNT(*) = 2 FROM grandparent;
|
|
--source include/wait_condition.inc
|
|
DELETE FROM grandparent WHERE id = 1;
|
|
|
|
SELECT * FROM grandparent;
|
|
SELECT * FROM parent;
|
|
SELECT * FROM child;
|
|
|
|
--connection node_1
|
|
--let $wait_condition = SELECT COUNT(*) = 1 FROM child;
|
|
--source include/wait_condition.inc
|
|
--let $wait_condition = SELECT COUNT(*) = 1 FROM parent;
|
|
--source include/wait_condition.inc
|
|
--let $wait_condition = SELECT COUNT(*) = 1 FROM grandparent;
|
|
--source include/wait_condition.inc
|
|
|
|
SELECT * FROM grandparent;
|
|
SELECT * FROM parent;
|
|
SELECT * FROM child;
|
|
|
|
DROP TABLE child;
|
|
DROP TABLE parent;
|
|
DROP TABLE grandparent;
|
|
|
|
--echo
|
|
--echo Scenario 2, testing PA applying with FK cascade delete
|
|
--echo
|
|
|
|
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);
|
|
|
|
--let $count = 100
|
|
--disable_query_log
|
|
while ($count)
|
|
{
|
|
--eval INSERT INTO p1 VALUES ($count, 0);
|
|
--eval INSERT INTO p2 VALUES ($count, 0);
|
|
--eval INSERT INTO c VALUES ($count, $count, $count, 0);
|
|
--dec $count
|
|
}
|
|
|
|
--connection node_2
|
|
set global wsrep_slave_threads=2;
|
|
|
|
--connection node_1
|
|
--let $count = 100
|
|
while ($count)
|
|
{
|
|
--eval DELETE FROM p2 WHERE f1=$count;
|
|
--eval DELETE FROM p1 WHERE f1=$count;
|
|
|
|
--dec $count
|
|
}
|
|
--enable_query_log
|
|
|
|
--connection node_2
|
|
set global wsrep_slave_threads=DEFAULT;
|
|
|
|
|
|
SELECT * FROM p1;
|
|
SELECT * FROM p2;
|
|
SELECT * FROM c;
|
|
|
|
--connection node_1
|
|
DROP TABLE c;
|
|
DROP TABLE p1,p2;
|
|
|
|
--echo
|
|
--echo Scenario 4, testing PA applying with FK cascade delete on
|
|
--echo more than one level
|
|
--echo
|
|
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;
|
|
|
|
--let $count = 100
|
|
--disable_query_log
|
|
while ($count)
|
|
{
|
|
--eval INSERT INTO gp1 VALUES ($count, 0);
|
|
--eval INSERT INTO gp2 VALUES ($count, 0);
|
|
--eval INSERT INTO p1 VALUES ($count, $count, $count, 0);
|
|
--eval INSERT INTO p2 VALUES ($count, $count, $count, 0);
|
|
--eval INSERT INTO c VALUES ($count, $count, $count, 0);
|
|
--dec $count
|
|
}
|
|
|
|
--connection node_2
|
|
set global wsrep_slave_threads=2;
|
|
|
|
--connection node_1
|
|
--let $count = 100
|
|
while ($count)
|
|
{
|
|
--eval DELETE FROM gp1 WHERE f1=$count;
|
|
--eval DELETE FROM gp2 WHERE f1=$count;
|
|
|
|
--dec $count
|
|
}
|
|
--enable_query_log
|
|
|
|
--connection node_2
|
|
set global wsrep_slave_threads=DEFAULT;
|
|
|
|
SELECT * FROM gp1;
|
|
SELECT * FROM gp2;
|
|
SELECT * FROM p1;
|
|
SELECT * FROM p2;
|
|
SELECT * FROM c;
|
|
|
|
--connection node_1
|
|
DROP TABLE c;
|
|
DROP TABLE p1,p2;
|
|
DROP TABLE gp1,gp2;
|
|
|
|
--echo
|
|
--echo Scenario 3, testing PA applying with FK cascade delete on
|
|
--echo more than one level in a diamond topology
|
|
--echo
|
|
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;
|
|
|
|
--let $count = 100
|
|
--disable_query_log
|
|
while ($count)
|
|
{
|
|
--eval INSERT INTO ggp1 VALUES ($count, 0);
|
|
--eval INSERT INTO gp1 VALUES ($count, $count, $count, 0);
|
|
--eval INSERT INTO gp2 VALUES ($count, $count, $count, 0);
|
|
--eval INSERT INTO p1 VALUES ($count, $count, $count, 0);
|
|
--eval INSERT INTO p2 VALUES ($count, $count, $count, 0);
|
|
--eval INSERT INTO c VALUES ($count, $count, $count, 0);
|
|
--dec $count
|
|
}
|
|
|
|
--connection node_2
|
|
set global wsrep_slave_threads=2;
|
|
|
|
--connection node_1
|
|
--let $count = 100
|
|
while ($count)
|
|
{
|
|
--eval DELETE FROM ggp1 WHERE f1=$count;
|
|
|
|
--dec $count
|
|
}
|
|
--enable_query_log
|
|
|
|
--connection node_2
|
|
set global wsrep_slave_threads=DEFAULT;
|
|
|
|
SELECT * FROM ggp1;
|
|
SELECT * FROM gp2;
|
|
SELECT * FROM gp1;
|
|
SELECT * FROM p1;
|
|
SELECT * FROM p2;
|
|
SELECT * FROM c;
|
|
|
|
--connection node_1
|
|
DROP TABLE c;
|
|
DROP TABLE p1,p2;
|
|
DROP TABLE gp1,gp2;
|
|
DROP TABLE ggp1;
|