MySQL Bugs: #73482: transaction lost if setting relay_log_info

Bug #73482	transaction lost if setting relay_log_info_repository=FILE and a crash happend
Submitted:	6 Aug 2014 8:54	Modified:	6 Aug 2014 9:52
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In function Xid_log_event::do_apply_event_worker
Step 1: the relay info of this worker  is updated 
Step 2: then commit the transaction

If crashed during step1 and step2 and relay_log_info_repository is setting to FILE, then the relay log info is updated , and after crashing recovery, this transaction is considered to be finished ( but actually not).

The workaround is setting relay_log_info_repository to TABLE

How to repeat:
a simple test case:

--source include/master-slave.inc
--source include/not_embedded.inc
--source include/have_debug.inc
--source include/have_binlog_format_row.inc

connection slave;
stop slave;

set global relay_log_info_repository= "FILE";
set global slave_parallel_workers = 8;
start slave;

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

connection slave;
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.2.expect
stop slave;
set global debug="d,crash_after_update_pos_before_apply";
start slave;

connection master;
insert into t1 values (1);

--let $rpl_server_number= 2
--source include/rpl_reconnect.inc

connection slave;
start slave;

connection master;
select * from t1;
--sync_slave_with_master

connection slave;
select * from t1;

#cleanup
connection master;
drop table t1;

--source include/rpl_end.inc

Suggested fix:
commit first and then update  relay info if relay_log_info_repository=FILE

Hello Zhai Weixiang,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

// 5.6.20

[ushastry@ushastry mysql-test]$ ./mysql-test-run.pl --mysqld=--binlog_format=row 73482
Logging: ./mysql-test-run.pl  --mysqld=--binlog_format=row 73482
2014-08-06 15:04:51 2788 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2014-08-06 15:04:51 2788 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2014-08-06 15:04:51 2788 [Note] Plugin 'FEDERATED' is disabled.
2014-08-06 15:04:51 2788 [Note] Binlog end
2014-08-06 15:04:51 2788 [Note] Shutting down plugin 'CSV'
2014-08-06 15:04:51 2788 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.20
Using binlog format 'row'
Checking supported features...
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/ushastry/mybuilds/mysql-5.6.20/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
Warnings:
Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
Note	####	Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
stop slave;
set global relay_log_info_repository= "FILE";
set global slave_parallel_workers = 8;
start slave;
Warnings:
Note	1753	slave_transaction_retries is not supported in multi-threaded slave mode. In the event of a transient failure, the slave will not retry the transaction and will stop.
create table t1 (a int primary key) engine=innodb;
stop slave;
set global debug="d,crash_after_update_pos_before_apply";
start slave;
Warnings:
Note	1753	slave_transaction_retries is not supported in multi-threaded slave mode. In the event of a transient failure, the slave will not retry the transaction and will stop.
insert into t1 values (1);
include/rpl_reconnect.inc
start slave;
select * from t1;
a
1
select * from t1;
a               <-------------------- row missing in slave
drop table t1;
include/rpl_end.inc
main.73482                               [ pass ]   4046
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 4.046 of 15 seconds executing testcases

Completed: All 1 tests were successful.