MySQL Bugs: #95496: Replication of memory tables

Bug #95496	Replication of memory tables
Submitted:	23 May 2019 13:51	Modified:	24 Jul 2020 15:45
Reporter:	Iwo P	Email Updates:
Status:	Closed	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

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.

Hello Iwo P,

Thank you for the report and feedback.

Thanks,
Umesh

This is directly related to my contribution in https://bugs.mysql.com/bug.php?id=93771 which unfortunately isn't implemented completely yet

Posted by developer:
 
Changelog entry added for MySQL 8.0.22 and 5.7.32:

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 statement to the binary log to that effect. Previously, this was a DELETE statement, but it is now a TRUNCATE statement. A replica server also writes this statement to its own binary log when it shuts down and restarts. The statement is always logged in statement format, even if the binary logging format is set to ROW, and it is written even if read_only or super_read_only mode is set on the server.   

Documentation updates made in
https://dev.mysql.com/doc/refman/8.0/en/replication-features-memory.html
https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html

Posted by developer:
 
Already changelogged, reclosing.