Bug #95531 mysql router fails to connect to cluster
Submitted: 25 May 2019 9:51 Modified: 9 Jul 2019 19:34
Reporter: Crt Zerjal Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Router Severity:S1 (Critical)
Version:8.0.16 OS:Debian
Assigned to: Andrzej Religa CPU Architecture:x86

[25 May 2019 9:51] Crt Zerjal
Description:
when trying to connect to a innodb cluster,
it exits with
Configuration error: cluster-metadata-servers is incorrect

I found out that inside state.json the hosts are entered incorrectly,
when the router starts it refreshes the file with square brackets [] around the domain name of members, if I remove those brackets by hand it starts normally but it updates the file state.json again and if i restart the router it fails

these are my settings and logs:

2019-05-25 11:29:06 metadata_cache DEBUG [7f73e878f700] Connected with metadata server running on fox.43.com:33061
2019-05-25 11:29:06 metadata_cache DEBUG [7f73e878f700] Updating metadata information for cluster 'farm'
2019-05-25 11:29:06 sql DEBUG [7f73e878f700] Executing query: SELECT R.replicaset_name, I.mysql_server_uuid, I.role, I.weight, I.version_token, H.location, I.addresses->>'$.mysqlClassic', I.addresses->>'$.mysqlX' FROM mysql_innodb_cluster_metadata.clusters AS F JOIN mysql_innodb_cluster_metadata.replicasets AS R ON F.cluster_id = R.cluster_id JOIN mysql_innodb_cluster_metadata.instances AS I ON R.replicaset_id = I.replicaset_id JOIN mysql_innodb_cluster_metadata.hosts AS H ON I.host_id = H.host_id WHERE F.
2019-05-25 11:29:06 sql DEBUG [7f73e878f700] Done executing query
2019-05-25 11:29:06 metadata_cache DEBUG [7f73e878f700] Updating replicaset status from GR for 'default'
2019-05-25 11:29:06 metadata_cache DEBUG [7f73e878f700] Connected to replicaset 'default' through fox.43.com:33061
2019-05-25 11:29:06 sql DEBUG [7f73e878f700] Executing query: show status like 'group_replication_primary_member'
2019-05-25 11:29:06 sql DEBUG [7f73e878f700] Done executing query
2019-05-25 11:29:06 sql DEBUG [7f73e878f700] Executing query: SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier'
2019-05-25 11:29:06 sql DEBUG [7f73e878f700] Done executing query
2019-05-25 11:29:06 metadata_cache DEBUG [7f73e878f700] Replicaset 'default' has 3 members in metadata, 3 in status table
2019-05-25 11:29:06 metadata_cache DEBUG [7f73e878f700] End updating replicaset for 'default' 
2019-05-25 11:29:07 main ERROR [7f9b53942780] Configuration error: cluster-metadata-servers is incorrect (invalid URI: expected to find IPv6 address, but failed at position 9 for: mysql://[fox.43.com]:33061)

SELECT R.replicaset_name, I.mysql_server_uuid, I.role, I.weight, I.version_token, H.location, I.addresses->>'$.mysqlClassic', I.addresses->>'$.mysqlX' FROM mysql_innodb_cluster_metadata.clusters AS F JOIN mysql_innodb_cluster_metadata.replicasets AS R ON F.cluster_id = R.cluster_id JOIN mysql_innodb_cluster_metadata.instances AS I ON R.replicaset_id = I.replicaset_id JOIN mysql_innodb_cluster_metadata.hosts AS H ON I.host_id = H.host_id WHERE F.cluster_name = "farm";

+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
| replicaset_name | mysql_server_uuid                    | role | weight | version_token | location | I.addresses->>'$.mysqlClassic' | I.addresses->>'$.mysqlX' |
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
| default         | 8c4c2414-74c7-11e9-a578-901b0ed5618c | HA   |   NULL |          NULL |          | fox.43.com:33061                | fox.43.com:33060          |
| default         | d764ca05-20db-11e9-8caf-901b0ecd05b9 | HA   |   NULL |          NULL | hetzner  | bat.43.com:33061                | bat.43.com:33060          |
| default         | fb914277-74ac-11e9-974f-96000019408b | HA   |   NULL |          NULL |          | rat.43.com:33061                | rat.43.com:33060          |
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
3 rows in set (0.0014 sec)

SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier';

+--------------------------------------+-------------+-------------+--------------+-----------------------------------------+
| member_id                            | member_host | member_port | member_state | @@group_replication_single_primary_mode |
+--------------------------------------+-------------+-------------+--------------+-----------------------------------------+
| 8c4c2414-74c7-11e9-a578-901b0ed5618c | fox.43.com   |       33061 | ONLINE       |                                       1 |
| d764ca05-20db-11e9-8caf-901b0ecd05b9 | bat.43.com   |       33061 | ONLINE       |                                       1 |
| fb914277-74ac-11e9-974f-96000019408b | rat.43.com   |       33061 | ONLINE       |                                       1 |
+--------------------------------------+-------------+-------------+--------------+-----------------------------------------+
3 rows in set (0.0012 sec)

#cat /etc/mysqlrouter/data/state.json
{
    "metadata-cache": {
        "group-replication-id": "2ba959bc-42cf-4e0c-97c9-2c5112f791a7",
        "cluster-metadata-servers": [
            "mysql://[fox.43.com]:33061",
            "mysql://[bat.43.com]:33061",
            "mysql://rat.43.com:33061"
        ]
    },
    "version": "1.0.0"
}

cluster.status();
{
    "clusterName": "farm",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "bat.43.com:33061",
        "ssl": "DISABLED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "bat.43.com:33061": {
                "address": "bat.43.com:33061",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.16"
            },
            "fox.43.com:33061": {
                "address": "fox.43.com:33061",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.16"
            },
            "rat.43.com:33061": {
                "address": "rat.43.com:33061",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.16"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "bat.43.com:33061"
}

How to repeat:
restart mysql router
[28 May 2019 6:08] MySQL Verification Team
Hi,

Can you give me your router config file?

thanks
Bogdan
[28 May 2019 12:56] Crt Zerjal
mysqlrouter.conf

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
user=mysqlrouter
logging_folder=/var/log/mysqlrouter/
runtime_folder=/var/run/mysqlrouter/
data_folder=/etc/mysqlrouter/data
keyring_path=/etc/mysqlrouter/data/keyring
master_key_path=/etc/mysqlrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/etc/mysqlrouter/data/state.json

[logger]
level = DEBUG

[metadata_cache:farm]
router_id=1
user=mysql_router1_8je03205g8a4
metadata_cluster=farm
ttl=0.5

[routing:farm_default_rw]
bind_address=0.0.0.0
bind_port=6446
socket=/var/run/mysqlrouter/mysql.sock
destinations=metadata-cache://farm/default?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:farm_default_ro]
bind_address=0.0.0.0
bind_port=6447
socket=/var/run/mysqlrouter/mysqlro.sock
destinations=metadata-cache://farm/default?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:farm_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
socket=/var/run/mysqlrouter/mysqlx.sock
destinations=metadata-cache://farm/default?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:farm_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
socket=/var/run/mysqlrouter/mysqlxro.sock
destinations=metadata-cache://farm/default?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x
[5 Jun 2019 21:21] MySQL Verification Team
How did you generate this config? What parameters did you give to mysqlrouter --bootstrap ?

I'm having issue reproducing this behavior

thanks
[6 Jun 2019 7:08] Crt Zerjal
hi,
this is the comand that generated those configs
mysqlrouter --bootstrap localhost:33061 --directory /etc/mysqlrouter --conf-use-sockets --user=mysqlrouter
i just changed the configuration of the paths for sockets

is it possible to change the cluster config to store ips instead of hostnames?
I suspect that mysqrouter thinks that my hostnames are ipv6 addresses

thanks
[6 Jun 2019 10:07] MySQL Verification Team
Hi,

Let me check about ip addresses.

As for the ipv6, maybe that's why I could not reproduce, I have disabled ipv6 support on my test gear. Lemme retest and get back
[6 Jun 2019 11:28] MySQL Verification Team
Hi,

Bug is verified, I discussed with a dev team and they'll try to push a fix in .17

thanks
Bogdan
[5 Jul 2019 14:54] lionel mazeyrat
is there a workaround for 8.0.16 to use IPV4 adresses instead of host name ?
[6 Jul 2019 8:25] Crt Zerjal
if you don't use ipv6 disabling it will make the router work
[9 Jul 2019 19:34] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Router 8.0.17 release, and here's the changelog entry:

Bootstrapping could misclassify a hostname as IPv6 and surround it with
square brackets in the state (state.json) file; and this produced a
"Configuration error: cluster-metadata-servers is incorrect" error. A
workaround was to disable ipv6 support on the system.

Thank you for the bug report and detailed information!
[11 Jul 2019 14:27] lionel mazeyrat
windows server 2016 - Mysql 8.0.16
I've tried the workaround, IPV6 desactivated, but when I stop/restart mysql router, the service don't restart and I need to suppress the backet in state.json.

Is it usefull to re-install or re-configure mysqlrouter service ?

{
    "metadata-cache": {
        "group-replication-id": "7321120a-9cd1-11e9-9bef-000c29fa29be",
        "cluster-metadata-servers": [
            "mysql://TVG-GTCHIS:3306",
            "mysql://[TVG-GTC01]:3306",
            "mysql://TVG-GTC02:3306"
        ]
    },
    "version": "1.0.0"
}
[11 Jul 2019 15:09] MySQL Verification Team
Hi,

since the fix is already in .17 please try it as soon as it's out.

Thanks
Bogdan

p.s. according to your test of a workaround looks like windows did not completely disabled ipv6