188.8.131.52 Replication and MEMORY Tables
When a replication source server shuts down and restarts, its
MEMORY tables become empty. To replicate this effect to replicas, the first time that the source uses a given
MEMORY table after startup, it logs an event that notifies replicas that the table must be emptied by writing a
DELETE or (from MySQL 5.7.32)
TRUNCATE TABLE statement for that table to the binary log. This generated event is identifiable by a comment in the binary log, and if GTIDs are in use on the server, it has a GTID assigned. The statement is always logged in statement format, even if the binary logging format is set to
ROW, and it is written even if
super_read_only mode is set on the server. Note that the replica still has outdated data in a
MEMORY table during the interval between the source's restart and its first use of the table. To avoid this interval when a direct query to the replica could return stale data, you can set the
init_file system variable to name a file containing statements that populate the
MEMORY table on the source at startup.
When a replica server shuts down and restarts, its
MEMORY tables become empty. This causes the replica to be out of synchrony with the source and may lead to other failures or cause the replica to stop:
Row-format updates and deletes received from the source may fail with
Can't find record in '.
Statements such as
INSERT INTO ... SELECT FROMmay insert a different set of rows on the source and replica.
The safe way to restart a replica that is replicating
MEMORY tables is to first drop or delete all rows from the
MEMORY tables on the source and wait until those changes have replicated to the replica. Then it is safe to restart the replica.
An alternative restart method may apply in some cases. When
binlog_format=ROW, you can prevent the replica from stopping if you set
slave_exec_mode=IDEMPOTENT before you start the replica again. This allows the replica to continue to replicate, but its
MEMORY tables will still be different from those on the source. This can be okay if the application logic is such that the contents of
MEMORY tables can be safely lost (for example, if the
MEMORY tables are used for caching).
slave_exec_mode=IDEMPOTENT applies globally to all tables, so it may hide other replication errors in non-
(The method just described is not applicable in NDB Cluster, where
slave_exec_mode is always
IDEMPOTENT, and cannot be changed.)
The size of
MEMORY tables is limited by the value of the
max_heap_table_size system variable, which is not replicated (see Section 184.108.40.206, “Replication and Variables”). A change in
max_heap_table_size takes effect for
MEMORY tables that are created or updated using
ALTER TABLE ... ENGINE = MEMORY or
TRUNCATE TABLE following the change, or for all
MEMORY tables following a server restart. If you increase the value of this variable on the source without doing so on the replica, it becomes possible for a table on the source to grow larger than its counterpart on the replica, leading to inserts that succeed on the source but fail on the replica with Table is full errors. This is a known issue (Bug #48666). In such cases, you must set the global value of
max_heap_table_size on the replica as well as on the source, then restart replication. It is also recommended that you restart both the source and replica MySQL servers, to insure that the new value takes complete (global) effect on each of them.