cannot add units if current leader is the highest numbered

[Rodrigo Barbieri]

latest/edge rev 89

Upon deploying a 3-unit cluster, the cluster cannot be scaled out (juju add-unit) if the current leader is the highest numbered unit. In such case, the new units get stuck in:

App Version Status Scale Charm Channel Rev Exposed Message
mysql 8.0.34 waiting 6 mysql-innodb-cluster latest/edge 89 no Instance not yet configured for clustering

Unit Workload Agent Machine Public address Ports Message
mysql/0 active idle 0 10.5.0.39 Unit is ready: Mode: R/O, Cluster is ONLINE and can tolerate up to ONE failure.
mysql/1 active idle 1 10.5.1.75 Unit is ready: Mode: R/O, Cluster is ONLINE and can tolerate up to ONE failure.
mysql/2* active idle 2 10.5.0.55 Unit is ready: Mode: R/W, Cluster is ONLINE and can tolerate up to ONE failure.
mysql/3 waiting idle 3 10.5.1.254 Instance not yet configured for clustering
mysql/4 waiting idle 4 10.5.1.137 Instance not yet configured for clustering
mysql/5 waiting idle 5 10.5.2.59 Instance not yet configured for clustering

There are no errors in the added units, but in the leader there is an error for each unit:

2023-10-13 14:28:13 INFO unit.mysql/2.juju-log server.go:316 cluster:1: Adding instance, 10.5.1.254, to the cluster.
2023-10-13 14:28:13 ERROR unit.mysql/2.juju-log server.go:316 cluster:1: Failed adding instance 10.5.1.254 to cluster: Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
^[[31mERROR: ^[[0mUnable to connect to the target instance '10.5.1.254:3306'. Please verify the connection settings, make sure the instance is available and try again.
Traceback (most recent call last):
  File "<string>", line 3, in <module>
mysqlsh.DBError: MySQL Error (1130): Cluster.add_instance: Could not open connection to '10.5.1.254:3306': Host '10.5.0.55' is not allowed to connect to this MySQL server

2023-10-13 14:28:14 INFO unit.mysql/2.juju-log server.go:316 cluster:1: Adding instance, 10.5.1.137, to the cluster.
2023-10-13 14:28:15 ERROR unit.mysql/2.juju-log server.go:316 cluster:1: Failed adding instance 10.5.1.137 to cluster: Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
^[[31mERROR: ^[[0mUnable to connect to the target instance '10.5.1.137:3306'. Please verify the connection settings, make sure the instance is available and try again.
Traceback (most recent call last):
  File "<string>", line 3, in <module>
mysqlsh.DBError: MySQL Error (1130): Cluster.add_instance: Could not open connection to '10.5.1.137:3306': Host '10.5.0.55' is not allowed to connect to this MySQL server

2023-10-13 14:28:15 INFO unit.mysql/2.juju-log server.go:316 cluster:1: Adding instance, 10.5.2.59, to the cluster.
2023-10-13 14:28:16 ERROR unit.mysql/2.juju-log server.go:316 cluster:1: Failed adding instance 10.5.2.59 to cluster: Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
^[[31mERROR: ^[[0mUnable to connect to the target instance '10.5.2.59:3306'. Please verify the connection settings, make sure the instance is available and try again.
Traceback (most recent call last):
  File "<string>", line 3, in <module>
mysqlsh.DBError: MySQL Error (1130): Cluster.add_instance: Could not open connection to '10.5.2.59:3306': Host '10.5.0.55' is not allowed to connect to this MySQL server

I reproduced this consistently multiple times with many small deployments. Every time the leader was the highest numbered unit (such as /2), it could not add instances properly, while any time the leader was /0 or /1 it worked successfully.

[Rodrigo Barbieri (rodrigo-barbieri2010)]

As soon as leadership is changed to any other unit, then all stuck units suddenly finish their configution successfully.