[MDEV-20122] Deprecate MASTER_USE_GTID=Current_Pos to favor new MASTER_DEMOTE_TO_SLAVE option - Jira

Geoff Montee (Inactive) created issue - 2019-07-22 22:45

Geoff Montee (Inactive) made changes - 2019-07-22 22:56

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-16834~~ [ ~~MDEV-16834~~ ]

Geoff Montee (Inactive) made changes - 2019-07-23 00:28

Link

This issue relates to ~~MDEV-17156~~ [ ~~MDEV-17156~~ ]

Geoff Montee (Inactive) made changes - 2019-07-23 00:28

Link

This issue relates to MDEV-10279 [ MDEV-10279 ]

Julien Fritsch made changes - 2019-10-28 10:39

Assignee

Andrei Elkin [ elkin ]

Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]

Julien Fritsch made changes - 2019-10-28 10:39

Assignee

Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]

Andrei Elkin [ elkin ]

Julien Fritsch made changes - 2020-05-18 07:27

Priority

Major [ 3 ]

Critical [ 2 ]

Sachin Setiya (Inactive) made changes - 2020-08-20 13:33

Assignee

Andrei Elkin [ elkin ]

Sachin Setiya [ sachin.setiya.007 ]

Sujatha Sivakumar (Inactive) made changes - 2020-10-15 04:22

Assignee

Sachin Setiya [ sachin.setiya.007 ]

Sujatha Sivakumar [ sujatha.sivakumar ]

Sujatha Sivakumar (Inactive) made changes - 2020-10-16 07:35

Status

Open [ 1 ]

In Progress [ 3 ]

Sujatha Sivakumar (Inactive) made changes - 2020-10-20 07:33

Assignee

Sujatha Sivakumar [ sujatha.sivakumar ]

Geoff Montee [ geoffmontee ]

Julien Fritsch made changes - 2020-10-20 07:50

Labels

need_feedback

Geoff Montee (Inactive) made changes - 2020-10-20 20:21

Labels

need_feedback

Sujatha Sivakumar (Inactive) made changes - 2020-10-21 06:38

Assignee

Geoff Montee [ geoffmontee ]

Sujatha Sivakumar [ sujatha.sivakumar ]

Julien Fritsch made changes - 2020-10-22 13:54

Priority

Critical [ 2 ]

Major [ 3 ]

Julien Fritsch made changes - 2020-11-06 15:58

Fix Version/s

10.1 [ 16100 ]

Julien Fritsch made changes - 2020-11-30 15:57

Priority

Major [ 3 ]

Critical [ 2 ]

Andrei Elkin made changes - 2020-12-07 13:32

Labels

gtid_current_pos

Julien Fritsch made changes - 2020-12-21 17:21

Labels

gtid_current_pos

gtid_current_pos need_feedback

Julien Fritsch made changes - 2021-03-05 19:08

Labels

gtid_current_pos need_feedback

gtid_current_pos

Andrei Elkin made changes - 2021-03-19 10:37

Assignee

Sujatha Sivakumar [ sujatha.sivakumar ]

Andrei Elkin [ elkin ]

Andrei Elkin made changes - 2021-05-27 13:02

Assignee

Andrei Elkin [ elkin ]

Geoff Montee [ geoffmontee ]

Andrei Elkin made changes - 2021-05-27 13:03

Labels

gtid_current_pos

gtid_current_pos need_feedback

Geoff Montee (Inactive) made changes - 2021-06-03 00:00

Assignee

Geoff Montee [ geoffmontee ]

Andrei Elkin [ elkin ]

Geoff Montee (Inactive) made changes - 2021-06-03 00:00

Labels

gtid_current_pos need_feedback

gtid_current_pos

Andrei Elkin made changes - 2021-06-10 17:55

Assignee

Andrei Elkin [ elkin ]

Geoff Montee [ geoffmontee ]

Julien Fritsch made changes - 2021-06-15 09:35

Assignee

Geoff Montee [ geoffmontee ]

Andrei Elkin [ elkin ]

Andrei Elkin made changes - 2021-06-15 17:02

Fix Version/s		10.6 [ 24028 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]

Sujatha Sivakumar (Inactive) made changes - 2021-08-11 09:56

Assignee

Andrei Elkin [ elkin ]

Sujatha Sivakumar [ sujatha.sivakumar ]

Sujatha Sivakumar (Inactive) made changes - 2021-09-15 12:04

