MySQL Replication Failure Caused by MEMORY Table Reset After Master Restart

Background

A production server hosting a MySQL master crashed and rebooted; the high‑availability component promoted a new master and started replication, but replication stopped shortly after with the error Could not execute update_rows event on table xx; Can't find record in xx, Error_Code: 1032; handler error HA_ERR_KEY_NOT_FOUND .

Troubleshooting Process

(1) Checked whether the master‑slave switch was forced – GTIDs matched, so no data loss.

(2) Verified slave configuration parameters – no issues.

(3) Ran show slave status\G on the new slave and found an extra GTID compared to the new master, indicating a problem.

(4) Analyzed the extra GTID with mysqlbinlog -vvvv binlogs.xx --include-gtids=xx and discovered that the problematic transaction involved the same table that caused the replication error.

BEGIN
/*!*/;
# at 349
#240716 12:33:45 server id 1002  end_log_pos 509 CRC32 0xbea1877e       Query   thread_id=9     exec_time=0     error_code=0
use `sky`/*!*/;
SET TIMESTAMP=1721104425/*!*/;
DELETE FROM `sky`.`sky_test` /* generated by server, implicitly emptying in‑memory table */
/*!*/;
# at 509
#240716 12:33:45 server id 1002  end_log_pos 584 CRC32 0xd0443172       Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1721104425/*!*/;
COMMIT

The table sky_test was identified as a MEMORY engine table:

CREATE TABLE `sky_test` (
  `id` int DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Because MEMORY tables are cleared on server restart, the new slave lost the rows while the new master performed an update, causing the replication error.

Solution Steps

Skip replication of the MEMORY table on the new slave and let replication catch up.

Confirm the table’s importance with the business; if needed, export the table data from the new master and import it into the slave before resuming normal replication.

Convert the table to an InnoDB transactional table to avoid similar issues.

To prevent accidental creation of non‑transactional tables, configure sql_mode and disabled_storage_engines at the instance level.

If a MEMORY table must be used, MySQL recommends setting the init_file system variable to populate the table on replica startup, avoiding stale data during the interval between source restart and first use.

Q&A

Why does a MEMORY table record only a statement in the binlog even when binlog_format=row? Because MEMORY tables are always logged in statement format.

When does the automatic DELETE FROM memory_table occur? It is triggered on the first access to the MEMORY table after a server restart.

Conclusion

To maintain master‑slave consistency, avoid using non‑transactional tables such as MEMORY; prefer InnoDB or adjust server settings accordingly.