Description:
With the SQL-layer foreign keys (innodb_native_foreign_keys=OFF, the default),
UPDATE IGNORE of a referenced parent key that fails with a duplicate-key error
skips the parent row with warning 1062, but the cascaded child updates for that
row stay. With ON UPDATE CASCADE the child rows now reference a different
parent (the one that already held the new key value). With ON UPDATE SET NULL
they are set to NULL. The parent row is unchanged.
INSERT IGNORE ... ON DUPLICATE KEY UPDATE behaves the same way.
With innodb_native_foreign_keys=ON the children are unchanged. Without IGNORE,
the statement fails with error 1062 and nothing is changed, with both
implementations.
Reproduced on trunk 3b99be40f8a (built from source, RelWithDebInfo) and on
26.7.0.
How to repeat:
CREATE DATABASE d; USE d;
CREATE TABLE p (id INT PRIMARY KEY, k VARCHAR(10) COLLATE utf8mb4_bin,
v INT DEFAULT 0, UNIQUE KEY (k));
CREATE TABLE c (id INT PRIMARY KEY, pk VARCHAR(10) COLLATE utf8mb4_bin,
FOREIGN KEY (pk) REFERENCES p (k) ON UPDATE CASCADE ON DELETE CASCADE);
INSERT INTO p VALUES (1, 'k2', 0), (2, 'q0', 0), (3, 'k3', 0);
INSERT INTO c VALUES (4, 'k2'), (5, 'q0'), (6, 'k3');
UPDATE IGNORE p SET k = 'q0' WHERE id = 1;
SHOW WARNINGS;
SELECT id, k FROM p ORDER BY id;
SELECT id, pk FROM c ORDER BY id;
Observed on trunk (26.10.0):
Query OK, 0 rows affected, 1 warning
Rows matched: 1 Changed: 0 Warnings: 1
Warning 1062 Duplicate entry 'q0' for key 'p.k'
p: (1,'k2'), (2,'q0'), (3,'k3')
c: (4,'q0'), (5,'q0'), (6,'k3') <- child 4 now references parent 2
Expected (and observed with innodb_native_foreign_keys=ON):
p: (1,'k2'), (2,'q0'), (3,'k3')
c: (4,'k2'), (5,'q0'), (6,'k3')
Other statements on the same data (SQL-layer FKs | native FKs):
ON UPDATE SET NULL instead of CASCADE,
UPDATE IGNORE p SET k = 'q0' WHERE id = 1:
0 rows, warning 1062; c 4 = NULL | c 4 = 'k2'
UPDATE IGNORE p SET k = CONCAT('q', id - 1) WHERE id IN (1, 3) ORDER BY id:
1 row (id 3), warning 1062; c 4 = 'q0', c 6 = 'q2' | c 4 = 'k2', c 6 = 'q2'
INSERT IGNORE INTO p VALUES (1, 'x', 0) ON DUPLICATE KEY UPDATE k = 'q0':
0 rows, warning 1062; c 4 = 'q0' | c 4 = 'k2'
UPDATE p SET k = 'q0' WHERE id = 1 (no IGNORE):
ERROR 1062, nothing changed | the same
Suggested fix:
Suggested fix:
In the single-table UPDATE, the SQL-layer FK checks (which run the cascades)
come before the parent row is written:
https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq...
on_update_cascade() updates the child rows there:
https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq...
The parent's ha_update_row() comes afterwards and hits the duplicate key. With
IGNORE the error is downgraded to a warning and the loop goes on to the next
row; nothing undoes the child updates made for the skipped row:
https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq...
INSERT ... ON DUPLICATE KEY UPDATE has the same order:
https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq...
Suggested fix: run the cascades only after the parent row has been written
successfully, or roll back the cascaded child changes (for example with a
row-level savepoint) when the parent row write fails and the error is ignored.
© 2026, Oracle Corporation and/or its affiliates
Description: With the SQL-layer foreign keys (innodb_native_foreign_keys=OFF, the default), UPDATE IGNORE of a referenced parent key that fails with a duplicate-key error skips the parent row with warning 1062, but the cascaded child updates for that row stay. With ON UPDATE CASCADE the child rows now reference a different parent (the one that already held the new key value). With ON UPDATE SET NULL they are set to NULL. The parent row is unchanged. INSERT IGNORE ... ON DUPLICATE KEY UPDATE behaves the same way. With innodb_native_foreign_keys=ON the children are unchanged. Without IGNORE, the statement fails with error 1062 and nothing is changed, with both implementations. Reproduced on trunk 3b99be40f8a (built from source, RelWithDebInfo) and on 26.7.0. How to repeat: CREATE DATABASE d; USE d; CREATE TABLE p (id INT PRIMARY KEY, k VARCHAR(10) COLLATE utf8mb4_bin, v INT DEFAULT 0, UNIQUE KEY (k)); CREATE TABLE c (id INT PRIMARY KEY, pk VARCHAR(10) COLLATE utf8mb4_bin, FOREIGN KEY (pk) REFERENCES p (k) ON UPDATE CASCADE ON DELETE CASCADE); INSERT INTO p VALUES (1, 'k2', 0), (2, 'q0', 0), (3, 'k3', 0); INSERT INTO c VALUES (4, 'k2'), (5, 'q0'), (6, 'k3'); UPDATE IGNORE p SET k = 'q0' WHERE id = 1; SHOW WARNINGS; SELECT id, k FROM p ORDER BY id; SELECT id, pk FROM c ORDER BY id; Observed on trunk (26.10.0): Query OK, 0 rows affected, 1 warning Rows matched: 1 Changed: 0 Warnings: 1 Warning 1062 Duplicate entry 'q0' for key 'p.k' p: (1,'k2'), (2,'q0'), (3,'k3') c: (4,'q0'), (5,'q0'), (6,'k3') <- child 4 now references parent 2 Expected (and observed with innodb_native_foreign_keys=ON): p: (1,'k2'), (2,'q0'), (3,'k3') c: (4,'k2'), (5,'q0'), (6,'k3') Other statements on the same data (SQL-layer FKs | native FKs): ON UPDATE SET NULL instead of CASCADE, UPDATE IGNORE p SET k = 'q0' WHERE id = 1: 0 rows, warning 1062; c 4 = NULL | c 4 = 'k2' UPDATE IGNORE p SET k = CONCAT('q', id - 1) WHERE id IN (1, 3) ORDER BY id: 1 row (id 3), warning 1062; c 4 = 'q0', c 6 = 'q2' | c 4 = 'k2', c 6 = 'q2' INSERT IGNORE INTO p VALUES (1, 'x', 0) ON DUPLICATE KEY UPDATE k = 'q0': 0 rows, warning 1062; c 4 = 'q0' | c 4 = 'k2' UPDATE p SET k = 'q0' WHERE id = 1 (no IGNORE): ERROR 1062, nothing changed | the same Suggested fix: Suggested fix: In the single-table UPDATE, the SQL-layer FK checks (which run the cascades) come before the parent row is written: https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq... on_update_cascade() updates the child rows there: https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq... The parent's ha_update_row() comes afterwards and hits the duplicate key. With IGNORE the error is downgraded to a warning and the loop goes on to the next row; nothing undoes the child updates made for the skipped row: https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq... INSERT ... ON DUPLICATE KEY UPDATE has the same order: https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq... Suggested fix: run the cascades only after the parent row has been written successfully, or roll back the cascaded child changes (for example with a row-level savepoint) when the parent row write fails and the error is ignored.