[MDEV-12012] gtid_domain_id doesn't work with multisource between 10.1 and 10.0 + GTID - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.28, 10.0.29, 10.1.21
Fix Version/s: 10.1.30, 10.2.11
Component/s: Replication
Labels:
Environment:
debian

Sprint:
10.2.11

Description

Hello,

I have the following scenario with a slave running multisource and no GTID and at some point I want to enable GTID on that existing slave. So I start setting the gtid_domain_id flags on both masters and it slaves as follows:

master 1:
gtid_domain_id=12
version: 10.0.28

master 2:
gtid_domain_id=14
version: 10.0.28

slave1 which replicates from both masters with multi source
gtid_domain_id=10
version: 10.1.21
gtid disabled

Current status after setting gtid_domain_id on the three hosts:

MariaDB [gtid_10]> show all slaves status\G

*************************** 1. row ***************************

              Connection_name: 12

              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.12

                  Master_User: slave_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mariadb-bin.000061

          Read_Master_Log_Pos: 2510

               Relay_Log_File: mysqld-relay-bin-12.000002

                Relay_Log_Pos: 538

        Relay_Master_Log_File: mariadb-bin.000061

             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: 2510

              Relay_Log_Space: 840

              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: 12

               Master_SSL_Crl:

           Master_SSL_Crlpath:

                   Using_Gtid: No

                  Gtid_IO_Pos: 0-2-210669,12-12-24,14-14-1

      Replicate_Do_Domain_Ids:

  Replicate_Ignore_Domain_Ids:

                Parallel_Mode: conservative

         Retried_transactions: 0

           Max_relay_log_size: 104857600

         Executed_log_entries: 29

    Slave_received_heartbeats: 0

       Slave_heartbeat_period: 1800.000

               Gtid_Slave_Pos: 0-10-210671,10-10-17,12-2-23,14-14-1

*************************** 2. row ***************************

              Connection_name: 14

              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.14

                  Master_User: slave_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysqld-bin.000063

          Read_Master_Log_Pos: 500

               Relay_Log_File: mysqld-relay-bin-14.000002

                Relay_Log_Pos: 537

        Relay_Master_Log_File: mysqld-bin.000063

             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: 500

              Relay_Log_Space: 839

              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: 14

               Master_SSL_Crl:

           Master_SSL_Crlpath:

                   Using_Gtid: No

                  Gtid_IO_Pos: 0-2-210669,12-12-24,14-14-1

      Replicate_Do_Domain_Ids:

  Replicate_Ignore_Domain_Ids:

                Parallel_Mode: conservative

         Retried_transactions: 0

           Max_relay_log_size: 104857600

         Executed_log_entries: 15

    Slave_received_heartbeats: 0

       Slave_heartbeat_period: 1800.000

               Gtid_Slave_Pos: 0-10-210671,10-10-17,12-2-23,14-14-1

2 rows in set (0.00 sec)

I keep inserting data and at some point I want to enable GTID, (slave_pos) and they crash replication:

MariaDB [gtid_10]>  stop all slaves;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

MariaDB [gtid_10]> change master '12' to master_use_gtid=slave_pos;

Query OK, 0 rows affected (0.01 sec)

MariaDB [gtid_10]> change master '14' to master_use_gtid=slave_pos;

Query OK, 0 rows affected (0.01 sec)

MariaDB [gtid_10]> start all slaves;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

MariaDB [gtid_10]> show all slaves status\G

*************************** 1. row ***************************

              Connection_name: 12

              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.12

                  Master_User: slave_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mariadb-bin.000061

          Read_Master_Log_Pos: 2631

               Relay_Log_File: mysqld-relay-bin-12.000002

                Relay_Log_Pos: 762

        Relay_Master_Log_File: mariadb-bin.000061

             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: 2631

              Relay_Log_Space: 1064

              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: 12

               Master_SSL_Crl:

           Master_SSL_Crlpath:

                   Using_Gtid: Slave_Pos

                  Gtid_IO_Pos: 0-2-210669,12-12-25,14-14-2

      Replicate_Do_Domain_Ids:

  Replicate_Ignore_Domain_Ids:

                Parallel_Mode: conservative

         Retried_transactions: 0

           Max_relay_log_size: 104857600

         Executed_log_entries: 40

    Slave_received_heartbeats: 0

       Slave_heartbeat_period: 1800.000

               Gtid_Slave_Pos: 0-10-210671,10-10-17,12-12-25,14-14-2

