Description:
As per documentation, the uniqueness of the channels will be verified only based on the server_id values. In order to fetch the same, the IO_THREAD need to connect to master host. This is the reason, while configuring replication for a channel using the CHANGE REPLICATION SOURCE command, we are not identifying the duplicate channels form same source.
Each channel on a multi-source replica must replicate from a different source. You cannot set up multiple replication channels from a single replica to a single source. This is because the server IDs of replicas must be unique in a replication topology. The source distinguishes replicas only by their server IDs, not by the names of the replication channels, so it cannot recognize different replication channels from the same replica.
https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html
Consider to validate the configuration while executing CHANGE REPLICATION SOURCE command rather than when starting the replication.
How to repeat:
mysql> SHOW REPLICA STATUS;
Empty set (0.00 sec)
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='172.31.21.51', SOURCE_USER='root', SOURCE_PASSWORD='temp12345', SOURCE_PORT=8000, SOURCE_AUTO_POSITION=0, SOURCE_DELAY=0 FOR CHANNEL 'test1';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='172.31.21.51', SOURCE_USER='root', SOURCE_PASSWORD='temp12345', SOURCE_PORT=8000, SOURCE_AUTO_POSITION=0, SOURCE_DELAY=0 FOR CHANNEL 'test2';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> select * from performance_schema.replication_connection_configuration;
+--------------+--------------+------+------+-------------------+---------------+-------------+-------------+-------------+-----------------+------------+---------+-------------------------------+--------------+--------------+---------------------------+------------------------+--------------------+-------------+-----------------+----------------+-------------------+-----------------------+------------------------+------------------+---------------------------------+-----------+
| CHANNEL_NAME | HOST | PORT | USER | NETWORK_INTERFACE | AUTO_POSITION | SSL_ALLOWED | SSL_CA_FILE | SSL_CA_PATH | SSL_CERTIFICATE | SSL_CIPHER | SSL_KEY | SSL_VERIFY_SERVER_CERTIFICATE | SSL_CRL_FILE | SSL_CRL_PATH | CONNECTION_RETRY_INTERVAL | CONNECTION_RETRY_COUNT | HEARTBEAT_INTERVAL | TLS_VERSION | PUBLIC_KEY_PATH | GET_PUBLIC_KEY | NETWORK_NAMESPACE | COMPRESSION_ALGORITHM | ZSTD_COMPRESSION_LEVEL | TLS_CIPHERSUITES | SOURCE_CONNECTION_AUTO_FAILOVER | GTID_ONLY |
+--------------+--------------+------+------+-------------------+---------------+-------------+-------------+-------------+-----------------+------------+---------+-------------------------------+--------------+--------------+---------------------------+------------------------+--------------------+-------------+-----------------+----------------+-------------------+-----------------------+------------------------+------------------+---------------------------------+-----------+
| test1 | 172.31.21.51 | 8000 | root | | 0 | NO | | | | | | NO | | | 60 | 86400 | 30.000 | | | NO | | uncompressed | 3 | NULL | 0 | 0 |
| test2 | 172.31.21.51 | 8000 | root | | 0 | NO | | | | | | NO | | | 60 | 86400 | 30.000 | | | NO | | uncompressed | 3 | NULL | 0 | 0 |
+--------------+--------------+------+------+-------------------+---------------+-------------+-------------+-------------+-----------------+------------+---------+-------------------------------+--------------+--------------+---------------------------+------------------------+--------------------+-------------+-----------------+----------------+-------------------+-----------------------+------------------------+------------------+---------------------------------+-----------+
2 rows in set (0.00 sec)
mysql> START REPLICA;
Query OK, 0 rows affected (0.00 sec)
mysql> show replica status\G
*************************** 1. row ***************************
Replica_IO_State:
Source_Host: 172.31.21.51
Source_User: root
Source_Port: 8000
Connect_Retry: 60
Source_Log_File: testbox-bin.000002
Read_Source_Log_Pos: 115775
Relay_Log_File: testbox-relay-bin-test1.000004
Relay_Log_Pos: 873
Relay_Source_Log_File: testbox-bin.000002
Replica_IO_Running: No
Replica_SQL_Running: Yes
.
.
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event '' at 4, the last event read from '/home/mysql80/mysql8029_gtid/data/testbox-bin.000002' at 115775, the last byte read from '/home/mysql80/mysql8029_gtid/data/testbox-bin.000002' at 115775.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 8000
Source_UUID: 5cd118e3-0e5f-11ee-85c2-02785e2dcd93
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
.
.
*************************** 2. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 172.31.21.51
Source_User: root
Source_Port: 8000
Connect_Retry: 60
Source_Log_File: testbox-bin.000002
Read_Source_Log_Pos: 66380754
Relay_Log_File: testbox-relay-bin-test2.000004
Relay_Log_Pos: 66380974
Relay_Source_Log_File: testbox-bin.000002
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
.
.
Replicate_Ignore_Server_Ids:
Source_Server_Id: 8000
Source_UUID: 5cd118e3-0e5f-11ee-85c2-02785e2dcd93
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 5cd118e3-0e5f-11ee-85c2-02785e2dcd93:1-189
Executed_Gtid_Set: 5cd118e3-0e5f-11ee-85c2-02785e2dcd93:1-190,
6ee86ef2-0e5f-11ee-88cc-02785e2dcd93:1-306,
7f570fe8-0e5f-11ee-8ac7-02785e2dcd93:1-6
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: test2
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
2 rows in set (0.01 sec)
Suggested fix:
As we are adding connection details to performance_schema tables, we can add compound unique index on host and port to enforce uniqueness. This helps to generate error at the time of configuration itself rather than at the time of starting replication.
