Bug #105223 mysql-router bootstrap reuses wrong router_id from mysql_innodb_cluster_metadata

• Submitted: 14 Oct 2021 8:59
• Modified: 14 Oct 2021 13:43
• Reporter: Florian Apolloner
• Status: Verified
• Category: MySQL Router
• Severity: S2 (Serious)
• Version: 8.0.26
• OS: Any
• CPU Architecture: Any

[14 Oct 2021 8:59] Florian Apolloner

Description:
When mysqlrouter bootstrap it tries to insert an entry into mysql_innodb_cluster_metadata.routers (metadata version v2) and if that fails (due to constraint violations) it tries to select the existing router.

The code in question can be found here:
https://github.com/mysql/mysql-server/blob/beb865a960b9a8a16cf999c323e46c5b0c67f21f/router...

When an existing router is found in the database, mysqlrouter fetches the router_id via
https://github.com/mysql/mysql-server/blob/beb865a960b9a8a16cf999c323e46c5b0c67f21f/router...

This is wrong as the unique constraint is defined as:
  UNIQUE KEY `address` (`address`,`router_name`)

so when the router tries to fetch the existing router it should also query by it's address since it doesn't seem that the router_name alone is supposed to be unique! The correct query would probably be:

      query = sqlstring(
          "SELECT router_id FROM mysql_innodb_cluster_metadata.v2_routers"
          " WHERE address = ? AND router_name = ?");
      query << hostname << router_name << sqlstring::end;

This becomes rather clear because the default name is an empty string, so essentially one could only bootstrap a single router.

How to repeat:

Bootstrap a router with --report-host=1234. Bootstrap another router and you will see that it reuses router_id 1

[14 Oct 2021 9:03] Florian Apolloner

Sorry the reproduction steps are not correct, it is more like this:

 * Bootstrap a router with --report-host=test --name=test and verify that it has router_id=1
 * Bootstrap a router with --repost-host=test2 --name=test and verify it has router_id=2
 * Bootstrap a router with --report-host=test --name=test and chances are that it will end up with router_id=2 and show a warning that the hostnames do not match

[14 Oct 2021 13:43] MySQL Verification Team

Hi Florian, thanks for the report