Bug #102994 Semi-synchronous replication is used with multi-source replication

Submitted: 16 Mar 2021 7:22 Modified: 25 Mar 2021 8:33
Reporter: yue li Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.10, 5.7.24, 8.0.16 OS:CentOS
Assigned to: CPU Architecture:x86

[16 Mar 2021 7:22] yue li
Description:
Multi-Source Replication close a channel causes the io thread of another replication channel to close.

I set up a multi-source replication: A->C<-B ,using gtid and semi-replication.

Here c has two replication channels:

Channel_Name: c3444 (master is A)
Channel_Name: c3445 (master is B)

When I try to close channel 'c3444',error log output on B:

2021-03-16T07:07:27.835149Z 15 [ERROR] [MY-013115] [Repl] Slave I/O for channel 'c3445': Replication event checksum verification failed while reading from network. Error_code: MY-013115
2021-03-16T07:07:27.835508Z 15 [ERROR] [MY-013122] [Repl] Slave I/O for channel 'c3445': Relay log write failure: could not queue event from master, Error_code: MY-013122

How to repeat:
Here are three instances:A、B and C.
GTID and semi-synchronous replication are enabled on each instance.

A:
mysql> show variables  like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 3000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+

B:
mysql> show variables like  '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 3000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+

C:
mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 3000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+

The final database architecture is A->C<-B

View the master-slave status on the C instance:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.221.222.186
                  Master_User: repl
                  Master_Port: 3444
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 873
               Relay_Log_File: relay-log-c3444.000014
                Relay_Log_Pos: 456
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 873
              Relay_Log_Space: 965
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102213444
                  Master_UUID: b1fc0fef-8600-11eb-b902-fa37ddbb3200
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b1fc0fef-8600-11eb-b902-fa37ddbb3200:2-3
            Executed_Gtid_Set: 7225ff3b-85fd-11eb-bcbc-fa37ddbb3200:1-3,
b1fc0fef-8600-11eb-b902-fa37ddbb3200:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: c3444
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.221.222.186
                  Master_User: repl
                  Master_Port: 3445
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 873
               Relay_Log_File: relay-log-c3445.000014
                Relay_Log_Pos: 456
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 873
              Relay_Log_Space: 965
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102213445
                  Master_UUID: 7225ff3b-85fd-11eb-bcbc-fa37ddbb3200
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 7225ff3b-85fd-11eb-bcbc-fa37ddbb3200:2-3
            Executed_Gtid_Set: 7225ff3b-85fd-11eb-bcbc-fa37ddbb3200:1-3,
b1fc0fef-8600-11eb-b902-fa37ddbb3200:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: c3445
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
2 rows in set (0.00 sec)

Only the c3444 channel is stopped at this time:
mysql> stop slave for channel 'c3444';
Query OK, 0 rows affected (0.03 sec)

Check the master-slave status again:
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.221.222.186
                  Master_User: repl
                  Master_Port: 3444
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 873
               Relay_Log_File: relay-log-c3444.000014
                Relay_Log_Pos: 456
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 873
              Relay_Log_Space: 965
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102213444
                  Master_UUID: b1fc0fef-8600-11eb-b902-fa37ddbb3200
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b1fc0fef-8600-11eb-b902-fa37ddbb3200:2-3
            Executed_Gtid_Set: 7225ff3b-85fd-11eb-bcbc-fa37ddbb3200:1-3,
b1fc0fef-8600-11eb-b902-fa37ddbb3200:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: c3444
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
*************************** 2. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.221.222.186
                  Master_User: repl
                  Master_Port: 3445
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 873
               Relay_Log_File: relay-log-c3445.000014
                Relay_Log_Pos: 456
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 873
              Relay_Log_Space: 965
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 13122
                Last_IO_Error: Relay log write failure: could not queue event from master
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102213445
                  Master_UUID: 7225ff3b-85fd-11eb-bcbc-fa37ddbb3200
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 210316 15:17:03
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 7225ff3b-85fd-11eb-bcbc-fa37ddbb3200:2-3
            Executed_Gtid_Set: 7225ff3b-85fd-11eb-bcbc-fa37ddbb3200:1-3,
b1fc0fef-8600-11eb-b902-fa37ddbb3200:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: c3445
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
2 rows in set (0.00 sec)

View the error log:
2021-03-16T07:17:03.760665Z 27 [ERROR] [MY-013115] [Repl] Slave I/O for channel 'c3445': Replication event checksum verification failed while reading from network. Error_code: MY-013115
2021-03-16T07:17:03.760926Z 27 [ERROR] [MY-013122] [Repl] Slave I/O for channel 'c3445': Relay log write failure: could not queue event from master, Error_code: MY-013122

At this point, you need to execute: start slave; to make other channel normal as well

This will not be a problem if the semi-synchronous replication of the multi-source replication slave is closed.
[25 Mar 2021 8:33] MySQL Verification Team
Hi,

Thanks for the report and the details. Verified as described.
[1 Apr 2022 16:13] Trey Raymond
this may be related to this older bug
https://bugs.mysql.com/bug.php?id=80122
[13 Jun 2022 13:11] Sven Sandberg
Posted by developer:
 
Semi-synchronous replication is unsupported when there are multiple replication channels. This is documented at https://dev.mysql.com/doc/refman/5.7/en/replication-semisync-installation.html , where the third bullet says "There must not be multiple replication channels configured. Semisynchronous replication is only compatible with the default replication channel. See Section 16.2.2, “Replication Channels”."

So I will change the status to "feature request".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.