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

[26 Aug 2014 2:31] Tac Huang
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.
[26 Aug 2014 10:30] Umesh Shastry
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
[7 Nov 2014 1:51] Tac Huang
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.