| Bug #94182 | not delete mysql_innodb_cluster_rxxx@'localhost' | ||
|---|---|---|---|
| Submitted: | 3 Feb 2019 11:49 | Modified: | 29 Mar 2019 11:14 |
| Reporter: | Bin Hong (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Shell AdminAPI InnoDB Cluster / ReplicaSet | Severity: | S3 (Non-critical) |
| Version: | 8.0.14 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | contributions | ||
[3 Feb 2019 11:50]
Bin Hong
add drop user with localhost
Attachment: drop_user.patch (application/octet-stream, text), 688 bytes.
[3 Feb 2019 11:54]
Bin Hong
modules/adminapi/mod_dba.cc
if (!replication_user.empty()) {
log_debug("Removing replication user '%s'", replication_user.c_str());
group_session->query("DROP USER IF EXISTS /*(*/" + replication_user +
"@'localhost' /*)*/");
group_session->query("DROP USER IF EXISTS /*(*/" + replication_user +
"@'%' /*)*/");
[4 Feb 2019 9:48]
MySQL Verification Team
Hello Bin Hong, Thank you for the report and contribution. Please ensure to re-send the patch via "Contributions" tab. Otherwise we would not be able to accept it. regards, Umesh
[5 Feb 2019 1:28]
Bin Hong
drop user localhost and % (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: drop_user.patch (application/octet-stream, text), 800 bytes.
[29 Mar 2019 11:14]
David Moss
Posted by developer: Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 8.0.16 changelog: In the event that dba.createCluster() failed and a rollback was performed to remove the created replication (recovery) users, the account created at localhost and any of the ipWhitelist addresses were not being removed. The fix ensures that the replication accounts are removed whenever a rollback related to dba.createCluster() is performed. This work was based on a code contribution from Bin Hong.
[16 May 2019 8:17]
Frederic Descamps
Hi Bin, Since the replication (recovery) users are created based on the 'ipWhitelist' (since 8.0.12 GA), whenever the option 'ipWhitelist' is used this patch won't work. Both scenarios (using or not 'ipWhitelist') must be considered thus a different patch has been pushed. Thank for the bug report and the contribution proposal.
Description: dba.createCluster failed, delete the account is not complete when rolling back, leaving mysql_innodb_cluster_rxxx@'localhost' How to repeat: 2019-02-03T03:04:36.737566Z 36 Query SELECT @@super_read_only 2019-02-03T03:04:36.737741Z 36 Query SET GLOBAL super_read_only = 'OFF' 2019-02-03T03:04:36.737893Z 36 Query SHOW MASTER STATUS 2019-02-03T03:04:36.738034Z 36 Query show databases like 'mysql_innodb_cluster_metadata' 2019-02-03T03:04:36.738944Z 36 Query CREATE USER IF NOT EXISTS 'mysql_innodb_cluster_r4916307673'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>' 2019-02-03T03:04:36.749383Z 36 Query GRANT REPLICATION SLAVE ON *.* TO 'mysql_innodb_cluster_r4916307673'@'%' 2019-02-03T03:04:36.756773Z 36 Query CREATE USER IF NOT EXISTS 'mysql_innodb_cluster_r4916307673'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '<secret>' 2019-02-03T03:04:36.763698Z 36 Query GRANT REPLICATION SLAVE ON *.* TO 'mysql_innodb_cluster_r4916307673'@'localhost' 2019-02-03T03:04:36.770374Z 36 Query start transaction 2019-02-03T03:04:36.770625Z 36 Query show databases like 'mysql_innodb_cluster_metadata' 2019-02-03T03:04:36.771066Z 36 Query INSERT INTO mysql_innodb_cluster_metadata.clusters (cluster_name, description, options, attributes) VALUES ('cluster2', 'Default Cluster', 'null', '{\"default\":true}') 2019-02-03T03:04:36.771802Z 36 Query INSERT INTO mysql_innodb_cluster_metadata.replicasets (cluster_id, replicaset_type, topology_type, replicaset_name, active, attributes) VALUES (8, 'gr', 'pm', 'default', 1,JSON_OBJECT('adopted', '0', 'group_replication_group_name', '')) 2019-02-03T03:04:36.772140Z 36 Query UPDATE mysql_innodb_cluster_metadata.clusters SET default_replicaset = 8 WHERE cluster_id = 8 2019-02-03T03:04:36.773309Z 36 Query SELECT COUNT(*) as count FROM mysql_innodb_cluster_metadata.instances WHERE replicaset_id = 8 2019-02-03T03:04:36.775538Z 37 Connect root@localhost on using TCP/IP 2019-02-03T03:04:36.775715Z 37 Query SHOW GLOBAL VARIABLES 2019-02-03T03:04:36.777295Z 37 Query SELECT @@datadir 2019-02-03T03:04:36.778213Z 37 Quit 2019-02-03T03:04:36.778293Z 36 Query rollback 2019-02-03T03:04:36.778818Z 36 Query DROP USER IF EXISTS /*(*/mysql_innodb_cluster_r4916307673/*)*/ MySQL localhost:20926 SQL > select user,host from mysql.user; +----------------------------------+-----------+ | user | host | +----------------------------------+-----------+ | msandbox | 127.% | | msandbox_ro | 127.% | | msandbox_rw | 127.% | | rsandbox | 127.% | | msandbox | localhost | | msandbox_ro | localhost | | msandbox_rw | localhost | | mysql.session | localhost | | mysql.sys | localhost | | mysql_innodb_cluster_r4916307673 | localhost | | root | localhost | Suggested fix: if (!replication_user.empty()) { log_debug("Removing replication user '%s'", replication_user.c_str()); group_session->query("DROP USER IF EXISTS /*(*/" + replication_user + "'@'localhost' /*)*/"); group_session->query("DROP USER IF EXISTS /*(*/" + replication_user + "'@'%' /*)*/");