MySQL Bugs: #69209: Number of transactions behind is wrong in mysqlfailover

Bug #69209	Number of transactions behind is wrong in mysqlfailover
Submitted:	13 May 2013 7:57	Modified:	11 Jun 2013 20:33
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.3.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Situation:
Master with 2 slaves (all GTID enabled 5.6.11)

1 Slave has a stopped SQL thread (STOP SLAVE; START SLAVE IO_THREAD)

The status for the slave with stopped SQL thread in the Healt screen:
SQL thread is not running., Slave has 1 transactions behind master.

Master:
GTID executed: <UUID>d:1-25,<UUID>b:1-16 

Slave:
Retrieved_Gtid_Set: <UUID>d:9-25
Executed_Gtid_Set: <UUID>d:1-18,<UUID>b:1-16

It has processed <UUID>b:1-16
For <UUID>d it has processed 1-18, but has received up to gtid 25.
25-18=7, so it's 7 transactions behind the master, not 1.

How to repeat:
Setup 5.6.11 with 1 master and 2 slaves.
Stop 1 slave and then start the IO thread.
Execute a number of transactions on the master.
Check slave status in mysqlfailover.

Suggested fix:
Use the correct calculation for transactions behind master.

root# mysqlrpladmin --master=user:pass@192.168.15 --discover-slaves-login=user:pass --demote-master --new-master=user:pass@192.168.14 --log=switchover2.log --rpl-user=user:pass gtid
WARNING: The --new-master option is not required for 'gtid' (option ignored). Only used with the switchover command.
# Discovering slaves for master at 192.168.15:3306
# Discovering slave at 192.168.14:3306
# Found slave: 192.168.14:3306
# Discovering slave at 192.168.16:3306
# Found slave: 192.168.16:3306
# Checking privileges.
#
# UUIDS for all servers:
+-----------------+-------+---------+---------------------------------------+
| host            | port  | role    | uuid                                  |
+-----------------+-------+---------+---------------------------------------+
| 192.168.15  | 3306  | MASTER  | <UUID>d  |
| 192.168.14  | 3306  | SLAVE   | <UUID>b  |
| 192.168.16  | 3306  | SLAVE   | <UUID>f  |
+-----------------+-------+---------+---------------------------------------+
#
# Transactions executed on the server:
+-----------------+-------+---------+--------------------------------------------+
| host            | port  | role    | gtid                                       |
+-----------------+-------+---------+--------------------------------------------+
| 192.168.15  | 3306  | MASTER  | <UUID>d:1-25  |
| 192.168.15  | 3306  | MASTER  | <UUID>b:1-16  |
| 192.168.14  | 3306  | SLAVE   | <UUID>d:1-25  |
| 192.168.14  | 3306  | SLAVE   | <UUID>b:1-16  |
| 192.168.16  | 3306  | SLAVE   | <UUID>d:1-18  |
| 192.168.16  | 3306  | SLAVE   | <UUID>b:1-16  |
+-----------------+-------+---------+--------------------------------------------+
# ...done.
root# mysqlrpladmin --master=user:pass@192.168.15 --discover-slaves-login=user:pass --demote-master --new-master=user:pass@192.168.14 --log=switchover2.log --rpl-user=user:pass health
WARNING: The --new-master option is not required for 'health' (option ignored). Only used with the switchover command.
# Discovering slaves for master at 192.168.15:3306
# Discovering slave at 192.168.14:3306
# Found slave: 192.168.14:3306
# Discovering slave at 192.168.16:3306
# Found slave: 192.168.16:3306
# Checking privileges.
#
# Replication Topology Health:
+-----------------+-------+---------+--------+------------+----------------------------------------------------------------------+
| host            | port  | role    | state  | gtid_mode  | health                                                               |
+-----------------+-------+---------+--------+------------+----------------------------------------------------------------------+
| 192.168.15  | 3306  | MASTER  | UP     | ON         | OK                                                                   |
| 192.168.14  | 3306  | SLAVE   | UP     | ON         | OK                                                                   |
| 192.168.16  | 3306  | SLAVE   | UP     | ON         | SQL thread is not running., Slave has 1 transactions behind master.  |
+-----------------+-------+---------+--------+------------+----------------------------------------------------------------------+
# ...done.

Hello Daniel,

Thank you for the report.
Verified as described.

Thanks,
Umesh

Fixed in release-1.2.3

Fixed as of the upcoming MySQL Utilities 1.2.3 release, and here's the changelog entry:

The algorithm to calculate the number of transactions behind the master
was fixed for the health report of the replication utilities.

Thank you for the bug report.