*************************** 2. row ***************************

              Connection_name: 14

              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

               Slave_IO_State:

                  Master_Host: 192.168.56.14

                  Master_User: slave_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysqld-bin.000063

          Read_Master_Log_Pos: 621

               Relay_Log_File: mysqld-relay-bin-14.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysqld-bin.000063

             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: 621

              Relay_Log_Space: 249

              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: 1236

                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 12-12-25, which is not in the master's binlog'

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 14

               Master_SSL_Crl:

           Master_SSL_Crlpath:

                   Using_Gtid: Slave_Pos

                  Gtid_IO_Pos: 0-2-210669,12-12-25,14-14-2

      Replicate_Do_Domain_Ids:

  Replicate_Ignore_Domain_Ids:

                Parallel_Mode: conservative

         Retried_transactions: 0

           Max_relay_log_size: 104857600

         Executed_log_entries: 18

    Slave_received_heartbeats: 0

       Slave_heartbeat_period: 1800.000

               Gtid_Slave_Pos: 0-10-210671,10-10-17,12-12-25,14-14-2

2 rows in set (0.00 sec)

This happens with both, current_pos and slave_pos.
If I go back to non GTID replication gets fixed and I can keep inserting data.

MariaDB [gtid_10]> stop all slaves;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

MariaDB [gtid_10]> change master '12' to master_use_gtid=no;

Query OK, 0 rows affected (0.00 sec)

MariaDB [gtid_10]> change master '14' to master_use_gtid=no;

Query OK, 0 rows affected (0.01 sec)

MariaDB [gtid_10]> start all slaves;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

MariaDB [gtid_10]> show all slaves status\G

*************************** 1. row ***************************

              Connection_name: 12

              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.12

                  Master_User: slave_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mariadb-bin.000061

          Read_Master_Log_Pos: 2631

               Relay_Log_File: mysqld-relay-bin-12.000002

                Relay_Log_Pos: 538

        Relay_Master_Log_File: mariadb-bin.000061

             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: 2631

              Relay_Log_Space: 840

              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: 12

               Master_SSL_Crl:

           Master_SSL_Crlpath:

                   Using_Gtid: No

                  Gtid_IO_Pos: 0-10-210671,12-2-23,14-14-2,10-10-17

      Replicate_Do_Domain_Ids:

  Replicate_Ignore_Domain_Ids:

                Parallel_Mode: conservative

         Retried_transactions: 0

           Max_relay_log_size: 104857600

         Executed_log_entries: 51

    Slave_received_heartbeats: 0

       Slave_heartbeat_period: 1800.000

               Gtid_Slave_Pos: 0-10-210671,10-10-17,12-2-23,14-14-2

*************************** 2. row ***************************

              Connection_name: 14

              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.14

                  Master_User: slave_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysqld-bin.000063

          Read_Master_Log_Pos: 621

               Relay_Log_File: mysqld-relay-bin-14.000002

                Relay_Log_Pos: 537

        Relay_Master_Log_File: mysqld-bin.000063

             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: 621

              Relay_Log_Space: 839

              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: 14

               Master_SSL_Crl:

           Master_SSL_Crlpath:

                   Using_Gtid: No

                  Gtid_IO_Pos: 0-10-210671,12-2-23,14-14-2,10-10-17

      Replicate_Do_Domain_Ids:

  Replicate_Ignore_Domain_Ids:

                Parallel_Mode: conservative

         Retried_transactions: 0

           Max_relay_log_size: 104857600

         Executed_log_entries: 29

    Slave_received_heartbeats: 0

       Slave_heartbeat_period: 1800.000

               Gtid_Slave_Pos: 0-10-210671,10-10-17,12-2-23,14-14-2

2 rows in set (0.00 sec)

And I insert data on the masters and they get replicated:

