Incorrect GRANTS with mysql-router colocated with mysql-innodb-cluster

Hi!

I'm trying to deploy vault HA colocated with MySQl on Focal and having a problem with mysql router charm on 1 of the 3 units that can't connect to mysql server, because the host is incorrect:

$ juju status
Model Controller Cloud/Region Version SLA Timestamp
k8s-1 k8s-shared-lma-controller openstack_cloud/reg1 2.8.11 unsupported 16:56:26Z

App Version Status Scale Charm Store Rev OS Notes
hacluster-vault active 3 hacluster jujucharms 76 ubuntu
mysql 8.0.25 active 3 mysql-innodb-cluster jujucharms 8 ubuntu
vault 1.5.4 blocked 3 vault jujucharms 44 ubuntu
vault-mysql-router 8.0.25 waiting 3 mysql-router jujucharms 6 ubuntu

Unit Workload Agent Machine Public address Ports Message
mysql/0* active idle 0 10.254.9.205 Unit is ready: Mode: R/W, Cluster is ONLINE and can tolerate up to ONE failure.
mysql/1 active idle 1 10.254.9.183 Unit is ready: Mode: R/O, Cluster is ONLINE and can tolerate up to ONE failure.
mysql/2 active idle 2 10.254.9.229 Unit is ready: Mode: R/O, Cluster is ONLINE and can tolerate up to ONE failure.
vault/0* waiting idle 0 10.254.9.205 'shared-db' incomplete
  hacluster-vault/2 active idle 10.254.9.205 Unit is ready and clustered
  vault-mysql-router/2 waiting idle 10.254.9.205 MySQL Router not yet bootstrapped
vault/1 blocked idle 1 10.254.9.183 8200/tcp Vault needs to be initialized
  hacluster-vault/0* active idle 10.254.9.183 Unit is ready and clustered
  vault-mysql-router/0* active idle 10.254.9.183 Unit is ready
vault/2 blocked idle 2 10.254.9.229 8200/tcp Vault needs to be initialized
  hacluster-vault/1 active idle 10.254.9.229 Unit is ready and clustered
  vault-mysql-router/1 active idle 10.254.9.229 Unit is ready

Machine State DNS Inst id Series AZ Message
0 started 10.254.9.205 2a055bef-7e3b-472e-bf6a-37cdf8c1ab98 focal AZ1 ACTIVE
1 started 10.254.9.183 9165b15a-cf9e-4061-b0e9-a0cb8bf84328 focal AZ3 ACTIVE
2 started 10.254.9.229 186e474c-f7fa-4d2c-b645-ae0148f8c5ac focal AZ2 ACTIVE

Logging in to the mysql cluster and listing the users we see:

mysql> select * from user where user='vault';
+--------------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+--------------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| 10.254.9.183 | vault | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$)\9}

                                                                                                                              (^J0▒F5▒?VFL0qQN2THcjESmG.pHipL8V4vCAa7PKwEuDz9S879A | N | 2021-06-24 16:45:09 | NULL | N | N | N | NULL | NULL | NULL | NULL |
| 10.254.9.229 | vault | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$X+sZnHDb+KeMD▒4e.zWK0n3qY72RirrLoaLdRLZrCWSJzdttJsLXZXxgy64 | N | 2021-06-24 16:45:55 | NULL | N | N | N | NULL | NULL | NULL | NULL |
+--------------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from user where user='mysqlrouteruser';
+--------------+-----------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+--------------+-----------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| 10.254.9.183 | mysqlrouteruser | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | | NULL | NULL |p_)/|D | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$
SQesV5Ueg70RX6RNb/IUpQHUSYVXzp6rYde4.VpI0r0 | N | 2021-06-24 16:44:50 | NULL | N | N | N | NULL | NULL | NULL | NULL |
| 10.254.9.229 | mysqlrouteruser | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$ r6Dg%aV*;:[ai0bp/4W.nXu9n0tPXBO3WnW/IkFLRWkc5sl3uxozcU5 | N | 2021-06-24 16:45:30 | NULL | N | N | N | NULL | NULL | NULL | NULL |
| 127.0.0.1 | mysqlrouteruser | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | | NULL | NULL |WkK%4o?/▒eHd ZSvswq4V8QvZUZ9VYgiBl1fIc9kBHcvw7IumkjkVz/mIn9A | N | 2021-06-24 16:45:43 | NULL | N | N | N | NULL | NULL | NULL | NULL |
+--------------+-----------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
3 rows in set (0.01 sec)

mysql> SHOW GRANTS FOR 'mysqlrouteruser'@'127.0.0.1';
+---------------------------------------------------------------------------------------------------------------------+
| Grants for mysqlrouteruser@127.0.0.1 |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO `mysqlrouteruser`@`127.0.0.1` WITH GRANT OPTION |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `mysql_innodb_cluster_metadata`.* TO `mysqlrouteruser`@`127.0.0.1` |
| GRANT SELECT ON `mysql`.`user` TO `mysqlrouteruser`@`127.0.0.1` |
| GRANT SELECT ON `performance_schema`.`global_variables` TO `mysqlrouteruser`@`127.0.0.1` |
| GRANT SELECT ON `performance_schema`.`replication_group_member_stats` TO `mysqlrouteruser`@`127.0.0.1` |
| GRANT SELECT ON `performance_schema`.`replication_group_members` TO `mysqlrouteruser`@`127.0.0.1` |
+---------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

The problematic user seems to be the 'mysqlrouteruser'@'127.0.0.1'. In the logs of vault-mysql-router/2 we see:

$ tail unit-vault-mysql-router-2.log | grep ERR
2021-06-24 16:59:03 ERROR juju-log Failed to bootstrap mysqlrouter: Error: Unable to connect to the metadata server: Error connecting to MySQL server at 10.254.9.205:0: Access denied for user 'mysqlrouteruser'@'10.254.9.205' (using password: YES) (1045)

Other units of vault-mysql-router are fine.

It looks like mysql router created mysql user incorrectly to access the local mysql and that blocked the deployment. See also the similar issue for the percona-cluster charm: https://bugs.launchpad.net/vault-charm/+bug/1883056.

Perhaps https://bugs.launchpad.net/bugs/1861523 is also relevant.