Assignee	Sujatha Sivakumar [ sujatha.sivakumar ]	Andrei Elkin [ elkin ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Andrei Elkin made changes - 2021-09-27 11:28

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Andrei Elkin made changes - 2021-09-27 12:08

Fix Version/s		10.8 [ 26121 ]
Fix Version/s	10.6 [ 24028 ]

Sergei Golubchik made changes - 2021-10-29 11:55

Priority

Critical [ 2 ]

Major [ 3 ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 98419 ]

MariaDB v4 [ 143600 ]

Julien Fritsch made changes - 2021-12-14 13:26

Priority

Major [ 3 ]

Critical [ 2 ]

Andrei Elkin made changes - 2022-02-16 17:05

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Andrei Elkin made changes - 2022-04-25 15:18

Fix Version/s		10.10 [ 27530 ]
Fix Version/s	10.8 [ 26121 ]

Andrei Elkin made changes - 2022-04-25 15:18

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Andrei Elkin made changes - 2022-06-08 12:58

Status

Stalled [ 10000 ]

In Review [ 10002 ]

Andrei Elkin made changes - 2022-06-08 13:04

Assignee	Andrei Elkin [ elkin ]	Brandon Nesterenko [ JIRAUSER48702 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Brandon Nesterenko made changes - 2022-06-13 20:19

Summary

With MASTER_USE_GTID=current_pos, slave's I/O thread only checks gtid_current_pos when thread is first started

Deprecate MASTER_USE_GTID=Current_Pos to favor new MASTER_DEMOTE_TO_SLAVE option

Brandon Nesterenko made changes - 2022-06-13 23:27

Description

When a slave is configured to replicate with "MASTER_USE_GTID=current_pos", the slave uses its value of gtid_current_pos to replicate from the master.

https://mariadb.com/kb/en/library/change-master-to/#master_use_gtid

https://mariadb.com/kb/en/library/gtid/#gtid_current_pos

The value of gtid_current_pos includes GTIDs from both gtid_slave_pos and gtid_binlog_pos:

https://mariadb.com/kb/en/library/gtid/#gtid_slave_pos

https://mariadb.com/kb/en/library/gtid/#gtid_binlog_pos

Since both gtid_slave_pos and gtid_binlog_pos are used, this means that the position takes into account both local transactions and replicated transactions. This can be somewhat problematic, since it means that executing a single local transaction on the slave can end up breaking replication, due to the fact that the local transaction would cause the slave's GTID position to become inconsistent with the master's GTID position. However, in my opinion, this makes sense, given the design of the GTID functionality. To prevent this specific issue, if a slave is using "MASTER_USE_GTID=current_pos", then it should have read_only=ON set.

However, the more problematic issue is that MariaDB will not alert users to the inconsistent GTID position until the slave threads are restarted. If the slave is running smoothly, then the slave threads may not be restarted for weeks or months.

The root cause of this appears to be that the slave's I/O thread only initializes its local value of gtid_current_pos when the thread is first started in start_slave_threads():

https://github.com/MariaDB/server/blob/mariadb-10.4.6/sql/slave.cc#L1400

This means that if a local transaction is executed on the slave, then the slave won't notice that its GTID position is inconsistent with the master until the slave threads are restarted.

For example, let's say that I have a master and a slave.

The master's GTID position:

{noformat}
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-1 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_domain_id | 3 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

The slave's GTID position:

{noformat}
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-1 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_domain_id | 4 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

And let's say that the slave is configured to use "MASTER_USE_GTID=current_pos":

{noformat}
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.30.0.105', MASTER_USER='maxscale', MASTER_PASSWORD='password', MASTER_USE_GTID=current_pos;
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.045 sec)
{noformat}

And the slave is initially replicating normally:

{noformat}
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000002
                 Relay_Log_Pos: 717
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 1035
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-1,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

But then let's say that we execute a local transaction on the slave. We can see that the slave's gtid_binlog_pos changes:

{noformat}
MariaDB [(none)]> CREATE DATABASE slave_db;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-2 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-2 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-2 |
| gtid_domain_id | 4 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

But at first, the slave doesn't actually notice that its position is inconsistent with the master:

{noformat}
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000002
                 Relay_Log_Pos: 717
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 1035
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-1,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

The slave only notices when the slave threads are restarted:

{noformat}
MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 296
               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 4-2-2, which is not in the master's binlog'
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-2,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

I think the slave should warn the user about this, so that users can be aware of inconsistent positions, even when the slave threads are not restarted.

For example, here's one potential fix:

If a slave has "MASTER_USE_GTID=current_pos" set, then the slave's I/O thread could periodically compare the thread's local value of gtid_current_pos (i.e. mi->gtid_current_pos) to the slave's global value of gtid_binlog_pos. If the global value of gtid_binlog_pos contains GTIDs that are greater than the GTIDs in the thread's local value of gtid_current_pos (i.e. mi->gtid_current_pos), then the slave could write a warning to the error log. If gtid_strict_mode were enabled, then maybe the warning could be changed to an error.

Description update after problem discussion:

This work deprecates Current_Pos as an option to CHANGE MASTER TO MASTER_USE_GTID while also adding a safe replacement option MASTER_DEMOTE_TO_SLAVE=<bool>. Specifically, the use case of Current_Pos is to transition a master to become a slave; however, this can break replication state due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos.

MASTER_DEMOTE_TO_SLAVE changes this use case by forcing users to set Using_Gtid=Slave_Pos and merging gtid_binlog_pos into gtid_slave_pos once at CHANGE MASTER TO time. Note that if gtid_slave_pos is more recent than gtid_binlog_pos (as in the case of chain replication), the replication state should be preserved.

Then, MASTER_USE_GTID=Current_Pos is deprecated in favor of using Slave_Pos in combination with MASTER_DEMOTE_TO_SLAVE=1.

==========================
Original Description:
==========================

When a slave is configured to replicate with "MASTER_USE_GTID=current_pos", the slave uses its value of gtid_current_pos to replicate from the master.

https://mariadb.com/kb/en/library/change-master-to/#master_use_gtid

https://mariadb.com/kb/en/library/gtid/#gtid_current_pos

The value of gtid_current_pos includes GTIDs from both gtid_slave_pos and gtid_binlog_pos:

https://mariadb.com/kb/en/library/gtid/#gtid_slave_pos

https://mariadb.com/kb/en/library/gtid/#gtid_binlog_pos

Since both gtid_slave_pos and gtid_binlog_pos are used, this means that the position takes into account both local transactions and replicated transactions. This can be somewhat problematic, since it means that executing a single local transaction on the slave can end up breaking replication, due to the fact that the local transaction would cause the slave's GTID position to become inconsistent with the master's GTID position. However, in my opinion, this makes sense, given the design of the GTID functionality. To prevent this specific issue, if a slave is using "MASTER_USE_GTID=current_pos", then it should have read_only=ON set.

However, the more problematic issue is that MariaDB will not alert users to the inconsistent GTID position until the slave threads are restarted. If the slave is running smoothly, then the slave threads may not be restarted for weeks or months.

The root cause of this appears to be that the slave's I/O thread only initializes its local value of gtid_current_pos when the thread is first started in start_slave_threads():

https://github.com/MariaDB/server/blob/mariadb-10.4.6/sql/slave.cc#L1400

This means that if a local transaction is executed on the slave, then the slave won't notice that its GTID position is inconsistent with the master until the slave threads are restarted.

For example, let's say that I have a master and a slave.

The master's GTID position:

{noformat}
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-1 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_domain_id | 3 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

The slave's GTID position:

{noformat}
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-1 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_domain_id | 4 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

And let's say that the slave is configured to use "MASTER_USE_GTID=current_pos":

{noformat}
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.30.0.105', MASTER_USER='maxscale', MASTER_PASSWORD='password', MASTER_USE_GTID=current_pos;
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.045 sec)
{noformat}

And the slave is initially replicating normally:

{noformat}
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000002
                 Relay_Log_Pos: 717
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 1035
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-1,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

But then let's say that we execute a local transaction on the slave. We can see that the slave's gtid_binlog_pos changes:

{noformat}
MariaDB [(none)]> CREATE DATABASE slave_db;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-2 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-2 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-2 |
| gtid_domain_id | 4 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

But at first, the slave doesn't actually notice that its position is inconsistent with the master:

{noformat}
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000002
                 Relay_Log_Pos: 717
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 1035
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-1,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

The slave only notices when the slave threads are restarted:

{noformat}
MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 296
               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 4-2-2, which is not in the master's binlog'
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-2,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

I think the slave should warn the user about this, so that users can be aware of inconsistent positions, even when the slave threads are not restarted.

For example, here's one potential fix:

If a slave has "MASTER_USE_GTID=current_pos" set, then the slave's I/O thread could periodically compare the thread's local value of gtid_current_pos (i.e. mi->gtid_current_pos) to the slave's global value of gtid_binlog_pos. If the global value of gtid_binlog_pos contains GTIDs that are greater than the GTIDs in the thread's local value of gtid_current_pos (i.e. mi->gtid_current_pos), then the slave could write a warning to the error log. If gtid_strict_mode were enabled, then maybe the warning could be changed to an error.

Brandon Nesterenko made changes - 2022-06-13 23:28

Description

Description update after problem discussion:

This work deprecates Current_Pos as an option to CHANGE MASTER TO MASTER_USE_GTID while also adding a safe replacement option MASTER_DEMOTE_TO_SLAVE=<bool>. Specifically, the use case of Current_Pos is to transition a master to become a slave; however, this can break replication state due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos.

MASTER_DEMOTE_TO_SLAVE changes this use case by forcing users to set Using_Gtid=Slave_Pos and merging gtid_binlog_pos into gtid_slave_pos once at CHANGE MASTER TO time. Note that if gtid_slave_pos is more recent than gtid_binlog_pos (as in the case of chain replication), the replication state should be preserved.

Then, MASTER_USE_GTID=Current_Pos is deprecated in favor of using Slave_Pos in combination with MASTER_DEMOTE_TO_SLAVE=1.

==========================
Original Description:
==========================

When a slave is configured to replicate with "MASTER_USE_GTID=current_pos", the slave uses its value of gtid_current_pos to replicate from the master.

https://mariadb.com/kb/en/library/change-master-to/#master_use_gtid

https://mariadb.com/kb/en/library/gtid/#gtid_current_pos

The value of gtid_current_pos includes GTIDs from both gtid_slave_pos and gtid_binlog_pos:

https://mariadb.com/kb/en/library/gtid/#gtid_slave_pos

https://mariadb.com/kb/en/library/gtid/#gtid_binlog_pos

Since both gtid_slave_pos and gtid_binlog_pos are used, this means that the position takes into account both local transactions and replicated transactions. This can be somewhat problematic, since it means that executing a single local transaction on the slave can end up breaking replication, due to the fact that the local transaction would cause the slave's GTID position to become inconsistent with the master's GTID position. However, in my opinion, this makes sense, given the design of the GTID functionality. To prevent this specific issue, if a slave is using "MASTER_USE_GTID=current_pos", then it should have read_only=ON set.

However, the more problematic issue is that MariaDB will not alert users to the inconsistent GTID position until the slave threads are restarted. If the slave is running smoothly, then the slave threads may not be restarted for weeks or months.

The root cause of this appears to be that the slave's I/O thread only initializes its local value of gtid_current_pos when the thread is first started in start_slave_threads():

https://github.com/MariaDB/server/blob/mariadb-10.4.6/sql/slave.cc#L1400

This means that if a local transaction is executed on the slave, then the slave won't notice that its GTID position is inconsistent with the master until the slave threads are restarted.

For example, let's say that I have a master and a slave.

The master's GTID position:

{noformat}
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-1 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_domain_id | 3 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

The slave's GTID position:

{noformat}
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-1 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_domain_id | 4 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

And let's say that the slave is configured to use "MASTER_USE_GTID=current_pos":

{noformat}
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.30.0.105', MASTER_USER='maxscale', MASTER_PASSWORD='password', MASTER_USE_GTID=current_pos;
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.045 sec)
{noformat}

And the slave is initially replicating normally:

{noformat}
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000002
                 Relay_Log_Pos: 717
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 1035
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-1,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

But then let's say that we execute a local transaction on the slave. We can see that the slave's gtid_binlog_pos changes:

{noformat}
MariaDB [(none)]> CREATE DATABASE slave_db;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-2 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-2 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-2 |
| gtid_domain_id | 4 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

But at first, the slave doesn't actually notice that its position is inconsistent with the master:

{noformat}
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000002
                 Relay_Log_Pos: 717
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 1035
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-1,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

The slave only notices when the slave threads are restarted:

{noformat}
MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 296
               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 4-2-2, which is not in the master's binlog'
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-2,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

I think the slave should warn the user about this, so that users can be aware of inconsistent positions, even when the slave threads are not restarted.

For example, here's one potential fix:

If a slave has "MASTER_USE_GTID=current_pos" set, then the slave's I/O thread could periodically compare the thread's local value of gtid_current_pos (i.e. mi->gtid_current_pos) to the slave's global value of gtid_binlog_pos. If the global value of gtid_binlog_pos contains GTIDs that are greater than the GTIDs in the thread's local value of gtid_current_pos (i.e. mi->gtid_current_pos), then the slave could write a warning to the error log. If gtid_strict_mode were enabled, then maybe the warning could be changed to an error.

======================================
Description update after problem discussion:
======================================

This work deprecates Current_Pos as an option to CHANGE MASTER TO MASTER_USE_GTID while also adding a safe replacement option MASTER_DEMOTE_TO_SLAVE=<bool>. Specifically, the use case of Current_Pos is to transition a master to become a slave; however, this can break replication state due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos.

MASTER_DEMOTE_TO_SLAVE changes this use case by forcing users to set Using_Gtid=Slave_Pos and merging gtid_binlog_pos into gtid_slave_pos once at CHANGE MASTER TO time. Note that if gtid_slave_pos is more recent than gtid_binlog_pos (as in the case of chain replication), the replication state should be preserved.

Then, MASTER_USE_GTID=Current_Pos is deprecated in favor of using Slave_Pos in combination with MASTER_DEMOTE_TO_SLAVE=1.

==========================
Original Description:
==========================

When a slave is configured to replicate with "MASTER_USE_GTID=current_pos", the slave uses its value of gtid_current_pos to replicate from the master.

https://mariadb.com/kb/en/library/change-master-to/#master_use_gtid

https://mariadb.com/kb/en/library/gtid/#gtid_current_pos

The value of gtid_current_pos includes GTIDs from both gtid_slave_pos and gtid_binlog_pos:

https://mariadb.com/kb/en/library/gtid/#gtid_slave_pos

https://mariadb.com/kb/en/library/gtid/#gtid_binlog_pos

Since both gtid_slave_pos and gtid_binlog_pos are used, this means that the position takes into account both local transactions and replicated transactions. This can be somewhat problematic, since it means that executing a single local transaction on the slave can end up breaking replication, due to the fact that the local transaction would cause the slave's GTID position to become inconsistent with the master's GTID position. However, in my opinion, this makes sense, given the design of the GTID functionality. To prevent this specific issue, if a slave is using "MASTER_USE_GTID=current_pos", then it should have read_only=ON set.

However, the more problematic issue is that MariaDB will not alert users to the inconsistent GTID position until the slave threads are restarted. If the slave is running smoothly, then the slave threads may not be restarted for weeks or months.

The root cause of this appears to be that the slave's I/O thread only initializes its local value of gtid_current_pos when the thread is first started in start_slave_threads():

https://github.com/MariaDB/server/blob/mariadb-10.4.6/sql/slave.cc#L1400

This means that if a local transaction is executed on the slave, then the slave won't notice that its GTID position is inconsistent with the master until the slave threads are restarted.

For example, let's say that I have a master and a slave.

The master's GTID position:

{noformat}
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-1 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_domain_id | 3 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

The slave's GTID position:

{noformat}
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-1 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-1 |
| gtid_domain_id | 4 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

And let's say that the slave is configured to use "MASTER_USE_GTID=current_pos":

{noformat}
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.30.0.105', MASTER_USER='maxscale', MASTER_PASSWORD='password', MASTER_USE_GTID=current_pos;
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.045 sec)
{noformat}

And the slave is initially replicating normally:

{noformat}
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000002
                 Relay_Log_Pos: 717
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 1035
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-1,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

But then let's say that we execute a local transaction on the slave. We can see that the slave's gtid_binlog_pos changes:

{noformat}
MariaDB [(none)]> CREATE DATABASE slave_db;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| gtid_binlog_pos | 1-1-95,3-1-1,4-2-2 |
| gtid_binlog_state | 1-1-95,3-1-1,4-2-2 |
| gtid_current_pos | 1-1-95,3-1-1,4-2-2 |
| gtid_domain_id | 4 |
| gtid_ignore_duplicates | OFF |
| gtid_pos_auto_engines | |
| gtid_slave_pos | 1-1-95,3-1-1 |
| gtid_strict_mode | OFF |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+--------------------+
10 rows in set (0.001 sec)
{noformat}

But at first, the slave doesn't actually notice that its position is inconsistent with the master:

{noformat}
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000002
                 Relay_Log_Pos: 717
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 1035
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-1,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

The slave only notices when the slave threads are restarted:

{noformat}
MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 172.30.0.105
                   Master_User: maxscale
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 376
                Relay_Log_File: ip-172-30-0-96-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mariadb-bin.000001
              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: 376
               Relay_Log_Space: 296
               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 4-2-2, which is not in the master's binlog'
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 1-1-95,4-2-2,3-1-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
{noformat}

I think the slave should warn the user about this, so that users can be aware of inconsistent positions, even when the slave threads are not restarted.

For example, here's one potential fix:

If a slave has "MASTER_USE_GTID=current_pos" set, then the slave's I/O thread could periodically compare the thread's local value of gtid_current_pos (i.e. mi->gtid_current_pos) to the slave's global value of gtid_binlog_pos. If the global value of gtid_binlog_pos contains GTIDs that are greater than the GTIDs in the thread's local value of gtid_current_pos (i.e. mi->gtid_current_pos), then the slave could write a warning to the error log. If gtid_strict_mode were enabled, then maybe the warning could be changed to an error.

Brandon Nesterenko made changes - 2022-06-14 03:31

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Brandon Nesterenko made changes - 2022-06-14 03:40

Status

In Progress [ 3 ]

In Testing [ 10301 ]

Brandon Nesterenko made changes - 2022-06-14 03:40

Status

In Testing [ 10301 ]

Stalled [ 10000 ]

Brandon Nesterenko made changes - 2022-06-14 03:42

Assignee	Brandon Nesterenko [ JIRAUSER48702 ]	Andrei Elkin [ elkin ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Andrei Elkin made changes - 2022-06-14 11:25

Link

This issue relates to ~~MDEV-28839~~ [ ~~MDEV-28839~~ ]

Andrei Elkin made changes - 2022-06-14 13:10

Assignee	Andrei Elkin [ elkin ]	Brandon Nesterenko [ JIRAUSER48702 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Brandon Nesterenko made changes - 2022-06-15 00:14

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Brandon Nesterenko made changes - 2022-06-15 00:14

Assignee

Brandon Nesterenko [ JIRAUSER48702 ]

Angelique Sklavounos [ JIRAUSER50741 ]

Brandon Nesterenko made changes - 2022-06-21 21:48

Link

This issue relates to TODO-3496 [ TODO-3496 ]

Angelique Sklavounos (Inactive) made changes - 2022-07-21 12:12

Status

In Testing [ 10301 ]

Stalled [ 10000 ]

Angelique Sklavounos (Inactive) made changes - 2022-07-21 12:12

Assignee

Angelique Sklavounos [ JIRAUSER50741 ]

Brandon Nesterenko [ JIRAUSER48702 ]

Brandon Nesterenko made changes - 2022-07-26 02:42

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Brandon Nesterenko made changes - 2022-07-26 02:42

Assignee	Brandon Nesterenko [ JIRAUSER48702 ]	Andrei Elkin [ elkin ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Andrei Elkin made changes - 2022-07-26 17:26

Assignee	Andrei Elkin [ elkin ]	Brandon Nesterenko [ JIRAUSER48702 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Brandon Nesterenko made changes - 2022-07-30 14:12

Fix Version/s		10.10.0 [ 27912 ]
Fix Version/s	10.10 [ 27530 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Brandon Nesterenko made changes - 2023-02-13 14:04

Link

This issue relates to ~~MDEV-30647~~ [ ~~MDEV-30647~~ ]

Ralf Gebhardt made changes - 2023-03-21 10:44

Labels

gtid_current_pos

Preview_10.10 gtid_current_pos

Ralf Gebhardt made changes - 2023-06-13 12:43

Affects Version/s	10.2.25 [ 23408 ]
Affects Version/s	10.3.16 [ 23410 ]
Affects Version/s	10.4.6 [ 23412 ]
Issue Type	Bug [ 1 ]	Task [ 3 ]

Brandon Nesterenko made changes - 2023-12-08 17:27

Link

This issue split to ~~MDEV-32976~~ [ ~~MDEV-32976~~ ]

Ralf Gebhardt made changes - 2024-02-20 13:35

Link

This issue causes ~~MDEV-31768~~ [ ~~MDEV-31768~~ ]

Roel Van de Paar made changes - 2024-05-03 08:24

Link

This issue causes MDEV-34064 [ MDEV-34064 ]

Roel Van de Paar made changes - 2024-05-03 08:27

Link

This issue relates to ~~MDEV-31768~~ [ ~~MDEV-31768~~ ]

Jira Automation (IT) made changes - 2024-07-04 03:50

Zendesk Related Tickets

125514

Brandon Nesterenko made changes - 2024-07-25 13:17

Link

This issue is duplicated by ~~MDEV-16800~~ [ ~~MDEV-16800~~ ]

MariaDB Server

Deprecate MASTER_USE_GTID=Current_Pos to favor new MASTER_DEMOTE_TO_SLAVE option

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration