Bug #104118 MGR all second node abnormal stop
Submitted: 26 Jun 2021 2:42 Modified: 29 Jun 2021 2:25
Reporter: peng gao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S2 (Serious)
Version:8.0.24 OS:Any
Assigned to: CPU Architecture:Any

[26 Jun 2021 2:42] peng gao
Description:
Hi all:
   our MGR encounter an case,two second node is stop only primary ok.then we restart two second node's group_replication,
   MGR all node status is recovery and online,the error like :
   
2021-06-25T22:23:27.978680-04:00 12 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': Error 'You are not allowed to create a user with GRANT' on query. Default database: 'performance_schema'. Query: 'GRANT SELECT, INSERT, UPDATE, DELETE ON `gr_opt`.* TO 'testuser'@'192.168.1.100'', Error_code: MY-001410
2021-06-25T22:23:27.978759-04:00 12 [Warning] [MY-010584] [Repl] Slave: You are not allowed to create a user with GRANT Error_code: MY-001410
2021-06-25T22:23:27.978786-04:00 12 [ERROR] [MY-011451] [Repl] Plugin group_replication reported: 'The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group.'
2021-06-25T22:23:27.978891-04:00 12 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'FIRST' position 0
2021-06-25T22:23:27.978987-04:00 9 [ERROR] [MY-011452] [Repl] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'
2021-06-25T22:23:27.979120-04:00 9 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'
2021-06-25T22:23:31.773679-04:00 0 [System] [MY-011504] [Repl] Plugin group_replication reported: 'Group membership changed: This member has left the group.'
   
This case is update mysql.user table (see repeat detail).

I know not recommend update mysql.user table use DML statement use drop/create user to replace.
But in Innodb cluster high load stop/start group replication is heavy operation , update mysql.user direct may cause all second node stop.

Grant privileges,find_acl_user funcation search user from cache Acl_user_ptr_list,but update mysql.user table not update this cache,
when error encounter,then load cache again,so frist grant is fail,then grant is success. 
But second node applier channel sql thread apply this statment will report an error and stop group replication. 

Can we add DML verifiy in group_replication_trans_before_dml callback function to verify like this systemtable DML operation,primary node not allow update mysql.user table(or other privilegs tables)? i think this is better way to 
solve this issue.

thanks.

How to repeat:
MGR single primary mode

1.inital

mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5435c165-728d-11eb-ae01-000c2956fa4f | mgr5        |        3329 | ONLINE       | SECONDARY   | 8.0.23         |
| group_replication_applier | c32955c5-7d5d-11eb-865d-000c29948de2 | mgr4        |        3329 | ONLINE       | PRIMARY     | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

2.primary node:

create user testuser identified with mysql_native_password by 'TT123t$';
update mysql.user set host='192.168.1.100' where user='testuser' ;
grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';(error,use cache verify user then reload cache from mysql.user)
grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';(normal,cache reload from mysql.user)

3.all second node is stop.

mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c32955c5-7d5d-11eb-865d-000c29948de2 | mgr4        |        3329 | ONLINE       | PRIMARY     | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

Suggested fix:
Can we add DML verifiy in group_replication_trans_before_dml callback function to verify like this system
table DML operation,primary node not allow update mysql.user table(or other privilegs tables).this is better way to 
solve this issue.
[26 Jun 2021 2:44] peng gao
no one
[26 Jun 2021 2:48] peng gao
thanks
[28 Jun 2021 7:24] MySQL Verification Team
Hi,

I'm not sure I follow. You are running 2 node GR or 3 node GR. In your description, you say you shutdown two read-only nodes if I understood correctly but then in how to reproduce you show a 2 node GR.

How did you setup the GR and how many nodes you actually have.

You reproduction scenario
1. You have 2 node GR (this is actually not enough for GR, your start state must have at least 3 nodes)
2. You create a user
3. You shutdown non-primary nodes (how do you shut them down? mysqladmin or kill -9 or poweroff or unplug ethernet or?)

I did this and I don't see any issues.
Can you please clarify, thanks.
[28 Jun 2021 7:56] yongliang lee
sorry I Not very well described.

MGR signal primary mode:

1.inital

mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5435c165-728d-11eb-ae01-000c2956fa4f | mgr5        |        3329 | ONLINE       | SECONDARY   | 8.0.23         |
| group_replication_applier | c32955c5-7d5d-11eb-865d-000c29948de2 | mgr4        |        3329 | ONLINE       | PRIMARY     | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

