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
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