MySQL Bugs: #73720: `SHOW SLAVE STATUS` != slave_relay_log

Bug #73720	`SHOW SLAVE STATUS` != slave_relay_log_info when replicating DDL
Submitted:	26 Aug 2014 2:31	Modified:	7 Nov 2014 1:51
Reporter:	Tac Huang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6.19, 5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
1. set relay_log_info_repository = TABLE
2. create table on master
3. on slave side, the slave_relay_log_info.Master_log_pos != `SHOW SLAVE STATUS`.Exec_Master_Log_Pos

How to repeat:
test case:

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

connection slave;
--source include/stop_slave.inc
set global relay_log_info_repository= "TABLE";
--source include/start_slave.inc

connection master;
create table t1 (a int primary key) engine=innodb;
--sync_slave_with_master

connection slave;
--disable_query_log
let $pos0_slave= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1);
let $pos1_slave= query_get_value(select * from mysql.slave_relay_log_info, Master_log_pos, 1);
if ($pos0_slave != $pos1_slave)
{
  echo $pos0_slave $pos1_slave;
  die SHOW_SLAVE_STATUS.Exec_Master_Log_Pos != mysql.slave_relay_log_info.Master_log_pos
}
--enable_query_log

#clean-up

connection master;
drop table t1;
--sync_slave_with_master

connection slave;
--source include/stop_slave.inc
set global relay_log_info_repository= "FILE";

Suggested fix:
DDL is wrapped in a QueryEvent, which doesn't flush relay log info with force (Rpl_info_handler::do_flush_info) after apply. 

When use file as Rpl_info_handler, it will flush_io_cache; when use table, it will do nothing.

Transactional DML events force flush relay log by Xid event.

Myisam DML events will encounter same problem since it's committed by Query event(commit), I guess.

Hello Tac Huang,

Thank you for the bug report and test case.
Observed that Master_log_pos and Exec_Master_Log_Pos were indeed different.

Thanks,
Umesh

Myisam DML encounter same problem in 5.6.20.
It seems that "relay_log_info_repository = TABLE" is less crash-safe than "relay_log_info_repository = FILE" in Myisam DML.