Bug #120251 REPLACE INTO silently bypasses ON DELETE FK rules with innodb_native_foreign_keys=OFF

• Submitted: 12 Apr 11:47
• Modified: 13 Apr 4:58
• Reporter: Yakir Gibraltar (OCA)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 9.6.0
• OS: Any
• CPU Architecture: Any

[12 Apr 11:47] Yakir Gibraltar

Description:
When innodb_native_foreign_keys=OFF (the default since 9.6.0, activating WL#11249 SQL-layer FK enforcement), REPLACE INTO silently bypasses all ON DELETE foreign key rules when a same-PK conflict occurs.

Every rule is affected:
- RESTRICT / NO ACTION: Expected ER_ROW_IS_REFERENCED error -> actual: silent success
- CASCADE: Expected child rows deleted -> actual: child rows untouched
- SET NULL: Expected child FK set to NULL -> actual: child FK unchanged

This is a regression vs innodb_native_foreign_keys=ON, which correctly enforces FK rules for REPLACE INTO (as InnoDB-native mode has since Bug #2418 was fixed in 2004).

A secondary bug causes true orphaned rows: in 3-level CASCADE chains, grandchild rows are not cascade-deleted and end up referencing non-existent parent rows.

Workaround: SET innodb_native_foreign_keys=ON before the statement.

Thank you,
Yakir Gibraltar

How to repeat:
-- Requires innodb_native_foreign_keys=OFF (default in 9.6+)
CREATE TABLE p (id INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE c (id INT PRIMARY KEY, fk INT,
    FOREIGN KEY (fk) REFERENCES p(id) ON DELETE RESTRICT) ENGINE=InnoDB;

INSERT INTO p VALUES (1);
INSERT INTO c VALUES (10, 1);

-- Regular DELETE correctly rejects:
DELETE FROM p WHERE id = 1;  -- ERROR 1451 (correct)

INSERT INTO p VALUES (1);    -- re-add parent

-- REPLACE with same PK bypasses RESTRICT:
REPLACE INTO p VALUES (1);
-- Expected: ERROR 1451 (HA_ERR_ROW_IS_REFERENCED)
-- Actual:   Query OK, 1 row affected -- constraint silently bypassed!

SELECT * FROM p;  -- p(1) still there
SELECT * FROM c;  -- c(10,1) still there, RESTRICT was not enforced

---

-- Secondary bug: CASCADE produces orphaned grandchild rows
CREATE TABLE p  (id INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE c  (id INT PRIMARY KEY, pid INT, KEY(pid),
    FOREIGN KEY (pid) REFERENCES p(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE gc (id INT PRIMARY KEY, cid INT, KEY(cid),
    FOREIGN KEY (cid) REFERENCES c(id) ON DELETE CASCADE) ENGINE=InnoDB;

INSERT INTO p  VALUES (1), (2);
INSERT INTO c  VALUES (10, 1), (20, 2);
INSERT INTO gc VALUES (100, 10), (200, 20);

REPLACE INTO p VALUES (1);

SELECT COUNT(*) FROM c  WHERE id = 10;   -- 1 (expected: 0, cascade skipped)
SELECT COUNT(*) FROM gc WHERE id = 100;  -- 1 (expected: 0, gc(100) is now a true orphan!)