1 files changed, 344 insertions, 0 deletions
diff --git a/mysql-test/suite/galera/t/MW-369.test b/mysql-test/suite/galera/t/MW-369.test
new file mode 100644
index 00000000..c8f8c974
--- /dev/null
+++ b/mysql-test/suite/galera/t/MW-369.test
@@ -0,0 +1,344 @@
+#
+# Test A Outline:
+# ===============
+#
+# This test tests the scenario for MW-369 where a new child table
+# row referring to parent table row is inserted concurrently from
+# another node while the transaction which tries to delete a
+# referred row from the parent table is committing.
+#
+# The p table will originally have rows (1, 0), (2, 0).
+# The c table will be empty.
+#
+# A new row (1, 1) pointing to parent row (1, 0) is inserted from
+# connection node_2, the transaction which tries to remove the
+# parent row (1, 0) is run from connection node_1.
+#
+# Expected outcome:
+# ================
+#
+# The transaction on node_1 will fail. The parent table will contain
+# rows (1, 0), (2, 0) and the child table will contain row (1, 1).
+#
+
+--source include/galera_cluster.inc
+--source include/have_innodb.inc
+--source include/have_debug_sync.inc
+--source include/galera_have_debug_sync.inc
+
+CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
+CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
+                CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1))  ;
+
+INSERT INTO p VALUES (1, 0);
+INSERT INTO p VALUES (2, 0);
+
+--let $mw_369_parent_query = DELETE FROM p WHERE f1 = 1
+--let $mw_369_child_query = INSERT INTO c VALUES (1, 1)
+
+#
+# we must open connection node_1a here, MW-369.inc will use it later
+#
+--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
+--source MW-369.inc
+
+# Commit fails
+--connection node_1
+--error ER_LOCK_DEADLOCK
+--reap
+
+--connection node_2
+SELECT * FROM p;
+SELECT * FROM c;
+
+DROP TABLE c;
+DROP TABLE p;
+
+#
+# Test B Outline:
+# ===============
+#
+# This test tests the scenario for MW-369 where a existing
+# child table row is updated concurrently from another node
+# with a transaction which updates the parent table.
+#
+# The p table will originally have rows (1, 0), (2, 0).
+# The c table will originally have rows (1, 1, 0) which points
+# to parent table row (1, 0).
+#
+# Expected outcome:
+# ================
+#
+# Both updates should succeed since they are done to separate tables and
+# rows. The parent table will contain rows (1, 1), (2, 0). The child
+# table will contain row (1, 1, 1).
+#
+
+--connection node_1
+CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
+CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
+                f2 INTEGER,
+                CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1))  ;
+
+INSERT INTO p VALUES (1, 0);
+INSERT INTO p VALUES (2, 0);
+INSERT INTO c VALUES (1, 1, 0);
+
+--let mw_369_parent_query = UPDATE p SET f2 = 1 WHERE f1 = 1
+--let $mw_369_child_query = UPDATE c SET f2 = 1 WHERE f1 = 1
+--source MW-369.inc
+
+# Commit succeeds
+--connection node_1
+--reap
+
+--connection node_2
+SELECT * FROM p;
+SELECT * FROM c;
+
+DROP TABLE c;
+DROP TABLE p;
+
+#
+# Test C Outline:
+# ===============
+#
+# This test tests the scenario for MW-369 where a child table row is
+# deleted concurrently from the other node while a transaction updates
+# the parent table referred by the child table row.
+#
+# The p table will originally have rows (1, 0), (2, 0)
+# The c table will originally have row (1, 1) which points to parent
+# table row (1, 0).
+#
+# A row (1, 1) pointing to parent row (1, 0) is deleted from
+# connection node_2, the transaction which tries to update the
+# parent row (1, 0) is run from connection node_1.
+#
+# Expected Outcome:
+# ================
+# Both operations on node_1 and node_2 should succeed without conflicts.
+# The parent table should contain values (1, 1), (2, 0) and the child
+# table should be empty.
+
+--connection node_1
+CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
+CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
+                CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1))  ;
+
+INSERT INTO p VALUES (1, 0);
+INSERT INTO p VALUES (2, 0);
+INSERT INTO c VALUES (1, 1);
+
+--let $mw_369_parent_query = UPDATE p SET f2 = 1 WHERE f1 = 1
+--let $mw_369_child_query = DELETE FROM c WHERE f1 = 1
+--source MW-369.inc
+
+# Commit succeeds
+--connection node_1
+--reap
+
+--connection node_2
+SELECT * FROM p;
+SELECT * FROM c;
+
+DROP TABLE c;
+DROP TABLE p;
+
+
+#
+# Test D Outline:
+# ===============
+#
+# This test is similar to test A, where parent row is deleted while a child row
+# is inserted simultaneously on node 2. However, in this test case the FK
+# constraint's target column is a unique key, and parent row is not delete,
+# but this key value is changed so that insert on node 2 will cause FK
+# violation
+#
+# The p table will originally have rows (1, 0)
+# The c table will originally be empty 
+#
+# in node_1, parent row is updated to value (1,1)
+# A row (1, 0) pointing to the old version of parent row (1, 0) is inserted
+# in connection node_2
+#
+# Expected Outcome:
+# ================
+# This is a true conflict and one transaciton must abort. In this case it is node_1
+# transaction, which was scheduled later.
+#    Parent table should have row (1,0)
+#    child table should have row (1,0)
+#
+
+CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER UNIQUE KEY) ENGINE=INNODB;
+CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
+                CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f2))  ;
+
+INSERT INTO p VALUES (1, 0);
+
+--let $mw_369_parent_query = UPDATE p SET f2 = 1 WHERE f1 = 1
+--let $mw_369_child_query = INSERT INTO c VALUES (1, 0);
+--source MW-369.inc
+
+# Commit fails
+--connection node_1
+--error ER_LOCK_DEADLOCK
+--reap
+
+--connection node_2
+SELECT * FROM p;
+SELECT * FROM c;
+
+DROP TABLE c;
+DROP TABLE p;
+
+#
+# Test E Outline:
+# ===============
+#
+# This test is similar to test B, where parent row is deleted while a child row
+# is updated simultaneously on node 2. However, in this test case the FK
+# constraint has ON DELETE CASCADE option, and the delete on parent row will
+# cascade a delete on child row as well. This will cause true conflict with 
+# connection node_2, which tries to update unrelated column on child table.
+#
+# The p table will originally have rows (1, 0), (2,0)
+# The c table will originally have row (1,1,0)
+#
+# in node_1, parent row (1,0) is deleted and cascaded delete will happen on
+# child table row (1,1,0).
+# in connection node_2 child table row is update to value (1,1,1)
+#
+# Expected Outcome:
+# ================
+# This is a true conflict and one transaciton must abort. In this case it is node_1
+# transaction, which was scheduled later.
+#    Parent table should have rows (1,0), (2,0)
+#    child table should have row (1,1,1)
+#
+
+
+CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
+CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER, f2 INTEGER,
+                CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)
+                ON DELETE CASCADE)  ;
+
+INSERT INTO p VALUES (1, 0);
+INSERT INTO p VALUES (2, 0);
+INSERT INTO c VALUES (1, 1, 0);
+
+--let $mw_369_parent_query = DELETE FROM p WHERE f1 = 1
+--let $mw_369_child_query = UPDATE c SET f2 = 1 WHERE f1 = 1
+--source MW-369.inc
+
+# Commit fails
+--connection node_1
+--error ER_LOCK_DEADLOCK
+--reap
+
+--connection node_2
+SELECT * FROM p;
+SELECT * FROM c;
+
+DROP TABLE c;
+DROP TABLE p;
+
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+#
+# Test F Outline:
+# ===============
+#
+# Test two concurrent INSERTs on the child table.
+#
+# The pf table will originally have row (1)
+# The cf table will originally be empty
+#
+# A new row (10, 1) pointing to parent row (1) is inserted from
+# connection node_2. A transaction which tries to INSERT another child
+# row (20, 1), pointing to the same parent, is run from connection node_1.
+#
+# Expected Outcome:
+# =================
+# Both INSERTs should succeed since they don't modify the common parent
+# key.
+#
+# At the end of the test:
+#    parent table should have row (1)
+#    child table should have rows (10, 1), (20, 1)
+
+--connection node_1
+
+CREATE TABLE pf (f1 INTEGER PRIMARY KEY) ENGINE=INNODB;
+CREATE TABLE cf (
+  f1 INTEGER PRIMARY KEY,
+  p_id INTEGER,
+  CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES pf (f1)
+);
+
+INSERT INTO pf VALUES (1);
+
+# This is run on node1:
+--let $mw_369_parent_query = INSERT INTO cf (f1, p_id) VALUES (10, 1)
+# This is run on node2:
+--let $mw_369_child_query = INSERT INTO cf (f1, p_id) VALUES (20, 1)
+--source MW-369.inc
+
+--connection node_1
+--reap
+
+--connection node_2
+SELECT * FROM pf;
+SELECT * FROM cf;
+
+DROP TABLE cf;
+DROP TABLE pf;
+
+#
+# Test G Outline:
+# ===============
+#
+# This test is similar to test B where a existing
+# child table row is updated concurrently from another node
+# with a transaction which updates the parent table, except
+# that here the child table row is inserted, not updated.
+#
+# The pg table will originally have rows (1, 0), (2, 0).
+# The cg table will originally be empty
+#
+# Expected outcome:
+# ================
+#
+# Both UPDATE and INSERT should succeed since they are done to separate tables
+# and UPDATE to parent row does not touch the foreign key referenced by the
+# child row INSERT. The parent table shall contain rows (1, 1), (2, 0).
+# The child table shall contain row (1, 1, 0) which points to parent table
+# row (1, 0).
+#
+
+--connection node_1
+CREATE TABLE pg (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
+CREATE TABLE cg (f1 INTEGER PRIMARY KEY, p_id INTEGER,
+                 f2 INTEGER,
+                 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES pg (f1))  ;
+
+INSERT INTO pg VALUES (1, 0);
+INSERT INTO pg VALUES (2, 0);
+
+--let mw_369_parent_query = UPDATE pg SET f2 = 1 WHERE f1 = 1
+--let $mw_369_child_query = INSERT INTO cg VALUES (1, 1, 0)
+--source MW-369.inc
+
+# Commit succeeds
+--connection node_1
+--reap
+
+--connection node_2
+SELECT * FROM pg;
+SELECT * FROM cg;
+
+DROP TABLE cg;
+DROP TABLE pg;
+