2.primary node:

create user testuser identified with mysql_native_password by 'TT123t$';
update mysql.user set host='192.168.1.100' where user='testuser' ;
grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';
grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';

now check second err log, group replication is abort!! error log like:

2021-06-25T22:23:27.978680-04:00 12 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': 
Error 'You are not allowed to create a user with GRANT' on query. Default database: 'performance_schema'. Query: 'GRANT SELECT, INSERT, UPDATE, DELETE ON `gr_opt`.* TO 'testuser'@'192.168.1.100'', Error_code: MY-001410

operation detial:

mysql> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | c32955c5-7d5d-11eb-865d-000c29948de2 |
+---------------+--------------------------------------+
1 row in set (0.02 sec)

mysql> select * from replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5435c165-728d-11eb-ae01-000c2956fa4f | mgr5        |        3329 | ONLINE       | SECONDARY   | 8.0.23         |
| group_replication_applier | c32955c5-7d5d-11eb-865d-000c29948de2 | mgr4        |        3329 | ONLINE       | PRIMARY     | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

mysql> drop user testuser@'192.168.1.100';
Query OK, 0 rows affected (0.05 sec)

mysql> create user testuser identified with mysql_native_password by 'TT123t$';
Query OK, 0 rows affected (0.01 sec)

mysql> update mysql.user set host='192.168.1.100' where user='testuser' ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c32955c5-7d5d-11eb-865d-000c29948de2 | mgr4        |        3329 | ONLINE       | PRIMARY     | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

thanks for  verification like this step again.
[28 Jun 2021 7:57] peng gao
sorry I Not very well described.

MGR signal primary mode:

1.inital

mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5435c165-728d-11eb-ae01-000c2956fa4f | mgr5        |        3329 | ONLINE       | SECONDARY   | 8.0.23         |
| group_replication_applier | c32955c5-7d5d-11eb-865d-000c29948de2 | mgr4        |        3329 | ONLINE       | PRIMARY     | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

2.primary node:

create user testuser identified with mysql_native_password by 'TT123t$';
update mysql.user set host='192.168.1.100' where user='testuser' ;
grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';
grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';

now check second err log, group replication is abort!! error log like:

2021-06-25T22:23:27.978680-04:00 12 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': 
Error 'You are not allowed to create a user with GRANT' on query. Default database: 'performance_schema'. Query: 'GRANT SELECT, INSERT, UPDATE, DELETE ON `gr_opt`.* TO 'testuser'@'192.168.1.100'', Error_code: MY-001410

operation detial:

mysql> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | c32955c5-7d5d-11eb-865d-000c29948de2 |
+---------------+--------------------------------------+
1 row in set (0.02 sec)

mysql> select * from replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5435c165-728d-11eb-ae01-000c2956fa4f | mgr5        |        3329 | ONLINE       | SECONDARY   | 8.0.23         |
| group_replication_applier | c32955c5-7d5d-11eb-865d-000c29948de2 | mgr4        |        3329 | ONLINE       | PRIMARY     | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

mysql> drop user testuser@'192.168.1.100';
Query OK, 0 rows affected (0.05 sec)

mysql> create user testuser identified with mysql_native_password by 'TT123t$';
Query OK, 0 rows affected (0.01 sec)

mysql> update mysql.user set host='192.168.1.100' where user='testuser' ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> grant select,update,insert,delete on gr_opt.* to testuser@'192.168.1.100';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c32955c5-7d5d-11eb-865d-000c29948de2 | mgr4        |        3329 | ONLINE       | PRIMARY     | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

thanks for  verification like this step again.
[28 Jun 2021 8:00] peng gao
MGR single primary mode
[28 Jun 2021 8:04] peng gao
this is second node err log:
2021-06-28T03:53:06.858274-04:00 12 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': Error 'You are not allowed to create a user with GRANT' on query. Default database: 'performance_schema'. Query: 'GRANT SELECT, INSERT, UPDATE, DELETE ON `gr_opt`.* TO 'testuser'@'192.168.1.100'', Error_code: MY-001410
[28 Jun 2021 16:48] MySQL Verification Team
Hi,

Thanks for the report

Bogdan Kecman
[29 Jun 2021 2:25] peng gao
thanks
[23 Aug 2022 8:45] Ankur Shukla
Hi, is there any update pn this?
We are using v8.0.23, with a 3-node group replication cluster. We ran into same issue.