GRANT without existing user leads to disconnected PXC node
General
Escalation
General
Escalation
Description
Environment
None
Activity
Show:
Kamil Holubicki
July 31, 2023 at 10:30 AM
The same for GRANT PROXY ON 'employee'@'localhost' TO 'employee_ext'@'localhost';
Venkatesh Prasad
July 31, 2023 at 6:04 AM
I was able to reproduce with the below steps
1. Start a 3 node cluster with --skip-name-resolve option
./mtr --suite=galera_3nodes --start --mysqld=--skip-name-resolve
2. Connect to any node using socket (as connecting over ip 127.0.0.1 is prohibited) and run the below query
ndoe1> GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
Logs
2023-07-31T06:04:09.210319Z 0 [Note] [MY-000000] [Galera] Member 0(node_1) initiates vote on d8360c85-2f67-11ee-a47d-92475a9337f4:4,be7a76efeb3c03b4: MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work, Error_code: 1285; You are not allowed to create a user with GRANT, Error_code: 1410;
2023-07-31T06:04:09.210346Z 0 [Note] [MY-000000] [Galera] Votes over d8360c85-2f67-11ee-a47d-92475a9337f4:4:
be7a76efeb3c03b4: 1/3
Waiting for more votes.
2023-07-31T06:04:09.213653Z 0 [Note] [MY-000000] [Galera] Member 1(node_2) initiates vote on d8360c85-2f67-11ee-a47d-92475a9337f4:4,c1c8a64419f4ff4a: You are not allowed to create a user with GRANT, Error_code: 1410;
2023-07-31T06:04:09.213673Z 0 [Note] [MY-000000] [Galera] Votes over d8360c85-2f67-11ee-a47d-92475a9337f4:4:
be7a76efeb3c03b4: 1/3
c1c8a64419f4ff4a: 1/3
Waiting for more votes.
2023-07-31T06:04:09.214924Z 0 [Note] [MY-000000] [Galera] Member 2(node_3) initiates vote on d8360c85-2f67-11ee-a47d-92475a9337f4:4,c1c8a64419f4ff4a: You are not allowed to create a user with GRANT, Error_code: 1410;
2023-07-31T06:04:09.214942Z 0 [Note] [MY-000000] [Galera] Votes over d8360c85-2f67-11ee-a47d-92475a9337f4:4:
be7a76efeb3c03b4: 1/3
c1c8a64419f4ff4a: 2/3
Winner: c1c8a64419f4ff4a
2023-07-31T06:04:09.215031Z 23 [ERROR] [MY-000000] [Galera] Inconsistency detected: Inconsistent by consensus on d8360c85-2f67-11ee-a47d-92475a9337f4:4
at /home/venki/work/pxc/80/percona-xtradb-cluster-galera/galera/src/replicator_smm.cpp:process_apply_error():1469
at /home/venki/work/pxc/80/percona-xtradb-cluster-galera/galera/src/replicator_smm.cpp:handle_apply_error():1496
2023-07-31T06:04:09.215069Z 23 [Note] [MY-000000] [Galera] Closing send monitor...
2023-07-31T06:04:09.215082Z 23 [Note] [MY-000000] [Galera] Closed send monitor.
Done
Details
Details
Assignee
Unassigned
UnassignedReporter
Thomas Babut
Thomas BabutNeeds Review
Yes
Needs QA
Yes
Fix versions
Affects versions
Priority
Created July 29, 2023 at 12:38 PM
Updated March 6, 2024 at 8:37 PM
Resolved October 16, 2023 at 6:40 AM
When you forget to create a MySQL user prior to the GRANT option, the PXC node gets disconnected and needs a full state transfer (SST):
mysql> GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost'; ERROR 1410 (42000): You are not allowed to create a user with GRANT
MySQL/Galera Log:
2023-07-29T11:40:27.460740Z 0 [Note] [MY-000000] [Galera] Member 1(test-pxc1) initiates vote on 0052e5ff-2709-11ee-af33-e7550c1533ae:249556,91681201f5859 761: MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work, Error_code: 1285; You are not allowed to create a user with GRANT, Error_code: 1410; 2023-07-29T11:40:27.460927Z 0 [Note] [MY-000000] [Galera] Votes over 0052e5ff-2709-11ee-af33-e7550c1533ae:249556: 91681201f5859761: 1/3 Waiting for more votes. 2023-07-29T11:40:27.464548Z 0 [Note] [MY-000000] [Galera] Member 0(test-pxc2) initiates vote on 0052e5ff-2709-11ee-af33-e7550c1533ae:249556,eb1b799a88e66 d19: You are not allowed to create a user with GRANT, Error_code: 1410; 2023-07-29T11:40:27.464611Z 0 [Note] [MY-000000] [Galera] Votes over 0052e5ff-2709-11ee-af33-e7550c1533ae:249556: 91681201f5859761: 1/3 eb1b799a88e66d19: 1/3 Waiting for more votes. 2023-07-29T11:40:27.471234Z 0 [Note] [MY-000000] [Galera] Member 2(test-pxc3) initiates vote on 0052e5ff-2709-11ee-af33-e7550c1533ae:249556,eb1b799a88e66d19: You are not allowed to create a user with GRANT, Error_code: 1410; 2023-07-29T11:40:27.471340Z 0 [Note] [MY-000000] [Galera] Votes over 0052e5ff-2709-11ee-af33-e7550c1533ae:249556: 91681201f5859761: 1/3 eb1b799a88e66d19: 2/3 Winner: eb1b799a88e66d19 2023-07-29T11:40:27.471571Z 379718 [ERROR] [MY-000000] [Galera] Inconsistency detected: Inconsistent by consensus on 0052e5ff-2709-11ee-af33-e7550c1533ae:249556