Description:
With the SQL-layer foreign keys (innodb_native_foreign_keys=OFF, the default),
an UPDATE of a referenced parent key with ON UPDATE CASCADE is silently
skipped when a child row already holds exactly the new value. This happens when
the old and new key are equal under the column's collation (case, accents, or
trailing spaces with a PAD SPACE collation): the child row then references the
parent both before and after. The statement returns "0 rows affected", no
warning, and the whole row update is lost, including other columns assigned in
the same statement. Other child rows of the same parent are not cascaded either.
With innodb_native_foreign_keys=ON, the same statement updates the row.
Under row-based replication the same condition is reached on every replica for
every collation-equal key change with ON UPDATE CASCADE, also when on the
source the child held the old value and the statement worked. The source logs
the cascaded child change first (with NO_FOREIGN_KEY_CHECKS_F) and then the
parent change. The replica applies the child event, so the child now holds the
new value, and the parent event then fails with error 1451. The replica stops,
and replaying the binary log with mysqlbinlog | mysql stops at the same event.
Reproduced on trunk 3b99be40f8a (built from source, RelWithDebInfo) and on
26.7.0.
How to repeat:
1) Single server, default settings:
CREATE DATABASE d; USE d;
CREATE TABLE p (id INT PRIMARY KEY, k VARCHAR(20) COLLATE utf8mb4_0900_ai_ci,
v INT DEFAULT 0, UNIQUE KEY (k));
CREATE TABLE c (id INT PRIMARY KEY, pk VARCHAR(20) COLLATE utf8mb4_0900_ai_ci,
FOREIGN KEY (pk) REFERENCES p (k) ON UPDATE CASCADE ON DELETE CASCADE);
INSERT INTO p VALUES (1, 'c0', 0);
INSERT INTO c VALUES (1, 'C0');
UPDATE p SET k = 'C0', v = v + 1 WHERE id = 1;
SELECT k, v FROM p;
Observed on trunk (26.10.0):
Query OK, 0 rows affected
Rows matched: 1 Changed: 0 Warnings: 0
p: 'c0', 0
Expected (and observed with innodb_native_foreign_keys=ON):
Query OK, 1 row affected
p: 'C0', 1
Same table definitions, other data (SQL-layer FKs | native FKs):
children 'C0' and 'c0', k 'c0' -> 'C0': 0 rows, p 'c0', children 'C0','c0'
| 1 row, p 'C0', children 'C0','C0'
utf8mb4_0900_ai_ci, k 'kéy' -> 'key', child 'key': 0 rows, p 'kéy' | 1 row
utf8mb4_general_ci, k 'k1' -> 'k1 ', child 'k1 ': 0 rows, p 'k1' | 1 row
child 'c0', k 'c0' -> 'C0': 1 row in both
no child, k 'c0' -> 'C0': 1 row in both
child 'c0', k 'c0' -> 'c9': 1 row in both
2) Replication. Two servers, both with
--log-bin=binlog --gtid-mode=ON --enforce-gtid-consistency=ON
and --server-id=1 on the source, --server-id=2 on the replica,
and on the replica:
CHANGE REPLICATION SOURCE TO SOURCE_HOST='127.0.0.1', SOURCE_PORT=<source port>,
SOURCE_USER='root', SOURCE_PASSWORD='', SOURCE_AUTO_POSITION=1, GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
On the source:
FLUSH BINARY LOGS;
CREATE DATABASE d;
CREATE TABLE d.p (id INT PRIMARY KEY, k VARCHAR(30), UNIQUE KEY (k));
CREATE TABLE d.c (cid INT PRIMARY KEY, pk VARCHAR(30),
FOREIGN KEY (pk) REFERENCES d.p (k) ON UPDATE CASCADE ON DELETE CASCADE);
INSERT INTO d.p VALUES (1, 'Key42');
INSERT INTO d.c VALUES (1, 'Key42');
UPDATE d.p SET k = 'key42' WHERE id = 1;
INSERT INTO d.p VALUES (3, 'after');
FLUSH BINARY LOGS;
Observed on trunk (26.10.0):
source : p (1,'key42'), (3,'after'); c (1,'key42')
replica: p (1,'Key42'); c (1,'Key42')
replica error: 1451 Worker 1 failed executing transaction '...:6' at source
log binlog.000002, end_log_pos 1810; Could not execute Update_rows event on
table d.p; Cannot delete or update a parent row: a foreign key constraint
fails (), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED
mysqlbinlog <source binlog> | mysql (fresh server):
ERROR 1451 (23000) at line 148: Cannot delete or update a parent row: a
foreign key constraint fails ()
restored p: (1,'Key42')
The same happens for 'kéy42' -> 'key42' (utf8mb4_0900_ai_ci), for 'key42' ->
'key42 ' (utf8mb4_general_ci), and one level down (a grandchild referencing a
child key that changes case). Applied correctly: 'key42' -> 'key43',
'Key42' -> 'key42' under utf8mb4_0900_as_cs, and the case change with
innodb_native_foreign_keys=ON on both servers.
Expected: the replica applies the event and ends with the source's rows.
Suggested fix:
Suggested fix:
is_column_updated() compares the key bytes, so a collation-equal change is a
key change and is cascaded:
https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq...
on_update_cascade() finds the child rows by the old key (under the collation)
and copies the new parent value into them. For a child that already holds those
bytes, InnoDB's update_row returns HA_ERR_RECORD_IS_THE_SAME
(storage/innobase/handler/ha_innodb.cc, line 10176). on_update_cascade breaks
out of the loop on any non-zero result of ha_update_row and sets *error = 1
without raising an error:
https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq...
check_child_fk_ref() then returns true (line 1561), and the single-table UPDATE
treats "true without an error" like a row skipped by IGNORE:
https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq...
On the replica, Update_rows_log_event maps the same result to
HA_ERR_ROW_IS_REFERENCED:
https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/lo...
(The HA_ERR_RECORD_IS_THE_SAME value is from reading the code; it was not
checked in a debugger.)
Suggested fix: in on_update_cascade() (and the ON UPDATE SET NULL loop, line
1298), treat HA_ERR_RECORD_IS_THE_SAME from the child's ha_update_row as
success and continue with the next child row. A cascade failure that raises no
error should not be returned as "skip this row".
© 2026, Oracle Corporation and/or its affiliates
Description: With the SQL-layer foreign keys (innodb_native_foreign_keys=OFF, the default), an UPDATE of a referenced parent key with ON UPDATE CASCADE is silently skipped when a child row already holds exactly the new value. This happens when the old and new key are equal under the column's collation (case, accents, or trailing spaces with a PAD SPACE collation): the child row then references the parent both before and after. The statement returns "0 rows affected", no warning, and the whole row update is lost, including other columns assigned in the same statement. Other child rows of the same parent are not cascaded either. With innodb_native_foreign_keys=ON, the same statement updates the row. Under row-based replication the same condition is reached on every replica for every collation-equal key change with ON UPDATE CASCADE, also when on the source the child held the old value and the statement worked. The source logs the cascaded child change first (with NO_FOREIGN_KEY_CHECKS_F) and then the parent change. The replica applies the child event, so the child now holds the new value, and the parent event then fails with error 1451. The replica stops, and replaying the binary log with mysqlbinlog | mysql stops at the same event. Reproduced on trunk 3b99be40f8a (built from source, RelWithDebInfo) and on 26.7.0. How to repeat: 1) Single server, default settings: CREATE DATABASE d; USE d; CREATE TABLE p (id INT PRIMARY KEY, k VARCHAR(20) COLLATE utf8mb4_0900_ai_ci, v INT DEFAULT 0, UNIQUE KEY (k)); CREATE TABLE c (id INT PRIMARY KEY, pk VARCHAR(20) COLLATE utf8mb4_0900_ai_ci, FOREIGN KEY (pk) REFERENCES p (k) ON UPDATE CASCADE ON DELETE CASCADE); INSERT INTO p VALUES (1, 'c0', 0); INSERT INTO c VALUES (1, 'C0'); UPDATE p SET k = 'C0', v = v + 1 WHERE id = 1; SELECT k, v FROM p; Observed on trunk (26.10.0): Query OK, 0 rows affected Rows matched: 1 Changed: 0 Warnings: 0 p: 'c0', 0 Expected (and observed with innodb_native_foreign_keys=ON): Query OK, 1 row affected p: 'C0', 1 Same table definitions, other data (SQL-layer FKs | native FKs): children 'C0' and 'c0', k 'c0' -> 'C0': 0 rows, p 'c0', children 'C0','c0' | 1 row, p 'C0', children 'C0','C0' utf8mb4_0900_ai_ci, k 'kéy' -> 'key', child 'key': 0 rows, p 'kéy' | 1 row utf8mb4_general_ci, k 'k1' -> 'k1 ', child 'k1 ': 0 rows, p 'k1' | 1 row child 'c0', k 'c0' -> 'C0': 1 row in both no child, k 'c0' -> 'C0': 1 row in both child 'c0', k 'c0' -> 'c9': 1 row in both 2) Replication. Two servers, both with --log-bin=binlog --gtid-mode=ON --enforce-gtid-consistency=ON and --server-id=1 on the source, --server-id=2 on the replica, and on the replica: CHANGE REPLICATION SOURCE TO SOURCE_HOST='127.0.0.1', SOURCE_PORT=<source port>, SOURCE_USER='root', SOURCE_PASSWORD='', SOURCE_AUTO_POSITION=1, GET_SOURCE_PUBLIC_KEY=1; START REPLICA; On the source: FLUSH BINARY LOGS; CREATE DATABASE d; CREATE TABLE d.p (id INT PRIMARY KEY, k VARCHAR(30), UNIQUE KEY (k)); CREATE TABLE d.c (cid INT PRIMARY KEY, pk VARCHAR(30), FOREIGN KEY (pk) REFERENCES d.p (k) ON UPDATE CASCADE ON DELETE CASCADE); INSERT INTO d.p VALUES (1, 'Key42'); INSERT INTO d.c VALUES (1, 'Key42'); UPDATE d.p SET k = 'key42' WHERE id = 1; INSERT INTO d.p VALUES (3, 'after'); FLUSH BINARY LOGS; Observed on trunk (26.10.0): source : p (1,'key42'), (3,'after'); c (1,'key42') replica: p (1,'Key42'); c (1,'Key42') replica error: 1451 Worker 1 failed executing transaction '...:6' at source log binlog.000002, end_log_pos 1810; Could not execute Update_rows event on table d.p; Cannot delete or update a parent row: a foreign key constraint fails (), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED mysqlbinlog <source binlog> | mysql (fresh server): ERROR 1451 (23000) at line 148: Cannot delete or update a parent row: a foreign key constraint fails () restored p: (1,'Key42') The same happens for 'kéy42' -> 'key42' (utf8mb4_0900_ai_ci), for 'key42' -> 'key42 ' (utf8mb4_general_ci), and one level down (a grandchild referencing a child key that changes case). Applied correctly: 'key42' -> 'key43', 'Key42' -> 'key42' under utf8mb4_0900_as_cs, and the case change with innodb_native_foreign_keys=ON on both servers. Expected: the replica applies the event and ends with the source's rows. Suggested fix: Suggested fix: is_column_updated() compares the key bytes, so a collation-equal change is a key change and is cascaded: https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq... on_update_cascade() finds the child rows by the old key (under the collation) and copies the new parent value into them. For a child that already holds those bytes, InnoDB's update_row returns HA_ERR_RECORD_IS_THE_SAME (storage/innobase/handler/ha_innodb.cc, line 10176). on_update_cascade breaks out of the loop on any non-zero result of ha_update_row and sets *error = 1 without raising an error: https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq... check_child_fk_ref() then returns true (line 1561), and the single-table UPDATE treats "true without an error" like a row skipped by IGNORE: https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/sq... On the replica, Update_rows_log_event maps the same result to HA_ERR_ROW_IS_REFERENCED: https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/sql/lo... (The HA_ERR_RECORD_IS_THE_SAME value is from reading the code; it was not checked in a debugger.) Suggested fix: in on_update_cascade() (and the ON UPDATE SET NULL loop, line 1298), treat HA_ERR_RECORD_IS_THE_SAME from the child's ha_update_row as success and continue with the next child row. A cascade failure that raises no error should not be returned as "skip this row".