Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34192

Support GTID_Anonymous to replicate MariaDB ⮕ MySQL with GTID_MODE = ON

Details

    Description

      MDEV-8685 added an acknowledgement that MySQL's anonymous GTIDs are a thing, enough to ignore them. I understand that MySQL 5.7.6+ adds these when a GTID is not present. This work is described in MySQL WL#7592.

      MySQL 8.0.23+ offers the ability to turn these anonymous GTID records into local or specified-base GTID with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS - the LOCAL option essentially upgrades the binlog to be compatible with GTID-only downstream replicas, with the caveats noted.

      This feature does not appear to work when there is no such record, as in replication from a MariaDB 10.11 server running in GTID_MODE = OFF, where a timestamp is inserted instead. Instead, an error is thrown trying to execute the relay log, as the replica's GTID_MODE = ON. So it'd be nice if MariaDB could operate in a compatibility mode that added these records to the binlog instead of timestamps (and potentially BEGIN) so that it was possible to feed a GTID-enabled MySQL replica in this manner.

      Why? I'd like to use a cloud provider's MySQL hosting for a partial regional read-only replica, keeping my core services MariaDB, rather than switch to MySQL - which isn't supported on Debian, although I got the Ubuntu 22.04 LTS jammy-updates package working anyway to try to work around this issue.

      Starting replication to the provider requires GTID_MODE = ON (not ON_PERMISSIVE), and uses AUTO_POSITION = 1, which causes replication to fail if I switch it back, as there are now anonymous transactions in the mix (e.g. "MySQL Errno - 13114: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1, at file ./binlog.000027, position 197...'").

      I'm given to understand that it may be possible to go MariaDB -> MySQL 5.7 -> MySQL 8.0, but this seems ill-advised, especially as MySQL 5.7 is no longer generally supported. (It was tolerable to add a MySQL 8 replica, as I can use a full read-only replica anyway.)

      I requested the provider add MariaDB (or consider relaxing their replication requirements), but given their focus is understandably on well-funded startups, this seems unlikely to happen. Getting ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS enabled might be easier, but would still require the MySQL intermediary unless MariaDB also implements them.

      Attachments

        Issue Links

          Activity

            There are no comments yet on this issue.

            People

              Unassigned Unassigned
              GreenReaper Laurence Parry
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.