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: | |
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
[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.