MariaDB [gtid_10]> show all slaves status\G

*************************** 1. row ***************************

              Connection_name: 12

              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.12

                  Master_User: slave_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mariadb-bin.000061

          Read_Master_Log_Pos: 2748

               Relay_Log_File: mysqld-relay-bin-12.000002

                Relay_Log_Pos: 655

        Relay_Master_Log_File: mariadb-bin.000061

             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: 2748

              Relay_Log_Space: 957

              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: 12

               Master_SSL_Crl:

           Master_SSL_Crlpath:

                   Using_Gtid: No

                  Gtid_IO_Pos: 0-10-210671,12-2-23,14-14-2,10-10-17

      Replicate_Do_Domain_Ids:

  Replicate_Ignore_Domain_Ids:

                Parallel_Mode: conservative

         Retried_transactions: 0

           Max_relay_log_size: 104857600

         Executed_log_entries: 53

    Slave_received_heartbeats: 0

       Slave_heartbeat_period: 1800.000

               Gtid_Slave_Pos: 0-10-210671,10-10-17,12-12-26,14-14-3

*************************** 2. row ***************************

              Connection_name: 14

              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.14

                  Master_User: slave_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysqld-bin.000063

          Read_Master_Log_Pos: 748

               Relay_Log_File: mysqld-relay-bin-14.000002

                Relay_Log_Pos: 664

        Relay_Master_Log_File: mysqld-bin.000063

             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: 748

              Relay_Log_Space: 966

              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: 14

               Master_SSL_Crl:

           Master_SSL_Crlpath:

                   Using_Gtid: No

                  Gtid_IO_Pos: 0-10-210671,12-2-23,14-14-2,10-10-17

      Replicate_Do_Domain_Ids:

  Replicate_Ignore_Domain_Ids:

                Parallel_Mode: conservative

         Retried_transactions: 0

           Max_relay_log_size: 104857600

         Executed_log_entries: 31

    Slave_received_heartbeats: 0

       Slave_heartbeat_period: 1800.000

               Gtid_Slave_Pos: 0-10-210671,10-10-17,12-12-26,14-14-3

Can this be related?: https://mariadb.atlassian.net/browse/MDEV-4485

Attachments

Issue Links

duplicates

MDEV-11969 Can't remove GTIDs for a stale GTID Domain ID

Closed

relates to

MDEV-9108 "GTID not in master's binlog" error with {ignore|do}_domain_ids

Open

Activity

Descending order - Click to sort in ascending order

Manuel Arostegui added a comment - 2020-03-23 16:09

Daniel, probably too late already, but leaving it here for posterity to avoid: https://xkcd.com/979/

FLUSH BINARY LOGS DELETE_DOMAIN_ID=(0);

Manuel Arostegui added a comment - 2020-03-23 16:09 Daniel, probably too late already, but leaving it here for posterity to avoid: https://xkcd.com/979/ FLUSH BINARY LOGS DELETE_DOMAIN_ID=(0);

Daniel Ragle added a comment - 2019-08-27 20:41

Thanks, still missing something. Now on the master:

MariaDB [(none)]> SELECT @@global.gtid_binlog_state;

+----------------------------------------------------------------------------------------------------------------+

| @@global.gtid_binlog_state                                                                                     |

+----------------------------------------------------------------------------------------------------------------+

| 0-302-67690294,0-301-67719794,0-303-67739600,1-303-1381518211,1-302-1300356654,1-301-1381751266,100-301-266595 |

+----------------------------------------------------------------------------------------------------------------+

