MySQL Bugs: #100906: cannot execute read-only transaction when group

Bug #100906	cannot execute read-only transaction when group_replication in ERROR state
Submitted:	22 Sep 2020 8:38	Modified:	30 Oct 2020 11:12
Reporter:	phoenix Zhang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Group Replication	Severity:	S2 (Serious)
Version:	8.0.18, 8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When set group_replication_unreachable_majority_timeout=30s, after major nodes fail-over, other node will finally in ERROR state.

While, after the node become ERROR state, it cannot execute an read-only transaction, which will throw error 3796: ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED

like below:

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | b7f8a395-fca8-11ea-8b0b-ec5c6826bca3 | 127.0.0.1   |       13003 | ERROR        |             | 8.0.18         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

mysql> select * from t1;
ERROR 3796 (HY000): The option group_replication_consistency cannot be used on the current member state.

How to repeat:
Build an 3 nodes group_replication cluster, no matter single-primary-mode=ON or not.

and set group_replication_unreachable_majority_timeout=30 for all 3 nodes.

we assume the 3 nodes is:
1. n1 (primary)
2. n2 (primary if single-primary-mode=off, otherwise, secondly)
3. n3 (primary if single-primary-mode=off, otherwise, secondly)

Then:
1. CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT);
   INSERT INTO t1 VALUES (1);

2. use kill -9, kill n2 and n3

3. SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid, wait the state be ERROR

4. SELECT * FROM t1, it will throw error, error_code is 3796

group_replication_consistency=before_and_after in my test

when query information_schema.tables, it will still throw same error, as below:

mysql> select * from information_schema.tables;
ERROR 3796 (HY000): The option group_replication_consistency cannot be used on the current member state.

So, why should information_schema affect by group_replication ?

Hello phoenix Zhang!

Thank you for the report.

regards,
Umesh

MySQL Server 8.0.21 test results

Attachment: 100906_8.0.21.results (application/octet-stream, text), 17.92 KiB.

The restriction is as per design.
I_S table has many tables with metadata information which is subjected to change with ongoing transactions in a group.
So with higher consistency, it is better to block I_S tables in the non-online state.
If you still want to run a query, please use EVENTUAL consistency (at SESSION level possible).