Bug #121535 Server crash on DML checking a foreign key that references a non-unique/partial key

• Submitted: 16 Oct 16:53
• Reporter: Akshat Nehra (OCA)
• Status: Open
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 9.7.2
• OS: Any
• CPU Architecture: Any
• Tags: 9.7 LTS

[16 Oct 16:53] Akshat Nehra

Description:
When a foreign key references a non-standard key (non-unique or partial key, allowed with restrict_fk_on_non_standard_key=OFF), DML that checks the foreign key crashes the server. This happens with the default SQL-layer foreign key implementation (innodb_native_foreign_keys=OFF).

The problem is serious for upgrades. Such foreign keys are still supported when upgrading from 8.0 or 8.4 (the server logs only warning MY-015129, "refers to non-unique key or partial key"). After an in-place upgrade from 8.4.11 to 9.7.2, every one of these crashes the server:
- INSERT or UPDATE on the child table, including valid values
- DELETE or UPDATE on the parent table, including rows not referenced by the child

SELECT, DELETE on the child, and inserting NULL foreign key values do not crash. 8.4.11 handles the same statements correctly: they succeed, or fail with ER_NO_REFERENCED_ROW_2 (1452) or ER_ROW_IS_REFERENCED_2 (1451). With innodb_native_foreign_keys=ON, 9.7.2 also behaves correctly.

How to repeat:
A) Fresh 9.7.2 server:
CREATE DATABASE test; USE test;
CREATE TABLE parent (a INT PRIMARY KEY, b INT, KEY(b));
SET SESSION restrict_fk_on_non_standard_key = OFF;
CREATE TABLE child (i INT, j INT, FOREIGN KEY (i, j) REFERENCES parent (b, a));
INSERT INTO parent VALUES (1,10);
INSERT INTO child VALUES (99,99);   -- expected ER_NO_REFERENCED_ROW_2; server crashes

B) Upgrade from 8.4.11:
1. On 8.4.11, run the same CREATE statements, then INSERT INTO parent VALUES (1,10),(2,20),(3,30); INSERT INTO child VALUES (10,1);.
2. Shut down cleanly with innodb_fast_shutdown=0.
3. Start 9.7.2 on the same datadir. The upgrade completes, and the error log shows warnings MY-015129 and MY-014070.
4. Run any of these. Each crashes the server:
INSERT INTO test.child VALUES (10,1);               -- valid row
UPDATE test.child SET i=20, j=2 WHERE i=10;
DELETE FROM test.parent WHERE a=1;                  -- referenced row
DELETE FROM test.parent WHERE a=3;                  -- unreferenced row
UPDATE test.parent SET b=11 WHERE a=1;

Workaround: start the server with --innodb-native-foreign-keys=ON