Bug #89059 LAST_CONFLICT_FREE_TRANSACTION shows different values in group (empty on joiner)
Submitted: 26 Dec 2017 18:54 Modified: 23 Feb 2018 14:13
Reporter: Narendra Chauhan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Group Replication Severity:S3 (Non-critical)
Version:8.0.11 OS:Any
Assigned to: CPU Architecture:Any

[26 Dec 2017 18:54] Narendra Chauhan
Description:
Scenario: Verify that LAST_CONFLICT_FREE_TRANSACTION shows same values across the group.
Expected value: It must show same value across the group once it is synchronized.
Actual value: It shows different values (i.e. empty on new joiner).

On M1:
MEMBER_PORT	TRANSACTIONS_COMMITTED_ALL_MEMBERS	LAST_CONFLICT_FREE_TRANSACTION
13001	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4

On M2:
MEMBER_PORT	TRANSACTIONS_COMMITTED_ALL_MEMBERS	LAST_CONFLICT_FREE_TRANSACTION
13001	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5	

mtr testcase output:-
======================
----------- MYSQLTEST OUTPUT START -----------

include/group_replication.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 server1]
[connection server1]
CREATE TABLE t1 (a INT PRIMARY KEY);
DROP TABLE t1;
# Sleep for 150 secs
SELECT gm.MEMBER_PORT, gms.TRANSACTIONS_COMMITTED_ALL_MEMBERS, gms.LAST_CONFLICT_FREE_TRANSACTION
FROM performance_schema.replication_group_member_stats gms
JOIN performance_schema.replication_group_members gm
ON gms.MEMBER_ID=gm.MEMBER_ID;
MEMBER_PORT	TRANSACTIONS_COMMITTED_ALL_MEMBERS	LAST_CONFLICT_FREE_TRANSACTION
13001	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-4	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4
13000	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-4	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4
[connection server2]
SELECT gm.MEMBER_PORT, gms.TRANSACTIONS_COMMITTED_ALL_MEMBERS, gms.LAST_CONFLICT_FREE_TRANSACTION
FROM performance_schema.replication_group_member_stats gms 
JOIN performance_schema.replication_group_members gm 
ON gms.MEMBER_ID=gm.MEMBER_ID;
MEMBER_PORT	TRANSACTIONS_COMMITTED_ALL_MEMBERS	LAST_CONFLICT_FREE_TRANSACTION
13001	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-4	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4
13000	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-4	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4
include/stop_group_replication.inc
include/start_group_replication.inc
# Sleep for 120 secs
[connection server1]
#
# Check LAST_CONFLICT_FREE_TRANSACTION of M2 on M1.
SELECT gm.MEMBER_PORT, gms.TRANSACTIONS_COMMITTED_ALL_MEMBERS, gms.LAST_CONFLICT_FREE_TRANSACTION
FROM performance_schema.replication_group_member_stats gms 
JOIN performance_schema.replication_group_members gm 
ON gms.MEMBER_ID=gm.MEMBER_ID;
MEMBER_PORT	TRANSACTIONS_COMMITTED_ALL_MEMBERS	LAST_CONFLICT_FREE_TRANSACTION
13001	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4
13000	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4
[connection server2]
#
# ISSUE HERE # Check LAST_CONFLICT_FREE_TRANSACTION of M2 on M2.
SELECT gm.MEMBER_PORT, gms.TRANSACTIONS_COMMITTED_ALL_MEMBERS, gms.LAST_CONFLICT_FREE_TRANSACTION
FROM performance_schema.replication_group_member_stats gms
JOIN performance_schema.replication_group_members gm
ON gms.MEMBER_ID=gm.MEMBER_ID;
MEMBER_PORT	TRANSACTIONS_COMMITTED_ALL_MEMBERS	LAST_CONFLICT_FREE_TRANSACTION
13001	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5	
13000	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5	aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4
include/group_replication_end.inc

------------ MYSQLTEST OUTPUT END ------------

How to repeat:
Details:-
==========
Version checked: 8.0.5

Steps to repro:-
================
Run command: ./mtr group_replication.gr_bug2.test

where,

$ cat gr_bug2.test
--let $group_replication_group_name= aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
--source ../inc/have_group_replication_plugin.inc
--source ../inc/group_replication.inc

--let $rpl_connection_name= server1
--source include/rpl_connection.inc
CREATE TABLE t1 (a INT PRIMARY KEY);
DROP TABLE t1;

--echo # Sleep for 150 secs
--sleep 150

SELECT gm.MEMBER_PORT, gms.TRANSACTIONS_COMMITTED_ALL_MEMBERS, gms.LAST_CONFLICT_FREE_TRANSACTION
 FROM performance_schema.replication_group_member_stats gms
  JOIN performance_schema.replication_group_members gm
  ON gms.MEMBER_ID=gm.MEMBER_ID;

--let $rpl_connection_name= server2
--source include/rpl_connection.inc
SELECT gm.MEMBER_PORT, gms.TRANSACTIONS_COMMITTED_ALL_MEMBERS, gms.LAST_CONFLICT_FREE_TRANSACTION
 FROM performance_schema.replication_group_member_stats gms 
  JOIN performance_schema.replication_group_members gm 
  ON gms.MEMBER_ID=gm.MEMBER_ID;

--source include/stop_group_replication.inc
--source include/start_group_replication.inc

--echo # Sleep for 120 secs
--sleep 120

--let $rpl_connection_name= server1
--source include/rpl_connection.inc

--echo #
--echo # Check LAST_CONFLICT_FREE_TRANSACTION of M2 on M1.
SELECT gm.MEMBER_PORT, gms.TRANSACTIONS_COMMITTED_ALL_MEMBERS, gms.LAST_CONFLICT_FREE_TRANSACTION
 FROM performance_schema.replication_group_member_stats gms 
  JOIN performance_schema.replication_group_members gm 
  ON gms.MEMBER_ID=gm.MEMBER_ID;

--let $rpl_connection_name= server2
--source include/rpl_connection.inc

--echo #
--echo # ISSUE HERE # Check LAST_CONFLICT_FREE_TRANSACTION of M2 on M2.
SELECT gm.MEMBER_PORT, gms.TRANSACTIONS_COMMITTED_ALL_MEMBERS, gms.LAST_CONFLICT_FREE_TRANSACTION
 FROM performance_schema.replication_group_member_stats gms
  JOIN performance_schema.replication_group_members gm
  ON gms.MEMBER_ID=gm.MEMBER_ID;

--source ../inc/group_replication_end.inc
[23 Feb 2018 14:13] David Moss
Posted by developer:
 
Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 8.0.11 changelog:
The value of LAST_CONFLICT_FREE_TRANSACTION in the replication_group_member_stats Performance Schema table was not correctly synchronized across group members.
[31 May 2018 14:54] David Moss
Reclosing.
[31 May 2018 14:58] David Moss
Reclosing.