failed bootstrap using manual cloud

[coccolesto]

mysql router bootstrap doesn't work using a manual cloud of VMs.

Steps to reproduce

1. set up a juju VM manual cloud
2. follow https://discourse.charmhub.io/t/mysql-router-tutorial-deploy-mysql-router/12334

Expected behavior

Access the database using mysql as in https://discourse.charmhub.io/t/mysql-router-tutorial-deploy-mysql-router/12334

Actual behavior

can't connect to mysql server(s). According to logs, mysql router can't bootstrap because cannot resolve mysql servers name

Versions

Operating system: Ubuntu 22.04.4 LTS

Juju CLI: 3.1.7-genericlinux-amd64

Juju agent: 3.1.7

Charm revision: mysql-router dpe/edge 138

LXD: N.A.

Log output

Juju debug log:

Additional context

[github-actions]

https://warthogs.atlassian.net/browse/DPE-3692

[taurus-forever]

@coccolesto can you please describe in details your 1. set up a juju VM manual cloud.
This is a key to prepare a proper fix here.

For the history, the story started here: https://discourse.charmhub.io/t/12334/5

[coccolesto]

Hi,
looking into /var/snap/charmed-mysql/current/var/lib/mysqlrouter/state.json
{
"metadata-cache": {
"clusterset-id": "eadb27a8-1cfd-11ef-bb44-fa163ef217bb",
"cluster-metadata-servers": [
"mysql://mysql-3:3306",
"mysql://mysql-2:3306",
"mysql://mysql-1:3306"
],
"view-id": 1
},
"version": "1.1.0"

the metadata-cache stores the mysql server name passed by juju.
I think that if we can use ip instead of names the problem is simply resolved

[paulomach]

@coccolesto the issue is clear.

This is currently a limitation in juju not being able to provide a recursive DNS solution.
In MySQL we choose to use hostnames instead of IPs for cross node communications due MySQL being sensitive to node IP changes.
Doing so, when a router instance is bootstrapped, the state.json file will track node addresses as reported by the cluster metadata, using hostnames not resolvable cross clouds/substrates.

We do have a POC that workaround the issue (PR #441) that can point out a manual solution.
Though current position is to push juju to provide us with a cleaner solution.

tl;dr; no support for this right now, we need support from juju.