Bug #95496 Replication of memory tables
Submitted: 23 May 13:51 Modified: 30 May 11:41
Reporter: Iwo P Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.26, 8.0.16 OS:Any
Assigned to: CPU Architecture:Any

[23 May 13:51] Iwo P
Description:
(1)

MySQL documentation says:

"When a master server shuts down and restarts, its MEMORY tables become empty. To replicate this effect to slaves, the first time that the master uses a given MEMORY table after startup, it logs an event that notifies slaves that the table must to be emptied by writing a DELETE statement for that table to the binary log.

When a slave server shuts down and restarts, its MEMORY tables become empty. This causes the slave to be out of synchrony with the master and may lead to other failures or cause the slave to stop."

That, however, doesn't describe exactly the behaviour of replication and memory tables. When slave server shuts down and restarts, its MEMORY tables become empty AND it will write a delete statement to own binary log.

It is quite important for replication, as it will break any failover solution if
slave's binary logs were already purged (and GTID is in use).

Moreover,

(2)
The delete event is always written as a statement, even if the binlog_format is set ROW.

Also,
(3)
The even is always written to the binary log even if (super_)read_only is set to 1.

How to repeat:
(1)

1. Setup GTID a master-slave replication, binlog_format=row and super_read_only=1 on slave.

2. Create schema
create database test;
create table test.test (id int) engine=memory;
insert into test.test SET id = 42;

3. Restart slave server, check slave's binary log, and after some time execute:
select * from test.test.

Slave's binary log will write the following event:
#190523  9:36:36 server id 102  end_log_pos 542 CRC32 0xbabd0d26 	Query	thread_id=5	exec_time=0	error_code=0
SET TIMESTAMP=1558618596/*!*/;
DELETE FROM `test`.`test`

4. Check master's and slave's gtid_executed

Master:
+-------------------------------------------+
| @@global.gtid_executed                    |
+-------------------------------------------+
| 00021599-1111-1111-1111-111111111111:1-10 |
+-------------------------------------------+

Slave:
+-----------------------------------------------------------------------------------+
| @@global.gtid_executed                                                            |
+-----------------------------------------------------------------------------------+
| 00021599-1111-1111-1111-111111111111:1-10,
**00021601-3333-3333-3333-333333333333:1** |
+-----------------------------------------------------------------------------------+

Suggested fix:
(1)
Change the documentation to reflect actual behaviour.

(2)
It is probably not fixable but still might be nice to include in the documentation.

(3)
A slave should not write anything (apart of replication events if log_slave_updates used) to its binary log if started with (super_)read_only.

Moreover, it might be nice to touch all memory tables as soon as the server is restarted and not when the table is explicitly queried/opened.
[30 May 11:41] Umesh Shastry
Hello Iwo P,

Thank you for the report and feedback.

Thanks,
Umesh
[11 Aug 9:40] Daniƫl van Eeden
This is directly related to my contribution in https://bugs.mysql.com/bug.php?id=93771 which unfortunately isn't implemented completely yet