MySQL Bugs: #71543: A new GTID_MODE is needed to evaluate/migrate to GTID: ANONYMOUS_IN-GTID

Bug #71543	A new GTID_MODE is needed to evaluate/migrate to GTID: ANONYMOUS_IN-GTID_OUT.
Submitted:	31 Jan 2014 14:08	Modified:	13 Nov 2015 19:06
Reporter:	Jean-François Gagné	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.6.15 & 5.7.3	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	GTID

Description:
In the current implementation of GTIDs, the feature can be OFF or ON (there is no way to partially enable them).  This makes migrating to GTID a risky operation as there is no way to evaluate their behavior before enabling them in production.

A new GTID_MODE, ANONYMOUS_IN-GTID_OUT, would:

1. Allow to evaluate the GTID feature,
2. Provide a minimal downtime upgrade path.

This new mode is mostly the same as GTID_MODE=ON with the following difference:

a. A master in GTID_MODE=ANONYMOUS_IN-GTID_OUT behaves the same way as in GTID_MODE=ON:

i.  It advertises GTID_MODE=ON to its slaves,
ii. It allocates GTIDs to local transactions.

b. A slave in GTID_MODE=ANONYMOUS_IN-GTID_OUT does 2 things that a GTID_MODE=ON slave does not: 

i.  It accepts to connect to a master with GTID_MODE=OFF,
ii. It executes anonymous transactions received from its master and assigns those a "local" GTID.

With this new mode, we can deploy a GTID_MODE=ANONYMOUS_IN-GTID_OUT node in a GTID_MODE=OFF replication tree (the same way we can deploy a MySQL 5.7 node in a 5.6 replication tree to evaluate the new version before deploying).

Using the replication tree bellow as an example:

1. All nodes are first in GTID_MODE=OFF,
2. B is restarted with GTID_MODE=ANONYMOUS_IN-GTID_OUT.

 -----
 | A |
 -----
   |
   +------+------+------+------+
   |      |      |      |      |
   V      V      V      V      V
 -----  -----  -----  -----  -----
 | B |  | C |  | D |  | E |  | F |
 -----  -----  -----  -----  -----

Having B running in GTID_MODE=ANONYMOUS_IN-GTID_OUT does not change the architecture of the system: writes are done on A and reads can be done on all nodes.

Once we are happy running B for some time with GTID_MODE=ANONYMOUS_IN-GTID_OUT, we can go one step further by moving C and D under B (enabling GTID_MODE=ON).  We are now in the configuration bellow where we can evaluate GTID more extensively.  We are then in this configuration:

 -----
 | A |
 -----
   |
   +--------------------+------+
   |                    |      |
   V                    V      V
 -----                -----  -----
 | B |                | E |  | F |
 -----                -----  -----
   |
   +------+
   |      |
   V      V
 -----  -----
 | C |  | D |
 -----  -----

Once we are done evaluating, the upgrade to GTID is easy:

1. Move E and F under B,
2. Re-point writes to B.

How to repeat:
Feature request, see description.

Related: BUG#69059.

Hello Jean-François,

Thank you for the feature request!

Thanks,
Umesh

Fixed in MySQL 5.7.6 by WL#7083 'SET gtid_mode ONLINE'.

Closed.

Please consider re-opening. The implementation in 5.7 GA is not the same and has some drawbacks, especially when evaluating GTID or MySQL Group Replication if servers aren't already in GTID mode.

The modes you have do not generate GTIDS from "anonymous" transactions which come from upstream unless you are in GTID_MODE = ON.
5.7 will receive an "anonymous transaction" and just pass it downstream in ON_PERMISSIVE or OFF_PERMISSIVE but leaving it as "anonymous". Hence you can't use AUTO_POSITION in this setup as no GTIDs actually exist until you complete the last step of setting it to ON.

