Bug #57185 Wrong SQL thread coordinates reported after CHANGE MASTER TO
Submitted: 1 Oct 2010 21:26 Modified: 16 Nov 2017 7:47
Reporter: Elena Stepanova Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1, 5.5, 5.6 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[1 Oct 2010 21:26] Elena Stepanova
Description:
In case CHANGE MASTER TO command contains RELAY_LOG_FILE/RELAY_LOG_POS values, even if they are not changed, SHOW SLAVE STATUS starts reporting wrong SQL thread position.

Use case:

I have slave server with SQL thread significantly behind master (while IO thread stays more or less in sync with master). At some point, I need to stop slave to issue CHANGE MASTER command, and then start it back. Since master binary logs have already been processed, they might be purged by this time. Due to this, I cannot afford losing relay logs, and have to include RELAY_LOG_FILE/RELAY_LOG_POS into my CHANGE MASTER TO command along with the desired changes. 

So, I stop slave, take Relay_Log_File/Relay_Log_Pos from SHOW SLAVE STATUS, and use the values in CHANGE MASTER TO to ensure they are the same as before.

As soon as I do so, SHOW SLAVE STATUS starts reporting wrong SQL thread coordinates (Relay_Master_Log_File / Exec_Master_Log_Pos) -- instead of previous SQL thread position, they start pointing at IO thread position.
It does not directly affect further replication, as SQL thread still starts where it is supposed to, but it makes SHOW SLAVE STATUS output unreliable, and in case of replication failure wrong values of <binary log>/<binary pos> are also given in the error log line "Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log <binary log> position <binary pos>"

How to repeat:
--source include/master-slave.inc

--disable_warnings
DROP TABLE IF EXISTS t;
--enable_warnings

--sync_slave_with_master

STOP SLAVE SQL_THREAD;

--connection master

SHOW MASTER STATUS;

CREATE TABLE t ( i INT );
FLUSH LOGS;
INSERT INTO t VALUES (1);

SHOW MASTER STATUS;

--connection slave

let $show_statement = SHOW SLAVE STATUS;
let $field = Slave_IO_State;
let $condition = = 'Waiting for master to send event';
--source include/wait_show_condition.inc

STOP SLAVE;

let $relay_log = query_get_value( SHOW SLAVE STATUS, Relay_Log_File, 1 );
let $relay_pos = query_get_value( SHOW SLAVE STATUS, Relay_Log_Pos, 1 );

let $old_master_log = query_get_value( SHOW SLAVE STATUS, Relay_Master_Log_File, 1 );
let $old_master_exec_pos = query_get_value( SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1 );

eval CHANGE MASTER TO RELAY_LOG_FILE='$relay_log', RELAY_LOG_POS=$relay_pos;
SET GLOBAL relay_log_purge=ON;

let $new_master_log = query_get_value( SHOW SLAVE STATUS, Relay_Master_Log_File, 1 );
let $new_master_exec_pos = query_get_value( SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1 );

--echo # From SHOW SLAVE STATUS:
--echo # Execution position before CHANGE MASTER: $old_master_log:$old_master_exec_pos
--echo # Execution position after CHANGE MASTER:  $new_master_log:$new_master_exec_pos

START SLAVE;

--connection master
INSERT INTO t VALUES (2);

--sync_slave_with_master
echo # Ensure that slave executed events
# between $old_master_log:$old_master_exec_pos and $new_master_log:$new_master_exec_pos;
SELECT * FROM t;

# Cleanup

--connection master
DROP TABLE t;
--sync_slave_with_master

--exit
[4 Oct 2010 23:09] Elena Stepanova
Due to bug#55460, the provided test case needs to be modified if it is run on 5.1 ('./' prefix to be added to the relay log file name in CHANGE MASTER command).
[16 Nov 2017 7:47] Erlend Dahl
Not reproducible on latest 8.0.