MySQL Bugs: #100156: XA COMMIT still work after stop group

Bug #100156	XA COMMIT still work after stop group_replication
Submitted:	8 Jul 2020 10:30	Modified:	8 Jul 2020 15:21
Reporter:	phoenix Zhang (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: XA transactions	Severity:	S3 (Non-critical)
Version:	8.0.18, 8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	group_replication, xa

Description:
In group_replication, if a node do STOP GROUP_REPLICATION, the node will be read-only, and cannot execute write operation like INSERT.

While XA COMMIT still work normal. 

How to repeat:
1. start group_replication
2. conn1:  xa start '1'; insert into t1 values(1); xa end '1'; xa prepare '1';
3. disconnect conn1
4. stop group_replication
5. conn2: xa commit '1'

Suggested fix:
After stop group_replication, it should not execute any xa transaction.

test file, need move to mysql-test/suite/group_replication/t

Attachment: gr_xa_commit_read_only_bug.test (application/octet-stream, text), 894 bytes.

run the test-case with below command:

./mtr group_replication.gr_xa_commit_read_only_bug --nocheck-testcase

The output will be:

include/group_replication.inc [rpl_server_count=2]
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]
[connect conn1]
select * from performance_schema.replication_group_members;
CHANNEL_NAME	MEMBER_ID	MEMBER_HOST	MEMBER_PORT	MEMBER_STATE	MEMBER_ROLE	MEMBER_VERSION
group_replication_applier	3a436cd6-c106-11ea-becb-c8f7507e5048	127.0.0.1	13000	ONLINE	PRIMARY	8.0.18
group_replication_applier	3a43bcef-c106-11ea-a1ed-c8f7507e5048	127.0.0.1	13001	ONLINE	PRIMARY	8.0.18
SELECT @@super_read_only;
@@super_read_only
0
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
INSERT INTO t1 VALUES (1,1);
[connect conn1_1]
XA START '1';
INSERT INTO t1 VALUES (2,2);
XA END '1';
XA PREPARE '1';
[connect conn1_2]
STOP GROUP_REPLICATION;
SELECT @@super_read_only;
@@super_read_only
1
INSERT INTO t1 VALUES (3,3);
ERROR HY000: The MySQL server is running with the --super-read-only option so it cannot execute this statement
XA COMMIT '1';
SELECT * FROM t1;
c1	c2
1	1
2	2

From above result, after STOP GROUP_REPLICATION, @@spuer_read_only become on, and cannot do INSERT. While XA COMMIT success.

Hello phoenix Zhang!

Thank you for the report.

regards,
Umesh

Duplicate of BUG#91646: xa command still operation when super_read_only is true