This means that if still testing 5.6 you can't fully test 5.7 before migrating to it which is not ideal. This also affects testing of GR in a similar manner. It would be nice to be able to test a 5.7 GR pair of boxes replicating from a 5.6 non-GTID master master setup (only one master being written to) but there's no way to enable GTID and push GTID events into the 5.7 GR servers which need GTID to work. Migration is possible in other ways but the suggestion here from my colleague was a great way to allow you to test without necessarily having to commit to something that might not work as expected.

Maybe adding a new mode in 5.7 is not practical for incompatibility reasons but if that were to be done or if this were to be considered again for 5.8, and given the gtid_modes now have different names, I'd suggest a name like ON_PERMISSIVE_GENERATE which would convert autonomous transactions into GTID transactions as if they'd be generated locally on the server in this mode.

Reopening as requested by Simon. Justification is that although online enabling of GTID is now possible, there is no gtid_mode that provides ANONYMOUS_IN-GTID_OUT (workaround is to replay the binary log into a ON_PERMISSIVE master). 

There might be some problems with this mode, as described in this worklog:
======
1.1. Requirement: ANONYMOUS TRANSACTION MUST REMAIN ANONYMOUS ON RE-EXECUTION
-----------------------------------------------------------------------------

An anonymous transaction must be kept anonymous when re-executed. If
an old master generates an anonymous transaction, then the new slave
must preserve anonymity and not generate a new GTID. The following
example illustrates what would happen if anonymous transactions were
not kept anonymous when replicated:

                              +---------->  Server B
                              |             GTID_MODE=ON
     Server A         --------+             Binlog: T1 (GTID=B:1)
     GTID_MODE=OFF            |
     Binlog: T1 (GTID=anon)   +---------->  Server C
                                            GTID_MODE=ON
                                            Binlog: T1 (GTID=C:1)
https://dev.mysql.com/worklog/task/?id=7083
======

I will let the replication team evaluate how feasible it is to implement this FR.

Leandro.

The requirement part is I think a false one for this case. Anyone using this sort of layout is NOT going to replicate to 2 different servers and then expect any slaves underneath to have the exact same set of GTIDs.  I see this is a migration path not as an "expected to be used in normal operation" situation. If implemented documentation should make that clear.

So my intended use case here was to move from an existing setup (5.6 SBR/non-gtid) and "feed" a new setup (in this case to 5.7 Group replication, which needs GTID and RBR).  This is likely to be under evaluation for some time (days / weeks) before deciding to make the switch.

[Observation: more discussion with users on how they operate and migrate/upgrade their systems and the issues involved in environments which can not be stopped seems worthwhile to ensure that the possible migration strategies are as flexible as possible where this is reasonable.]

Note: the workaround is not as you state but to:

(workaround is to replay the binary log into a gtid_mode=ON master)

The only issue I currently see with this is being able to stop/start/continue the process once you start. mysqlbinlog does not I think have signal handling so can not catch a request to stop it, nor does if stopped it write it's latest position to any file to read later when it starts.

There had been some discussion with a colleague to use pseudo-gtids which we use on most systems and to sync up the position on the master with the injected position on the GTID downstream GR servers, but I doubt this will work given upstream is SBR/non-gtid and downstream would be GTID RBR. Orchestrator at the moment is not I think able to synchronise positions in such a situation, so some other mechanism might be needed.

Note: while I've requested this FR to be reopened this is not a critical requirement, but it does make evaluation much much easier for the reasons described.  Especially when considering something like moving an existing Master-master setup to a possible GR replacement

Hi Simon,

I listed Requirements in worklog 7083 so readers of this FR would be aware what can go wrong with the FR use. It's just a heads up as it's too early to tell if/how/when the FR will be implemented.

As for the workaround of feeding the binlog, if master being fed is 5.6, then yes, there is only gtid_mode=ON. However, if you want to replay a 5.6 binlog into 5.7 master, then you'll need to use gtid_mode=ON_PERMISSIVE and skip-gtids on mysqlbinlog.
  https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#option_mysqld_gtid-...
 https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html#option_mysqlbinlog_skip-gtids

Regards,
Leandro Morgado