MySQL Bugs: #74089: Report Relay_Log_File and Relay_Log

Bug #74089	Report Relay_Log_File and Relay_Log_Pos on relay-log-recovery.
Submitted:	25 Sep 2014 21:21	Modified:	1 Sep 2015 13:57
Reporter:	Jean-François Gagné	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When doing relay log recovery, a line is put in MySQL error log with Relay_Master_Log_File and Exec_Master_Log_Pos:

2014-09-25 22:55:58 1947 [Warning] Recovery from master pos 1801889 and file binlog.000001.

Adding Relay_Log_File and Relay_Log_Pos to this line (or another line) would be useful.

An example where this information would be useful is if the master is unreachable after crash/restart.  If the slave was lagging prior to the restart and with this information, a DBA could set the SQL thread position in the relay logs and allow the slave to make progress while the master is recovered.

How to repeat:
Run MySQL with relay-log-recovery=1.

Sorry about previous comment, bat cut and paste, retry:

Related to Bug #74321 and Bug #74323.

I think the following test case mimics a valid use case scenario.

#
# Test case validating BUG#74089 scenario
#

--source include/master-slave.inc
--source include/have_binlog_format_statement.inc

CREATE TABLE t1 (c1 INT);

--source include/sync_slave_sql_with_master.inc
call mtr.add_suppression("Recovery from master pos");

--source include/stop_slave_sql.inc

--source include/rpl_connection_master.inc

INSERT INTO t1 VALUES (1);

# show the events in the master's binary log
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)

--let $create_master_pos= query_get_value(SHOW BINLOG EVENTS, Pos, 2)
--let $ins_master_pos= query_get_value(SHOW BINLOG EVENTS, Pos, 4)

--source include/sync_slave_io_with_master.inc

# show that we have the CREATE *and* the INSERT statement in the
# relay log and that the read positions and exec positions are
# as expected (binlog fully copied, executed only the CREATE TABLE
# statement)

--let $begin_of_insert_trx_pos_in_rl= query_get_value(SHOW RELAYLOG EVENTS in 'slave-relay-bin.000002', Pos, 5)
--let $create_tbl_stmt_trx_pos_in_bl= query_get_value(SHOW RELAYLOG EVENTS in 'slave-relay-bin.000002', End_log_pos, 4)
--let $end_of_insert_trx_log_pos_in_rl= query_get_value(SHOW RELAYLOG EVENTS in 'slave-relay-bin.000002', End_log_pos, 7)

--let $exec_master_log_pos= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1)
--let $read_master_log_pos= query_get_value(SHOW SLAVE STATUS, Read_Master_Log_Pos, 1)
--let $relay_log_pos= query_get_value(SHOW SLAVE STATUS, Relay_Log_Pos, 1)

--let $assert_cond=$begin_of_insert_trx_pos_in_rl = $relay_log_pos
--let $assert_text="Relay_log_pos points at the beginning of the INSERT trx."
--source include/assert.inc

--let $assert_cond=$create_tbl_stmt_trx_pos_in_bl = $exec_master_log_pos
--let $assert_text="Exec_master_log_pos points at the CREATE TABLE statement."
--source include/assert.inc

--let $assert_cond= $end_of_insert_trx_log_pos_in_rl = $read_master_log_pos
--let $assert_text="Read_master_log_pos points at the end of the INSERT trx."
--source include/assert.inc

#
# Restart the server with relay-log-recovery set - this will reset the positions
#
--let $rpl_server_parameters=--relay-log-recovery=1 --skip-slave-start
--source include/rpl_restart_server.inc

--let $exec_master_log_pos_restart= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1)
--let $read_master_log_pos_restart= query_get_value(SHOW SLAVE STATUS, Read_Master_Log_Pos, 1)
--let $relay_log_pos_restart= query_get_value(SHOW SLAVE STATUS, Relay_Log_Pos, 1)

# check the positions again
--let $assert_cond=$relay_log_pos_restart = 4
--let $assert_text="Relay_log_pos points at the beginning of the new relay log file now."
--source include/assert.inc

--let $assert_cond=$read_master_log_pos_restart = $create_tbl_stmt_trx_pos_in_bl
--let $assert_text="Read_master_log_pos after restart points at the CREATE TABLE position."
--source include/assert.inc

#
# Show that even though the positions have changed, the
# relay logs have not yet been purged. Lets assume that
# the master has crashed and thus the slave cannot connect
# to it. This means that we could try to apply the existing
# relay logs (maybe they are indeed corrupt, maybe they are
# not).
#

--eval CHANGE MASTER TO Relay_log_pos=$relay_log_pos, Relay_log_file='slave-relay-bin.000002'
--source include/start_slave_sql.inc
--let $wait_condition= SELECT COUNT(*)=1 FROM t1
--source include/wait_condition.inc

--source include/start_slave_io.inc
--source include/rpl_connection_master.inc

DROP TABLE t1;

--source include/sync_slave_sql_with_master.inc

--source include/rpl_end.inc

Thanks for looking into that Luís.

Yes, your test case is a valid use case scenario.

About your corruption comment, corruption will be detected by the checksum.

This use case scenario becomes event more significant if there are more than a single un-executed transaction on the slave.

Thanks for your feedback, this has been improved in upcoming versions and the following was noted in the 5.6.27 and 5.7.8 changelogs:
When relay_log_recovery is set, the error log entry that reports the new recovery positions has been extended to also report the old relay log positions.