Errant transaction need to be checked in GTID mode

[deniszh]

Trying to fix
https://code.google.com/p/mysql-master-ha/issues/detail?id=86
We need some check for errant transactions in masterha_check_repl, algorithm is in http://www.percona.com/blog/2014/05/19/errant-transactions-major-hurdle-for-gtid-based-failover-in-mysql-5-6/

[brutto]

+1

[bogdanov1609]

+2

[surpr1ze]

👍

[grypyrg]

I think it's more important to prevent errand transactions. The best way to do this is with super_read_only which is in PerconaServer/MariaDB (comes from webscalesql). look at mha-helper for implementation in MHA: https://github.com/grypyrg/mha-helper

[deniszh]

Yep, super_read_only is nice, but it's not in upstream. Well, we switched to mysql-utilities based failover anyway...

[grypyrg]

mysql-utilities failover is a lot more limited than what MHA can do. It might work for you, but for many, it's just not good enough.

[deniszh]

Hello @grypyrg,
If you have no GTID - yep, then you have no choice (maybe you can use Pseudo-GTID and https://github.com/outbrain/orchestrator) but otherwise I see no other limitation, though. Of course, you need to write some wrapper around it, but it's not really complicated.
From the point of current MySQL state (5.7) MHA looks quite outdated IMO.

[grypyrg]

MHA can ssh to all nodes including a mysqlbinlog stream to fetch missing transactions from both binary and relay logs.
mysqlfailover and/or mysqlrpladmin only just changes the replication setup, it does not do anything like that.

[deniszh]

IIRC if you using GTID - it will complete skipping all ssh stuff, it doesn't even check that -

mha4mysql-manager/lib/MHA/MasterMonitor.pm

Lines 370 to 389 in c6db3eb

	if ( !$_server_manager->is_gtid_auto_pos_enabled() ) {
	$log->info("GTID (with auto-pos) is not supported");
	MHA::SSHCheck::do_ssh_connection_check( \@alive_servers, $log,
	$servers_config[0]->{log_level}, $g_workdir )
	unless ($g_skip_ssh_check);
	$log->info("Checking MHA Node version..");
	foreach my $slave (@alive_slaves) {
	MHA::ManagerUtil::check_node_version(
	$log, $slave->{ssh_user}, $slave->{ssh_host},
	$slave->{ssh_ip}, $slave->{ssh_port}
	);
	}
	$log->info(" Version check ok.");
	}
	else {
	$log->info(
	"GTID (with auto-pos) is supported. Skipping all SSH and Node package checking."
	);
	check_binlog_servers( $binlog_server_ref, $log );
	}

So, if you using GTID everywhere - MHA is mostly overkill and easily can be replaced with mysql-utilities.

[grypyrg]

Yes, you're right in that.it skips most ssh stuff, but it does connect to binlog servers and get missing events from the masters binlog there (although buggy).

[LaurentKol]

I submitted pull request #110 which adds optional check for errant transactions prior to manual failover (masterha_master_switch). If any are found, failover is aborted and a fix is suggested using mysqlslavetrx with the list of relevant GTIDs. Hope this helps.

[nuzayats]

+1