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
