MySQL Bugs: #87249: Relay logs shouldn't be purged if options unrelated in CHANGE MASTER is executed

Bug #87249	Relay logs shouldn't be purged if options unrelated in CHANGE MASTER is executed
Submitted:	30 Jul 2017 0:44	Modified:	28 Dec 2017 20:34
Reporter:	Jaime Sicam	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It's documented on 5.6 that relay logs will be deleted when CHANGE MASTER is executed unless RELAY_LOG_FILE or RELAY_LOG_POS options are included:

https://dev.mysql.com/doc/refman/5.6/en/change-master-to.html
CHANGE MASTER TO deletes all relay log files and starts a new one, unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay log files are kept; the relay_log_purge global variable is set silently to 0.

Same thing with 5.7 but it will also keep relay log if either SQL_THREAD or IO_THREAD is still running:

https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
Prior to MySQL 5.7.4, CHANGE MASTER TO deletes all relay log files and starts a new one, unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay log files are kept; the relay_log_purge global variable is set silently to 0. In MySQL 5.7.4 and later, relay logs are preserved if at least one of the slave SQL thread and the slave I/O thread is running; if both threads are stopped, all relay log files are deleted unless at least one of RELAY_LOG_FILE or RELAY_LOG_POS is specified.

For performance reasons, relay logs shouldn't be deleted if options provided in CHANGE MASTER should not affect relay logs such as MASTER_DELAY.

How to repeat:
In Slave, run:
STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY=1000000;
START SLAVE;

In Master, run:
USE test;
CREATE TABLE t1(a int not null auto_increment primary key, b int);
INSERT INTO t1(b) VALUES(1);
FLUSH LOGS;
INSERT INTO t1(b) SELECT a FROM t1;
FLUSH LOGS;
INSERT INTO t1(b) SELECT a FROM t1;
FLUSH LOGS;
INSERT INTO t1(b) SELECT a FROM t1;
FLUSH LOGS;
INSERT INTO t1(b) SELECT a FROM t1;
FLUSH LOGS;
INSERT INTO t1(b) SELECT a FROM t1;
FLUSH LOGS;
INSERT INTO t1(b) SELECT a FROM t1;

In Slave shell, run this to list relay logs:
while [ 1 ] ; do ls /mysql/datadir/mysql-relay* -l; sleep 1; done

Then on Slave, run:
STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY=0;

And you will see that all existing relay logs will be deleted and a new one will be created

Hi,

I apologize but I don't see how this differs from what's documented:

> STOP SLAVE;
> CHANGE MASTER TO MASTER_DELAY=0;
>
> And you will see that all existing relay logs will be deleted 
> and a new one will be created

Maybe you wanted to log a feature request?

all best
Bogdan

Of course this is feature request!

Bogdan, could you please convert this report to such and choose appropriate severity? 

Thanks in advance.

Hey Sveta,

It was not clear from the report if it's a FR or a bug report so I had to ask :)

changed it to FR

happy holidays
Bogdan

Related: Bug#93843.

Bug #93843 marked as duplicate of this one

Still affects 8.0. If I'm using GTID and CHANGE REPLICATION SOURCE (ie: promote a replica), if the GTIDs between new source and original replica are subset, then there is no reason at all to erase the relay logs. You can be in a situation where the binlogs on new source have aged away (binlog-seconds, etc) and when you change the replica to point to new replica, the new replica will NOT have the needed binlogs to restore replication.