MySQL incorrectly saying serverID non unique when adding an instance to the InnoDB cluster

Question

I Get the following error when adding the second instance to a MYSQL Innodb Cluster

ERROR: Cannot add instance 'XXX' to the cluster because it has the same server ID of a member of the cluster. Please change the server ID of the instance to add: all members must have a unique server ID.
Cluster.addInstance: Access denied for user 'XXX'@'XXX' (using password: YES) (RuntimeError)

However if I check the two serverid's I can confirm that they are different

Server 1

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
1 row in set (0.00 sec)

Server 2

show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

Please advise?

Answer 1

Did you restart the server after changing the server-id? Despite server-id being dynamic, there is the following caveat on the manual:

If the server ID is set to 0, binary logging takes place, but a master with a server ID of 0 refuses any connections from slaves, and a slave with a server ID of 0 refuses to connect to a master. Note that although you can change the server ID dynamically to a nonzero value, doing so does not enable replication to start immediately. You must change the server ID and then restart the server to initialize the replication slave.

I am giving a wild guess that may be your issue, because it is a very common problem that people suffer once when setting up replication.

Edit: Re "Interestingly mysql is ignoring the server_id in the my.cnf file and choosing its own."

Then you have a configuration problem in which your server id is not properly read, please use --print-defaults to debug which files and options are being applied live, and check the paths for correct permissions, file existence and avoid duplicate config definitions/unintended includes.

Answer 2

Are you using different accounts to administer the cluster? i.e. did you use an account to create the cluster and are you using a different one to add the instance to it? Or is it the same account with a different password?

If that's the case, then that's the reason you're hitting this issue. The account used to administer the cluster must have the same credentials on all cluster members.

The recommendation is to create a clusterAdmin account, using dba.configureInstance(). More info in the docs.

The current error message is misleading and confusing. This will be improved soon and the documentation will also be improved to outline this requirement.

Cheers, Miguel