Description:
If you use a lower-case instance name in cluster.removeInstance, the instance will have group replication stopped, but will not be removed from the cluster metadata.
I worked with Miguel Araujo on this, here are some further details:
verbose: 2021-02-12T09:39:54Z: instance01:3306: DELETE FROM mysql_innodb_cluster_metadata.instances WHERE addresses->'$.mysqlClassic' = 'instance02:3306'
This query doesn't do anything, because this is what's registered in the metadata:
addresses: {"mysqlX": "INSTANCE02:33060", "grLocal": "INSTANCE02:33061", "mysqlClassic": "INSTANCE02:3306"}
But then shell issues this query on that instance: STOP GROUP_REPLICATION, so group_replication stops, and the instance leaves the group, but it's not removed from the metadata and that's why it's shown as (MISSING) in .status()
How to repeat:
* Create a 3 node InnoDB Cluster
* Run dba.removeInstance('instance02'), AdminAPI will state that the instance was successfully removed
* Run cluster.status(), the instance is still listed and shown as offline
* If you do cluster.rescan() and choose yes when prompted to remove the missing instance, it will be removed from the metadata
* If you then add it back with cluster.addInstance(), and then do cluster.removeInstance('INSTANCE02'), it will be removed from the cluster metadata successfully
Suggested fix:
Don't allow the user to run dba.removeInstance with a lower-case instance name.
Description: If you use a lower-case instance name in cluster.removeInstance, the instance will have group replication stopped, but will not be removed from the cluster metadata. I worked with Miguel Araujo on this, here are some further details: verbose: 2021-02-12T09:39:54Z: instance01:3306: DELETE FROM mysql_innodb_cluster_metadata.instances WHERE addresses->'$.mysqlClassic' = 'instance02:3306' This query doesn't do anything, because this is what's registered in the metadata: addresses: {"mysqlX": "INSTANCE02:33060", "grLocal": "INSTANCE02:33061", "mysqlClassic": "INSTANCE02:3306"} But then shell issues this query on that instance: STOP GROUP_REPLICATION, so group_replication stops, and the instance leaves the group, but it's not removed from the metadata and that's why it's shown as (MISSING) in .status() How to repeat: * Create a 3 node InnoDB Cluster * Run dba.removeInstance('instance02'), AdminAPI will state that the instance was successfully removed * Run cluster.status(), the instance is still listed and shown as offline * If you do cluster.rescan() and choose yes when prompted to remove the missing instance, it will be removed from the metadata * If you then add it back with cluster.addInstance(), and then do cluster.removeInstance('INSTANCE02'), it will be removed from the cluster metadata successfully Suggested fix: Don't allow the user to run dba.removeInstance with a lower-case instance name.