Description:
**What I did:** On the source, ran one UPDATE of 200,000 rows on a table that is the parent of a FOREIGN KEY. The
updated column is not part of any key and the child table is empty. The replica applied it via GTID row-based replication.
**What I expected:** The replica's memory use should not depend on how many rows the transaction changes, as on the
source.
**What happened:** On the replica, `memory/sql/THD::main_mem_root` grows by **~3 KB per changed row for every foreign key
that references the table**. The memory is held until the statement has been applied, and about a third of the peak
stays allocated afterwards. The source executes the same UPDATE without any `THD::main_mem_root` growth.
- The growth does not depend on the referential action (none, `ON UPDATE CASCADE`), `binlog_row_image` (FULL, MINIMAL)
or `binlog_transaction_compression` (OFF, ON).
- **Regression:** MySQL 9.5.0 is not affected. 9.6.0, which moved foreign key handling into the SQL layer, and all later
versions are affected.
- With `innodb_native_foreign_keys=ON` there is no growth.
In production (9.7), a single UPDATE of 1.4 M rows on a table referenced by ~12 foreign keys makes the replica exceed
15 GB and get OOM-killed. After a restart it applies the same transaction again and gets killed again, so the replica
never gets past it.
## Related
Bug #121491 ("SQL-layer FKs prepare ON UPDATE CASCADE for UPDATEs that don't touch referenced columns") is in the same
area, but it is a different symptom. It reports execution time and `Opened_tables` on the server running the
statement, and it goes away with `ON UPDATE NO ACTION`. This report is about memory on the replica applier, the source
is not affected, and the growth happens with any foreign key, without a referential action.
## Related
Bug #121491 ("SQL-layer FKs prepare ON UPDATE CASCADE for UPDATEs that don't touch referenced columns") is in the same
area, but it is a different symptom. It reports execution time and `Opened_tables` on the server running the
statement, and it goes away with `ON UPDATE NO ACTION`. This report is about memory on the replica applier, the source
is not affected, and the growth happens with any foreign key, without a referential action.
How to repeat:
Run on both source and replica, so the data is identical and nothing is replicated yet:
```sql
SET sql_log_bin = 0, cte_max_recursion_depth = 200000;
CREATE DATABASE test;
CREATE TABLE test.t (id INT PRIMARY KEY, val INT NOT NULL, pad CHAR(100) NOT NULL);
INSERT INTO test.t WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 200000)
SELECT n, 0, REPEAT('x', 100) FROM seq;
CREATE TABLE test.child1 (id INT PRIMARY KEY, t_id INT, FOREIGN KEY (t_id) REFERENCES test.t (id));
```
Start GTID replication (all other settings default), then run on the source:
```sql
UPDATE test.t SET val = val + 1;
```
On the replica, while and after the transaction is applied:
```sql
SELECT CURRENT_NUMBER_OF_BYTES_USED, HIGH_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME = 'memory/sql/THD::main_mem_root';
```
Suggested fix:
Skip foreign key processing for rows whose referenced columns are not changed, or release the per-row foreign key
allocations after each row instead of at the end of the statement.
## Workaround
- `innodb_native_foreign_keys=ON` on source and replica (read-only, requires a restart).
- Split large UPDATEs on parent tables into smaller transactions.
- In our production environment, `STOP REPLICA; START REPLICA` releases the memory that stays allocated after the
transaction has been applied.
