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

Replication fails when XA transactions are used where the slave has replicate_do_db set and the client has touched a different database when running DML such as inserts.

Details

    Description

      When a client runs an insert / delete / update on a master in a XA transaction, the replication fails if the following conditions are true:

      • `replicate_do_db` is present on the slave
      • A schema outside of `replicate_do_db` is touched either because:
        • DML statements are executed with a schema selected that is not listed in `replicate_do_db`
          including none (e.g. no `USE` or `USE junkschema` vs `USE srcschema`)
        • DML statements are executed on a schema outside of those listed in `replicate_do_db`
      • Two phase commits are used

      Considering the following setup in 10.5:

      Master:

      [mariadb]
      		 
      log-bin
      		 
      server_id=1
      		 
      log-basename=master-mariadb


      CREATE SCHEMA srcschema;
      		 
       
      		 
      USE srcschema;
      		 
       
      		 
      CREATE TABLE `srctable`
      		 
      (
      		 
          `srcidentity` int(11) NOT NULL,
      		 
          `srcnumber`   int(11) NOT NULL,
      		 
          PRIMARY KEY (`srcidentity`)
      		 
      ) ENGINE = InnoDB
      		 
        DEFAULT CHARSET = utf8
      		 
        COLLATE = utf8_general_ci;
      		 
       
      		 
      CREATE SCHEMA junkschema;
      		 
       
      		 
      USE junkschema;
      		 
       
      		 
      CREATE TABLE `junktable`
      		 
      (
      		 
          `srcidentity` int(11) NOT NULL,
      		 
          `srcnumber`   int(11) NOT NULL,
      		 
          PRIMARY KEY (`srcidentity`)
      		 
      ) ENGINE = InnoDB
      		 
        DEFAULT CHARSET = utf8
      		 
        COLLATE = utf8_general_ci

      Slave:

      [mariadb]
      		 
      server_id=2
      		 
      log-basename=slave-mariadb
      		 
      replicate_do_db=srcschema
      		 
      replicate_wild_do_table=srcschema.%

      This will crash the replication:
      USE outside replicate_do_db

      XA START 'testtx1';
      		 
      USE junkschema;
      		 
      INSERT INTO srcschema.srctable (srcidentity,srcnumber) VALUES (2,2);
      		 
      XA END 'testtx1';
      		 
      XA PREPARE 'testtx1';
      		 
      XA COMMIT 'testtx1';

      No USE

      XA START 'testtx1';
      		 
      INSERT INTO srcschema.srctable (srcidentity,srcnumber) VALUES (1,1);
      		 
      XA END 'testtx1';
      		 
      XA PREPARE 'testtx1';
      		 
      XA COMMIT 'testtx1';

      DML outside replicate_do_db

      XA START 'testtx1';
      		 
      USE junkschema;
      		 
      INSERT INTO junkschema.junktable (srcidentity,srcnumber) VALUES (1,1);
      		 
      XA END 'testtx1';
      		 
      XA PREPARE 'testtx1';
      		 
      XA COMMIT 'testtx1';

      For control this will pass:

      XA START 'testtx1';
      		 
      USE srcschema;
      		 
      INSERT INTO srcschema.srctable (srcidentity,srcnumber) VALUES (1,1);
      		 
      XA END 'testtx1';
      		 
      XA PREPARE 'testtx1';
      		 
      XA COMMIT 'testtx1';

      You can find my replication setup using Docker here: https://github.com/WorksWithJava/Mariadb-XA-10.5-bug-replication

      MariaDB logs:

      database_bugged_slave_setup-slave-1   | 2024-04-17  5:33:44 4 [Note] Slave I/O thread: connected to master 'slave_user@mariadb-master:3306',replication started in log 'FIRST' at position 4
      		 
      database_bugged_slave_setup-master-1  | 2024-04-17  5:33:44 4 [Note] Start binlog_dump to slave_server(2), pos(, 4), using_gtid(0), gtid('')
      		 
      database_bugged_slave_setup-slave-1   | 2024-04-17  5:33:46 5 [ERROR] Slave SQL: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state, Gtid 0-1-7, Internal MariaDB error code: 1399
      		 
      database_bugged_slave_setup-slave-1   | 2024-04-17  5:33:46 5 [Warning] Slave: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state Error_code: 1399
      		 
      database_bugged_slave_setup-slave-1   | 2024-04-17  5:33:46 5 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-mariadb-bin.000002' position 351
      		 
      database_bugged_slave_setup-slave-1   | 2024-04-17  5:33:46 5 [Note] Slave SQL thread exiting, replication stopped in log 'master-mariadb-bin.000002' at position 351
      		 
      database_bugged_slave_setup-slave-1   | 2024-04-17  5:33:46 5 [Note] master was mariadb-master:3306

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25616 Binlog event for XA COMMIT is generated without matching XA START, replication aborts

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30423 Deadlock on Replica during BACKUP STAGE BLOCK_COMMIT on XA transactions

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32020 XA transaction replicates incorrectly, must be applied at XA COMMIT, not XA PREPARE

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Activity

            People

              bnestere Brandon Nesterenko
              kenny.lemay@usherbrooke.ca Kenny lemay
              Votes:
              3 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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