1 row in set (0.000 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'version';

+---------------+---------------------+

| Variable_name | Value               |

+---------------+---------------------+

| version       | 10.3.14-MariaDB-log |

+---------------+---------------------+

1 row in set (0.001 sec)

MariaDB [(none)]> FLUSH LOCAL BINARY LOGS DELETE_DOMAIN_ID=0;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0' at line 1

If it makes any difference, the zero domain has been gone from all the servers in the chain for months, it shouldn't exist in any binlog, anywhere.

Daniel Ragle added a comment - 2019-08-27 20:41 Thanks, still missing something. Now on the master: MariaDB [(none)]> SELECT @@global.gtid_binlog_state; +----------------------------------------------------------------------------------------------------------------+ | @@global.gtid_binlog_state | +----------------------------------------------------------------------------------------------------------------+ | 0-302-67690294,0-301-67719794,0-303-67739600,1-303-1381518211,1-302-1300356654,1-301-1381751266,100-301-266595 | +----------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SHOW VARIABLES LIKE 'version'; +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | version | 10.3.14-MariaDB-log | +---------------+---------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> FLUSH LOCAL BINARY LOGS DELETE_DOMAIN_ID=0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0' at line 1 If it makes any difference, the zero domain has been gone from all the servers in the chain for months, it shouldn't exist in any binlog, anywhere.

Andrei Elkin added a comment - 2019-08-23 19:23

knielsen, thank you for helping out! (though you beat alice to that by a few hours )

Andrei Elkin added a comment - 2019-08-23 19:23 knielsen , thank you for helping out! (though you beat alice to that by a few hours )

Kristian Nielsen added a comment - 2019-08-23 19:09

> Daniel Ragle added a comment - 2 days ago
> Hmmmm... what did I do wrong?

What went wrong here (IIUC) is that multi-source replication was set up
without correctly configuring the gtid_domain_id on the masters (they both
had domain 0). There is therefore no single GTID that can represent the
slave's current position in domain 0 - a different position is needed on
each master.

Setting the slave's position in domain 0 to empty just means that it should
start from the very first GTID in that domain - and that GTID (0-303-1
presumably) is long gone, of course.

One option to fix is to remove the record of domain on on the masters -
using the FLUSH BINARY LOGS DELETE_DOMAIN_ID=0 command. You can see the
effect of this command on the master in the @@gtid_binlog_state variable
which keeps a record of all domains that were ever used on the master.

Another option is to artificially make it look like one GTID is the latest
in domain 0 on both masters (say 0-303-67739600) and then inject a dummy
transaction with this GTID on the master that's missing it. This tells the
slave that it can start at this GTID on both masters without missing any
events.

(I am assuming here that the problem Daniel Ragle has is the same as the
original reporter).

Kristian Nielsen added a comment - 2019-08-23 19:09 > Daniel Ragle added a comment - 2 days ago > Hmmmm... what did I do wrong? What went wrong here (IIUC) is that multi-source replication was set up without correctly configuring the gtid_domain_id on the masters (they both had domain 0). There is therefore no single GTID that can represent the slave's current position in domain 0 - a different position is needed on each master. Setting the slave's position in domain 0 to empty just means that it should start from the very first GTID in that domain - and that GTID (0-303-1 presumably) is long gone, of course. One option to fix is to remove the record of domain on on the masters - using the FLUSH BINARY LOGS DELETE_DOMAIN_ID=0 command. You can see the effect of this command on the master in the @@gtid_binlog_state variable which keeps a record of all domains that were ever used on the master. Another option is to artificially make it look like one GTID is the latest in domain 0 on both masters (say 0-303-67739600) and then inject a dummy transaction with this GTID on the master that's missing it. This tells the slave that it can start at this GTID on both masters without missing any events. (I am assuming here that the problem Daniel Ragle has is the same as the original reporter).

Daniel Ragle added a comment - 2019-08-21 20:46

Hmmmm... what did I do wrong? I've verified that the master has the position indicated (that master keeps five days of binary logs, and this position is within the last 3 hours):

MariaDB [(none)]> STOP SLAVE;

Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> SHOW WARNINGS;

+-------+------+--------------------------------+

| Level | Code | Message                        |

+-------+------+--------------------------------+

| Note  | 1255 | Slave already has been stopped |

+-------+------+--------------------------------+

1 row in set (0.000 sec)

MariaDB [(none)]> SELECT @@global.gtid_slave_pos;

+------------------------------------------------+

| @@global.gtid_slave_pos                        |

+------------------------------------------------+

| 0-303-67739600,1-301-1314447552,100-301-176774 |

+------------------------------------------------+

1 row in set (0.000 sec)

MariaDB [(none)]> SET @@global.gtid_slave_pos = '1-301-1314447552,100-301-176774';

Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> SELECT @@global.gtid_slave_pos;

+---------------------------------+

| @@global.gtid_slave_pos         |

+---------------------------------+

| 1-301-1314447552,100-301-176774 |

+---------------------------------+

1 row in set (0.000 sec)

MariaDB [(none)]> START SLAVE;

Query OK, 0 rows affected (0.646 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

                Slave_IO_State:

                   Master_Host: 192.168.100.23

                   Master_User: repl

                   Master_Port: 3306

                 Connect_Retry: 60

               Master_Log_File: binary-log.011058

           Read_Master_Log_Pos: 956169617

                Relay_Log_File: mysql-relay-log.000001

                 Relay_Log_Pos: 4

         Relay_Master_Log_File: binary-log.011058

              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: 956169617

               Relay_Log_Space: 256

               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: 1236

                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.'

                Last_SQL_Errno: 0

                Last_SQL_Error:

   Replicate_Ignore_Server_Ids:

              Master_Server_Id: 303

                Master_SSL_Crl:

            Master_SSL_Crlpath:

                    Using_Gtid: Slave_Pos

                   Gtid_IO_Pos: 1-301-1314447552,100-301-176774

       Replicate_Do_Domain_Ids:

   Replicate_Ignore_Domain_Ids:

                 Parallel_Mode: conservative

                     SQL_Delay: 3600

           SQL_Remaining_Delay: NULL

       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

              Slave_DDL_Groups: 8003

Slave_Non_Transactional_Groups: 131

    Slave_Transactional_Groups: 3329917

1 row in set (0.000 sec)

MariaDB [(none)]> SELECT @@global.gtid_slave_pos;

+---------------------------------+

| @@global.gtid_slave_pos         |

+---------------------------------+

| 1-301-1314447552,100-301-176774 |

+---------------------------------+

1 row in set (0.000 sec)

MariaDB [(none)]> STOP SLAVE;

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT @@global.gtid_slave_pos;

+---------------------------------+

| @@global.gtid_slave_pos         |

+---------------------------------+

| 1-301-1314447552,100-301-176774 |

+---------------------------------+

1 row in set (0.000 sec)

Daniel Ragle added a comment - 2019-08-21 20:46 Hmmmm... what did I do wrong? I've verified that the master has the position indicated (that master keeps five days of binary logs, and this position is within the last 3 hours): MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [(none)]> SHOW WARNINGS; +-------+------+--------------------------------+ | Level | Code | Message | +-------+------+--------------------------------+ | Note | 1255 | Slave already has been stopped | +-------+------+--------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT @@global.gtid_slave_pos; +------------------------------------------------+ | @@global.gtid_slave_pos | +------------------------------------------------+ | 0-303-67739600,1-301-1314447552,100-301-176774 | +------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SET @@global.gtid_slave_pos = '1-301-1314447552,100-301-176774'; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> SELECT @@global.gtid_slave_pos; +---------------------------------+ | @@global.gtid_slave_pos | +---------------------------------+ | 1-301-1314447552,100-301-176774 | +---------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.646 sec) MariaDB [(none)]> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.100.23 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binary-log.011058 Read_Master_Log_Pos: 956169617 Relay_Log_File: mysql-relay-log.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: binary-log.011058 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: 956169617 Relay_Log_Space: 256 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: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 303 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 1-301-1314447552,100-301-176774 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 3600 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 8003 Slave_Non_Transactional_Groups: 131 Slave_Transactional_Groups: 3329917 1 row in set (0.000 sec) MariaDB [(none)]> SELECT @@global.gtid_slave_pos; +---------------------------------+ | @@global.gtid_slave_pos | +---------------------------------+ | 1-301-1314447552,100-301-176774 | +---------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SELECT @@global.gtid_slave_pos; +---------------------------------+ | @@global.gtid_slave_pos | +---------------------------------+ | 1-301-1314447552,100-301-176774 | +---------------------------------+ 1 row in set (0.000 sec)

View 35 older comments

MariaDB Server

gtid_domain_id doesn't work with multisource between 10.1 and 10.0 + GTID

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration