WL#12819: GTIDs: Replicate from GTID disabled source to GTID enabled replica directly

Affects: Server-8.0 — Status: Complete

Description
Requirements
High Level Architecture
Low Level Design

EXECUTIVE SUMMARY
=================

This worklog implements a new option ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = [OFF, LOCAL, ] which will be a per channel property to be specified with
CHANGE MASTER that allows direct data flow between a non-GTID replication setup and a GTID replication setup. After this worklog, a user shall be able to replicate from a GTID disabled source to a GTID enabled replica without any other middleware or any upgrade procedure. This will enable online migration of data between cloud providers that do   not support GTIDs and Oracle Cloud.

USER STORIES
============

- As a MySQL user I want to replicate from a non GTID replication
  setup to a GTID setup so I can:

  a) Replicate from Cloud X (which does not support GTIDs) to MySQL
     DBaaS @ OCI.

  b) Migrate from an on-premise deployment using GTID_MODE = OFF,
     into MySQL DBaaS @ OCI.

  c) For an existing production workload executed on a server with
     GTID_MODE = OFF, pre-check how it would behave if GTIDs were enabled.

SCOPE/LIMITS
============

a) This feature is intended for cases when the originating server cannot
   enable GTIDs. For instance, when the user does not have full control over
   the originating server, or when there is a legacy application running on the
   originating server which cannot enable GTIDs.

b) Lets assume the topology has the following parts:
   - There is exactly one part that does not use GTIDs.
   - There is one or more parts that use GTIDs, each one having an "entry point"
     consisting of a server that uses the new feature when replicating from the
     non-GTID part.
   Now for this the limitations will be:
   1) Servers must not be moved between the parts.
   2) There must not be replication between the parts, except for the entry 
      points.
   3) Clients must not fail-over, or in any other way transfer or compare GTIDs
      between the different GTID parts.
   4) The backup of a server in one part must not be restored to deploy a new
      server in another part.

c) This WL will not create a new replication handshake protocol.

d) The new option can not be used together with AUTO_POSITION in the same 
   channel.

e) This WL must not design and implement a new failover mechanism built into
   server for this new option "ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS".

f) ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS can not be used to change a non-GTID 
   setup into a GTID setup in-place.

g) Specify LOCAL if you want the transaction identifier to use the UUID of the 
   local replica server, and specify a different UUID manually if you want to 
   differentiate the transactions arriving on this channel from other 
   transactions on the local replica server.

h) Gtids generated this way may be out of order when using MTS even with
   slave-preserve-commit-order=1. The reason behind this is that Gtids are
   being generated on the replica at applying time and not during commit.

REFERENCES
==========

The following refers to stories of people migrating between cloud
providers with different GTID modes:

- https://medium.com/youngcapital-it-talks/moving-from-aws-aurora-to-google-
  cloudsql-73484e4d663f

The following references are more related to people asking for a
direct mode to migrate/test GTIDs, by deploying a GTID enabled replica
replicating from a GTID disabled source:

- A new GTID_MODE is needed to evaluate/migrate to GTID: ANONYMOUS_IN-GTID_OUT.
  https://bugs.mysql.com/bug.php?id=71543

- https://medium.com/booking-com-infrastructure/mysql-5-6-gtids-evaluation-and-
  online-migration-139693719ff2

- https://code.fb.com/core-data/lessons-from-deploying-mysql-gtid-at-scale/

FUNCTIONAL REQUIREMENTS:
------------------------

F-0: There shall be a new clause in CHANGE MASTER command with the following 
     syntax
     CHANGE MASTER ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {OFF, LOCAL, or a   
     string containing a valid UUID}

F-1: The option "ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS" must not be set to 
     {LOCAL | } when GTID_MODE <> ON

F-2: The applier shall generate new GTIDs for transactions during applying. The 
     UUID part of the GTIDs generated will depend on the value of 
     ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS.
     If it is LOCAL than the UUID of the server on which anonymous_gtid event 
     was being applied will be used, and if it , then the UUID specified 
     at the time of CHM will be used.

F-3: It is not allowed to configure MASTER_AUTO_POSITION=1 and
     ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {LOCAL | } at the same 
     time'.

F-4: When the option ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {LOCAL | } 
     is used in a replication channel, the following shall not be errors:

    - Connecting to a source having GTID_MODE = OFF | OFF_PERMISSIVE, when this 
      replica has GTID_MODE = ON
    - Receiving an anonymous transaction when GTID_MODE = ON.
    - Applying an anonymous transaction when GTID_MODE = ON


F-5: It is also allowed for the channel to receive and apply GTID transactions 
     from the source. Those GTID transactions will retain their existing GTID 
     value.

F-6: It is allowed to use this feature together with semisync.

F-7: ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS must be allowed on all non-GR 
     channels, even in case GR is enabled.
     In case GR is enabled, it can either use LOCAL (which means server_uuid), 
     or a specified UUID. In both cases, it(UUID) must be different from 
     group_replication_group_name.

F-8: There shall be new columns in the 
     performance_schema.replication_applier_configuration table, having the
     following definition:
     Assign_gtids_to_anonymous_transactions_type ENUM('OFF', 'LOCAL', 'MANUAL') 
     NOT NULL COMMENT 'Indicates whether anonymous transactions are converted to 
     use a GTID or not. If the type is LOCAL or MANUAL, anonymous transactions 
     are converted to GTID transactions.'

     Assign_gtids_to_anonymous_transactions_value TEXT CHARACTER SET utf8 
     COLLATE utf8_bin DEFAULT NULL COMMENT 'Indicates the UUID used while 
     generating GTIDs for anonymous transactions.'

F-9: There shall be new columns in the mysql.slave_relay_log_info table, with 
     the following definition:
     Assign_gtids_to_anonymous_transactions_type ENUM('OFF', 'LOCAL', 'MANUAL') 
     NOT NULL COMMENT 'Indicates whether anonymous transactions are converted to 
     use a GTID or not.
     If the type is LOCAL or MANUAL, anonymous transactions are converted to 
     GTID transactions.'

     Assign_gtids_to_anonymous_transactions_value TEXT CHARACTER SET utf8 
     COLLATE utf8_bin DEFAULT NULL  COMMENT 'Indicates the UUID used while 
     generating GTIDs for anonymous transactions.'

F-10:When slave-relay-log-repository=FILE, there shall be two new lines in the 
     file. The first line shall contain the value for 
     Assign_gtids_to_anonymous_transactions_type, and the second one will 
     contain a string if the Assign_gtids_to_anonymous_transactions_type is not 
     OFF and NULL otherwise.

F-11:CHANGE MASTER TO ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS shall be 
     disallowed when either the receiver or the applier thread is running; both 
     must be stopped.

F-12:After an upgrade from a version not having this feature, pre-existing 
     channels shall have this feature disabled and use the value OFF.

F-13:The default for new replication channels shall be that the feature is 
     disabled. Having the value 'OFF'

F-14:RESET SLAVE without ALL shall not alter the option.

F-15:SET GTID_MODE <> ON should be disallowed when any channel uses
     ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {LOCAL | }

F-16:SET sql_slave_skip_counter shall be allowed regardless of gtid_mode. But 
     it shall generate a warning when GTID_MODE = ON, stating that it will only 
     take effect for channels using ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = 
     {LOCAL | }.

F-17:SET GTID_MODE = ON shall be allowed regardless of sql_slave_skip_counter. 
     But it shall generate a warning when sql_slave_skip_counter > 0, stating 
     that this makes sql_slave_skip_counter apply only to channels using 
     ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {LOCAL | }.

F-18:Channels decide at the time the applier starts, whether they use
     sql_slave_skip_counter or not. If the channel has 
     ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {LOCAL | }, or if GTID_MODE 
     != ON when the channel starts, then the channel uses 
     sql_slave_skip_counter. If the channel have
     ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = OFF and GTID_MODE = ON, the 
     channel does not use sql_slave_skip_counter.

F-19:This will be implemented on a per channel basis, in a MSR setup it SHALL be
     possible to have some channels using ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS 
     = {LOCAL| }

F-20:When the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = LOCAL, the gtid generated 
     corresponding to the anonymous_gtid event MUST have the UUID of the local 
     server(the replica or secondary on which events are being applied).

F-21:When the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = , the gtid 
     generated corresponding to the anonymous_gtid event MUST have the UUID 
     specified at the time of CHANGE MASTER command.

SUMMARY OF THE APPROACH:
-----------------------
Add a new parameter ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS in the CHANGE MASTER command, which when set to the value {LOCAL| } will be used to make replication work between a non-GTID setup and a GTID setup.

SECURITY CONTEXT:
-----------------
Nothing to specify

UPGRADE/DOWNGRADE and CROSS-VERSION REPLICATION:
------------------------------------------------

There is no issue on upgrade/downgrade and cross-version replication.

USER INTERFACE:
---------------

I-1: No new files
I-2: No new commands
I-3: No new tools
I-4: New option "ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS" for the command CHANGE
MASTER. it can have one of the values
{OFF, LOCAL, or a string containing a valid UUID}.
I-5: The default is OFF, the third option is when we want a specific UUID to be
used in the GTID value created while converting Anonymous_gtid event to
Gtid_event on replica server.
I-6: This value is persisted in the slave_relay_log_info file|table.

OBSERVABILITY:
--------------

Warnings:

1. New Warning when using the option ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS !=
OFF in the
CHANGE MASTER command, this warning is returned to the client which issued
CHANGE MASTER command.

ER_USING_ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS_AS_LOCAL_OR_UUID:
"Using ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS implies limitations on the
replication topology - you cannot fail-over between downstream and upstream
servers. Only use this option if it is not possible to enable GTIDs on the
source, for instance, because of lack of permissions. If possible, use the
procedure for enabling GTID transactions online instead, as described in the
documentation."

2. When server starts with GTID_MODE = OFF and any replication channel has
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS != OFF, the server shall generate a
warning message in the error log, but not change the gtid_mode or the option.
(This is analogous to when the server starts with GTID_MODE = OFF and
AUTO_POSITION = 1.)

ER_SLAVE_ANONYMOUS_TO_GTID_IS_LOCAL_OR_UUID_AND_GTID_MODE_NOT_ON:
"Replication channel '%.192s' is configured with
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS='%d',
which is invalid when GTID_MODE <> ON. If you intend to use GTID_MODE = ON
everywhere, change to ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = OFF and use
the procedure for enabling GTIDs online (see the documentation). If you
intend to use GTIDs on this server and cannot enable GTIDs on the source,
enable GTID_MODE = ON and leave ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS =
LOCAL|. If you intend to not use GTIDs at all in the replication
topology, change to ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = OFF and leave
GTID_MODE <> ON."

3. Issue a warning if sql_slave_skip_counter is set with GTID_MODE = ON.
Issue a warning if GTID_MODE = ON is set with sql_slave_skip_counter > 0,
this warning is returned to the client which issued either of these SET
statements..

ER_SQL_SLAVE_SKIP_COUNTER_USED_WITH_GTID_MODE_ON:
"The value of sql_slave_skip_counter will only take effect for channels
running with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS != OFF.

Error:

These errors are returned to the client which issued CHANGE MASTER command.

1. Setting AUTOPOSITION along with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS != OFF
should be disallowed

ER_CANT_COMBINE_ANONYMOUS_TO_GTID_AND_AUTOPOSITION: "The CHANGE MASTER TO
options ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = LOCAL| and
MASTER_AUTO_POSITION = 1 cannot be used together".

2. Setting ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {LOCAL| } requires
having GTID_MODE = ON

ER_ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS_REQUIRES_GTID_MODE_ON: "CHANGE
MASTER TO ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = LOCAL| cannot be
executed because @@GLOBAL.GTID_MODE <> ON."

3. Using WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS on a replication channel having
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS != OFF shall generate an error.

ER_CANT_SET_ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS_AND_WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS:
"WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS cannot be used on a channel configured
with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {LOCAL| }".

4. It should be disallowed to use START SLAVE UNTIL SQL_BEFORE_GTIDS and
START SLAVE UNTIL SQL_AFTER_GTIDS on channels having
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS != OFF.

ER_CANT_SET_SQL_AFTER_OR_BEFORE_GTIDS_WITH_ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS:
"The SQL_AFTER_GTIDS or SQL_BEFORE_GTIDS clauses for START SLAVE cannot be
used when the replication channel is configured with
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = LOCAL| ".

5. It should be disallowed to start replica when
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = LOCAL| and GTID_MODE <> ON
after restart

ER_CANT_USE_ANONYMOUS_TO_GTID_WITH_GTID_MODE_NOT_ON
"The replication receiver thread%-.192s cannot start with
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = LOCAL| this server uses
@@GLOBAL.GTID_MODE <> ON."

Change in Performance_schema.replication_applier_configuration table and mysql.slave_relay_log_info table
--------------------------------------------------------------------------------

There are two new columns added in the replication_applier_configuration and slave_relay_log_info table, corresponding to the value and type of rename_transaction configuration.

In case of the rename transaction type OFF the rename transaction value will be NULL.
In case of the rename transaction type LOCAL the rename transaction value will be the replica server UUID.
In case of the rename transaction type MANUAL the rename transaction value will be the UUID specified with Change master

DEPLOYMENT and INSTALLATION:
----------------------------

- No new files to package. No new files to install.
No new public header file to be included in the mysql dev package

PROTOCOL:
---------

This worklog MUST NOT create a new replication handshake protocol.

FAILURE MODEL SPECIFICATION:
----------------------------

No implications to the current state of the art in terms of failures model.

LOW-LEVEL DESIGN SPECIFICATION:
-------------------------------
This worklog will introduce a new parameter to the CHANGE MASTER command
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = [ OFF, LOCAL, ].