Instances removed using 'remove-instance' action throw failure message with a blocked state

[Muhammad Ahmad]

If an instance is removed from the MySQL cluster using 'juju run-action remove-instance', the action throws back a failed response and the instance/unit results in a blocked state.

buntu@ahmadfsbd-bastion:~$ juju run-action mysql-innodb-cluster/0 remove-instance address=10.5.2.59
Action queued with id: "2"
ubuntu@ahmadfsbd-bastion:~$ juju show-action-output 2
UnitId: mysql-innodb-cluster/0
id: "2"
status: running
timing:
  enqueued: 2022-07-25 14:39:18 +0000 UTC
  started: 2022-07-25 14:39:18 +0000 UTC
ubuntu@ahmadfsbd-bastion:~$ juju show-action-output 2
UnitId: mysql-innodb-cluster/0
id: "2"
message: Remove instance failed
results:
  output: "Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory\nThe
    instance will be removed from the InnoDB cluster. Depending on the instance\nbeing
    the Seed or not, the Metadata session might become invalid. If so, please\nstart
    a new session to the Metadata Storage R/W instance.\n\nInstance '10.5.2.59:3306'
    is attempting to leave the cluster...\n\e[31mERROR: \e[0mInstance '10.5.2.59:3306'
    failed to leave the cluster: Variable 'group_replication_force_members' is a non
    persistent variable\nTraceback (most recent call last):\n File \"<string>\",
    line 3, in <module>\nmysqlsh.DBError: MySQL Error (1238): Cluster.remove_instance:
    Variable 'group_replication_force_members' is a non persistent variable\n"
  traceback: |
    Traceback (most recent call last):
      File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/charm/actions/remove-instance", line 299, in remove_instance
        output = instance.remove_instance(address, force=force)
      File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/charm/lib/charm/openstack/mysql_innodb_cluster.py", line 1030, in remove_instance
        raise e
      File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/charm/lib/charm/openstack/mysql_innodb_cluster.py", line 1006, in remove_instance
        output = self.run_mysqlsh_script(_script).decode("UTF-8")
      File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/charm/lib/charm/openstack/mysql_innodb_cluster.py", line 1703, in run_mysqlsh_script
        return subprocess.check_output(cmd, stderr=subprocess.PIPE)
      File "/usr/lib/python3.8/subprocess.py", line 415, in check_output
        return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,
      File "/usr/lib/python3.8/subprocess.py", line 516, in run
        raise CalledProcessError(retcode, process.args,
    subprocess.CalledProcessError: Command '['/snap/bin/mysqlsh', '--no-wizard', '--python', '-f', '/root/snap/mysql-shell/common/tmpqhdq7t5f.py']' returned non-zero exit status 1.
status: failed
timing:
  completed: 2022-07-25 14:39:26 +0000 UTC
  enqueued: 2022-07-25 14:39:18 +0000 UTC
  started: 2022-07-25 14:39:18 +0000 UTC

ubuntu@ahmadfsbd-bastion:~$ juju status
mysql-innodb-cluster/0* active idle 0 10.5.2.16 Unit is ready: Mode: R/W, Clust
er is ONLINE and can tolerate up to ONE failure. 1 member is not active.
mysql-innodb-cluster/1 active idle 1 10.5.1.91 Unit is ready: Mode: R/O, Clust
er is ONLINE and can tolerate up to ONE failure. 1 member is not active.
mysql-innodb-cluster/2 active idle 2 10.5.1.61 Unit is ready: Mode: R/O, Clust
er is ONLINE and can tolerate up to ONE failure. 1 member is not active.
mysql-innodb-cluster/3 blocked idle 3 10.5.2.59 Cluster is inaccessible from th
is instance. Please check logs for details.

The 'juju debug' shows the following error on the subject instances:

unit-mysql-innodb-cluster-3: 12:28:09 ERROR unit.mysql-innodb-cluster/3.juju-log Cluster is unavailable: Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory

Trying to add the instance back to the cluster using 'juju run-action rejoin-instance' shows a success response, however, the instance stays in a blocked state:

ubuntu@ahmadfsbd-bastion:~$ juju run-action mysql-innodb-cluster/0 rejoin-instance address=10.5.2.59
Action queued with id: "4"
ubuntu@ahmadfsbd-bastion:~$ juju show-action-output 4
UnitId: mysql-innodb-cluster/0
id: "4"
results:
  outcome: Success
  output: ""
status: completed
timing:
  completed: 2022-07-25 14:52:32 +0000 UTC
  enqueued: 2022-07-25 14:52:23 +0000 UTC
  started: 2022-07-25 14:52:23 +0000 UTC

Deployed Using Master Branch: https://opendev.org/openstack/charm-mysql-innodb-cluster.git
Channel: 8.0/stable