-- Observed that for large transactions there is a noticeable delay but eventually data is syncing and reflecting on other nodes.
-- With default settings, frequently node ejected from the cluster for the large transactions.
-- Scaled down the test case to avoid seeing ERROR 3101

rm -rf data
mkdir data
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/data/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/data/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/data/

bin/mysqld --defaults-file=./hod03.cnf --log-error=hod03.err --log_error_verbosity=3 --log_warnings=2 2>&1 &
bin/mysqld --defaults-file=./hod04.cnf --log-error=hod04.err --log_error_verbosity=3 --log_warnings=2 2>&1 &
bin/mysqld --defaults-file=./hod06.cnf --log-error=hod06.err --log_error_verbosity=3 --log_warnings=2 2>&1 &

################# Configure, Start GR

- hod03(node 1)

 CREATE USER 'rpl_user'@'%' IDENTIFIED BY 'rpl_pass';
 GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
 SHOW GLOBAL VARIABLES LIKE 'group_replication%';
 SET GLOBAL group_replication_bootstrap_group=1;
 CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
 START GROUP_REPLICATION;
 SET GLOBAL group_replication_bootstrap_group=0;
 SELECT * FROM performance_schema.replication_connection_status\G
 SELECT * FROM performance_schema.replication_group_member_stats\G
 SELECT * FROM performance_schema.replication_group_members;
 

- hod04/hod06(node 2 and 3)
 SHOW GLOBAL VARIABLES LIKE 'group_replication%';
 CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
 START GROUP_REPLICATION;
 SELECT * FROM performance_schema.replication_connection_status\G
 SELECT * FROM performance_schema.replication_group_member_stats\G
 SELECT * FROM performance_schema.replication_group_members;
 
 -- node 1
create database if not exists test;
use test;
drop table if exists t1;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

drop table if exists t2;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

- Had to break dump file to avoid seeing ERROR 3101
- perl script to generate dummy data

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.17: cat 84900.pl
#!/bin/perl

for(my $i=1; $i <= 10000000; $i++) {
  print "$i,200\n";
}


load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.7.17/84900.dmp' into table t1 fields terminated by ',';
load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.7.17/84900_2.dmp' into table t1 fields terminated by ',';
load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.7.17/84900_3.dmp' into table t1 fields terminated by ',';

load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.7.17/84900.dmp' into table t2 fields terminated by ',';
load data local infile '/export/umesh/server/binaries/GABuilds/mysql-5.7.17/84900_2.dmp' into table t2 fields terminated by ',';

- node 1

root@localhost [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 30000000 |
+----------+
1 row in set (6.37 sec)

root@localhost [test]> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 20000000 |
+----------+
1 row in set (3.14 sec)


root@localhost [test]> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 20000000 |
+----------+
1 row in set (3.14 sec)

root@localhost [test]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 6318834d-eea5-11e6-b9d4-0010e0734b98 | hod06       |        3306 | ONLINE       |
| group_replication_applier | 64a7be34-eea5-11e6-adee-0010e05f4178 | hod04       |        3306 | ONLINE       |
| group_replication_applier | 6632df88-eea5-11e6-97b2-0010e05f3e06 | hod03       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> update t1 set balance=2000 where id < 2000000;
Query OK, 1999999 rows affected (29.57 sec)
Rows matched: 1999999  Changed: 1999999  Warnings: 0

root@localhost [test]> update t2 set balance=2000 where id < 2000000;
Query OK, 1999999 rows affected (29.64 sec)
Rows matched: 1999999  Changed: 1999999  Warnings: 0

root@localhost [test]> commit;
Query OK, 0 rows affected (25.59 sec)

root@localhost [test]> select now();select count(*) from t1 where balance=2000;
+---------------------+
| now()               |
+---------------------+
| 2017-02-09 11:14:27 |
+---------------------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|  1999999 |
+----------+
1 row in set (7.20 sec)

- node 2


root@localhost [test]> select now();select count(*) from t1 where balance=2000;
+---------------------+
| now()               |
+---------------------+
| 2017-02-09 11:14:36 |
+---------------------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (8.91 sec)

.
.

root@localhost [test]> select now();select count(*) from t1 where balance=2000;
+---------------------+
| now()               |
+---------------------+
| 2017-02-09 11:16:39 |
+---------------------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|  1999999 |
+----------+
1 row in set (7.07 sec)

######## Bug#84901

This issue is seen even with any other high load transactions, after this node is no longer in cluster:

root@localhost [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 30000000 |
+----------+
1 row in set (4.95 sec)

root@localhost [test]> create table t2 like t1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@localhost [test]> insert into t2 (balance) select balance from t1;
ERROR 3101 (HY000): Plugin instructed the server to rollback the current transaction.
root@localhost [test]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | b8175e20-ee9f-11e6-930c-0010e05f3e06 | hod03       |        3306 | ERROR        |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

-- excerpt from error log when member state becomes error

2017-02-09T09:38:24.166239Z 0 [ERROR] Plugin group_replication reported: 'Member was expelled from the group due to network failures, changing member status to ERROR.'
2017-02-09T09:38:24.166365Z 0 [Warning] Plugin group_replication reported: 'Due to a plugin error, some transactions can't be certified and will now rollback.'