MySQL Bugs: #77259: Can not change master after MTS replication breakage

Bug #77259	Can not change master after MTS replication breakage
Submitted:	5 Jun 2015 12:37	Modified:	4 Mar 2016 9:14
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.24	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	5.6.24, MTS, RESET SLAVE

Description:
I broken slave required me to do some "maintenance". Given it was in MTS mode I tried to change the master position (I don't use GTID) and it would not let me even after trying to do RESET SLAVE.

How to repeat:
Replication breaks (let's not worry for now why).
I stop the slave and want to reconnect to the master at a specific point in time.
I did have slave_parallel_threads = 25 previously.

root@myhost2 [mysql]> change master to master_host = 'myhost1',master_user= 'some_user', master_password='some_pass', master_log_file = 'binlog.000375' , master_log_pos= 1907;
ERROR 1802 (HY000): CHANGE MASTER cannot be executed when the slave was stopped with an error or killed in MTS mode. Consider using RESET SLAVE or START SLAVE UNTIL.
root@myhost2 [mysql]> set global slave_parallel_workers = 0;
Query OK, 0 rows affected (0.00 sec)

root@myhost2 [mysql]> reset slave;
Query OK, 0 rows affected (0.01 sec)

root@myhost2 [mysql]> change master to master_host = 'myhost1',master_user= 'some_user', master_password='some_pass', master_log_file = 'binlog.000375' , master_log_pos= 1907;
ERROR 1802 (HY000): CHANGE MASTER cannot be executed when the slave was stopped with an error or killed in MTS mode. Consider using RESET SLAVE or START SLAVE UNTIL.
root@myhost2 [mysql]> 

Suggested fix:
It seems that the "I'm in a broken MTS mode" does not get reset if you do RESET SLAVE.
I guess the right thing to do here is to fix that.

Ignore data consistency caused by the breakage etc. That's my problem. If I want to talk to the master at the position I want then it should be allowed. I've already done RESET SLAVE

Note: even RESET SLAVE ALL does not work.

Hello Simon,

Thank you for the bug report.
Sorry, somehow I missed this bug for long.
I tried to reproduce this issue at my end but no luck so far with 5.6.28/29(I'm not sure whether internally any related bug got fixed in previous release). Could you please share exact conf file/any other details which would help us to repeat this issue at our end? 

Regards,
Umesh

Broken slave means basically "power off" or "server crash" while mysqld was running. That's likely to lead to lost data on startup, if GTID is not being used, and basically the MTS info was not kept fully consistently. So mysqld would recovery (Innodb tables) but the information in the relay logs might not be consistent with that.

I'm not really sure now what happened as this was some time ago. I filed this nearly 9 months ago.

However, the point here was that I was unable to do a RESET SLAVE.   If I see the issue again I'll re-open this ticket.  I'm now using a newer version of MySQL so will have to see if it behaves  the same.)

Thank you Simon for the update.
For now I'll close this bug, but please re-open it if required.

Regards,
Umesh