Bug #85047 Secondaries allows transactions through ASYNC setup into the group
Submitted: 17 Feb 2017 11:03 Modified: 12 Jun 2017 15:34
Reporter: Narendra Chauhan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Group Replication Severity:S2 (Serious)
Version:5.7.18 OS:Any
Assigned to: CPU Architecture:Any

[17 Feb 2017 11:03] Narendra Chauhan
Description:
Scenario: Form a Group of S1 <-> S2 and ASYNC setup of S2 <- S3 and test what happens in Single Primary Mode where Primary is S1.
Actual Output:- Although S2 is part of the group and Read-Only member, it still accepts transactions from S3 and thus the same is available to the group.

MTR testcase output:-
======================
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13019
include/group_replication.inc [rpl_server_count=3]
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]
#
# Bootstrap start GR on server1 (Primary).
#
[connection server1]
include/start_and_bootstrap_group_replication.inc
CREATE TABLE test.grt1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO test.grt1 VALUES (1);
[connection server2]
include/start_group_replication.inc
CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='root',
MASTER_PORT=13002 for channel 'ch3_2';
Warnings:
Note	1759	Sending passwords in plain text without SSL/TLS is extremely insecure.
Note	1760	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.
START SLAVE FOR CHANNEL 'ch3_2';
INSERT INTO test.grt1 VALUES (2);
ERROR HY000: The MySQL server is running with the --super-read-only option so it cannot execute this statement

[connection server3]
SHOW SLAVE HOSTS;
Server_id	Host	Port	Master_id	Slave_UUID
CREATE TABLE test.asynct1 (c1 INT NOT NULL) ENGINE=InnoDB;
INSERT INTO test.asynct1 VALUES (1);
[connection server2]
SHOW TABLES in test;
Tables_in_test
asynct1
grt1
SELECT COUNT(*) FROM test.asynct1;
COUNT(*)
1
[connection server1]
SHOW TABLES in test;
Tables_in_test
asynct1
grt1
SELECT COUNT(*) FROM test.asynct1;
COUNT(*)
1
[connection server3]
DROP TABLE test.asynct1;
[connection server1]
DROP TABLE test.grt1;
include/group_replication_end.inc
group_replication.gr_secondary_ro        [ pass ]  34604
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 34.604 of 109 seconds executing testcases

How to repeat:
Details:-
=========
MySQL Version: 5.7.18
commit e6bf12fd793de8f183555e79dae042e8246b2f2a

Steps to repro:-
=================
Run command:- ./mtr group_replication.gr_secondary_ro.test
where,
$ cat gr_secondary_ro.cnf 
!include ../my.cnf

[mysqld.1]

[mysqld.2]

[mysqld.3]

[ENV]
SERVER_MYPORT_3=		@mysqld.3.port
SERVER_MYSOCK_3=		@mysqld.3.socket

$ cat gr_secondary_ro.test
--source ../inc/have_group_replication_plugin.inc
--let $rpl_skip_group_replication_start= 1
--let $rpl_server_count= 3
--let $rpl_group_replication_single_primary_mode=1
--source ../inc/group_replication.inc

--echo #
--echo # Bootstrap start GR on server1 (Primary).
--echo #
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
--source ../inc/start_and_bootstrap_group_replication.inc

# Execute some transactions on server1
CREATE TABLE test.grt1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO test.grt1 VALUES (1);

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

# Make server2 as Slave of server3
eval CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='root',
     MASTER_PORT=$SERVER_MYPORT_3 for channel 'ch3_2';
START SLAVE FOR CHANNEL 'ch3_2';

# Make sure server2 is RO
--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO test.grt1 VALUES (2);

--echo
--let $rpl_connection_name= server3
--source include/rpl_connection.inc
SHOW SLAVE HOSTS;

CREATE TABLE test.asynct1 (c1 INT NOT NULL) ENGINE=InnoDB;
INSERT INTO test.asynct1 VALUES (1);

--sleep 2

--let $rpl_connection_name= server2
--source include/rpl_connection.inc
SHOW TABLES in test;
SELECT COUNT(*) FROM test.asynct1;

--let $rpl_connection_name= server1
--source include/rpl_connection.inc
SHOW TABLES in test;
SELECT COUNT(*) FROM test.asynct1;

# Clean up.
--let $rpl_connection_name= server3
--source include/rpl_connection.inc
DROP TABLE test.asynct1;

--let $rpl_connection_name= server1
--source include/rpl_connection.inc
DROP TABLE test.grt1;

--source ../inc/group_replication_end.inc
[12 Jun 2017 15:34] David Moss
Posted by developer:
 
Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 5.7.19 / 8.0.2 change logs:
In a set up where single-primary Group Replication was combined with asynchronous replication, for example with S1 and S2 forming a group and with S2 and S3 functioning as master and slave, secondaries such as S2 were accepting transactions and these could then enter the group. The fix prevents secondaries creating an asynchronous replication channel when belonging to a single-primary group, and Group Replication cannot be started when asynchronous replication is running.