Note that there is an active open issue that is being worked on related to --tc-heuristic-recover=ROLLBACK: MDEV-21117

The issue MDEV-21117 is not relevant with mariabackup as neither BACKUP STAGE or FLUSH TABLES WITH READ LOCK will cause binary log and InnoDB redo log to be inconsistent.

In case of FLUSH TABLES WITH READ LOCK, there can't be any active XA transactions.
In case of BACKUP STAGE BLOCK_COMMIT, all XA transaction may active or prepared but they are never written to the binary log and can thus be rolled back.
In both cases there can be no active internal XA transactions doing a commit as the BLOCK_COMMIT MDL lock will protect against this.
The backup GTID position is from the position before any XA transaction has been written to log and thus correct.

I was merely pointing out that there is work ongoing that changes the behaviour of --tc-heurustic-recover=ROLLBACK. It might of course have no impact on this issue.

Let's consider the following test case in 10.2:

--source include/have_innodb.inc

--source include/not_embedded.inc

--source include/have_binlog_format_row.inc

--disable_warnings

drop table if exists t1, t2;

--enable_warnings

RESET MASTER;

create table t1 (a int) engine=innodb;

xa start 'test1';

insert t1 values (10);

xa end 'test1';

xa prepare 'test1';

--connect (con1,localhost,root,,)

--source include/kill_mysqld.inc

--let $restart_parameters= --tc-heuristic-recover=ROLLBACK

--source include/start_mysqld.inc

--exit

xarecover_handlerton() contains the following code:

      for (int i=0; i < got; i ++)

      {

        my_xid x= WSREP_ON && wsrep_is_wsrep_xid(&info->list[i]) ?

                  wsrep_xid_seqno(info->list[i]) :

                  info->list[i].get_my_xid();

        if (!x) // not "mine" - that is generated by external TM

        {

#ifndef DBUG_OFF

          char buf[XIDDATASIZE*4+6]; // see xid_to_str

          DBUG_PRINT("info", ("ignore xid %s", xid_to_str(buf,

info->list+i)));

#endif

          xid_cache_insert(info->list+i, XA_PREPARED);

          info->found_foreign_xids++;

          continue;

        }

In the above test xid_t::get_my_xid() returns 0 (as the XID does not have MYSQL_XID_PREFIX prefix) for XID pushed in info->list by innodb(innobase_xa_prepare()). As a result, the XID is just pushed in xid_cache, and then XA transaction stays in prepared state independently on tc_heuristic_recover value.

Elkin confirmed this is correct behaviour.

There are two kinds of XA transactions:

1) client XA transactions, which are created explicitly by user with "XA start/end/prepare/commit/rollback" statements,

2) server XA transactions(with MYSQL_XID_PREFIX prefix in XID), which are created implicitly by the server to commit transaction in binary log and storage engine using two phase commit(see also innobase_xa_prepare()):

(gdb) bt

#0  binlog_prepare (hton=0x55555789f630, thd=0x7fff94000d50, all=false) at ./sql/log.cc:1927

#1  0x0000555555d70050 in prepare_or_error (ht=0x55555789f630, thd=0x7fff94000d50, all=false)

    at ./sql/handler.cc:1195

#2  0x0000555555d70903 in ha_commit_trans (thd=0x7fff94000d50, all=false) at ./sql/handler.cc:1474

#3  0x0000555555c5bd7c in trans_commit_stmt (thd=0x7fff94000d50) at ./sql/transaction.cc:510

#4  0x0000555555b0523c in mysql_execute_command (thd=0x7fff94000d50) at ./sql/sql_parse.cc:6042

#5  0x0000555555b09c44 in mysql_parse (thd=0x7fff94000d50, rawbuf=0x7fff94013418 "INSERT INTO t1 VALUES (1)", length=25, 

    parser_state=0x7fffefd1c230, is_com_multi=false, is_next_command=false)

    at ./sql/sql_parse.cc:7740

Client XA transactions are not rolled back or committed on recovery, they stays in prepared state independently on tc_heuristic_recover value until user commits or rolls them back manually. As I understood from our conversation with Elkin, this is intentional and correct behaviour.

So the solution with tc_heuristic_recover will not work. We could, for example, invoke innobase_rollback_by_xid() for prepared client transactions in mariabackup, but I am not sure this is correct behaviour, as there can be other storage engines involved in XA transaction.

When doing a backup, we want to have the state of committed transactions. Anything that is not committed, including XA transactions, should roll back!
In other words, backup state is not same as the state for a killed server.

In the case of the killed server, the intention is that the server will restart and the client will reconnect and issue new XA commands (commit or rollback).
In the case of backup, the client will NEVER reconnect and do this. In this case we have no other option than to rollback the XA transactions and generate a backup that is from the state of before the XA was prepared.

Regards,
Monty

What about the case when backup is used to create slave? Prepared XA's can be committed or rolled back by slave thread. Elkin is working on MDEV-742, where XA PREPARE is going to be written in binary log(and replicated).

The answer from Monty:
This shouldn't happen in backup as BACKUP STAGE could be used to block XA commands for BLOCK COMMIT

The commit https://github.com/MariaDB/server/commit/be85c2dc889b668382106071e712213cd3b1cbcf disables transactions processing on mariabackup --prepare (except --prepare --export to export tablespaces, see also https://mariadb.com/kb/en/library/copying-tables-between-different-mariadb-databases-and-mariadb-servers/, https://mariadb.com/kb/en/library/partial-backup-and-restore-with-mariabackup/).

The comment from marko:
"Why should mariabackup normally care about transaction metadata (undo log pages)? It should only apply log. In xtrabackup there is the option --apply-log-only which I think might not even work in all cases. As long as you start to care about anything higher-level than applying the redo log, there is an increased risk that you will write something to the data files (and necessarily, to the redo log) on your own. If a backup program generates redo log (say, by doing change buffer merge, by rolling back incomplete transactions, by purging old transactions, by updating persistent statistics), it will ‘fork the time’ and lose the ability to correctly receive an incremental backup in the future."

So currently mariabackup does not process transactions on backup restoring to allow incremental backup delta applying.

We could, for example, implement some option, like --prepare-finalize, which would make backup data directory unusable for further incremental backup delta applying.

For what it is worth, I would like to make the mariabackup --prepare step optional, and allow the server to start directly on the backup directory. With the redo log format changes in MDEV-12353 and MDEV-14425, it should be possible to inject the --incremental log to the backed-up log file, instead of writing separate .delta files. I do not think that all this can be implemented before the 10.5 GA release.

julien.fritsch, vlad.lesin: First, it's correct MDEV-742 fixes the issue, a prepared XA can be involved into backup image safely with it. Yet its porting to earlier versions could
be rather problematic (not least 'cos of a new replication event type in there).

I believe that we cannot claim this to work correctly without the fix of MDEV-22398. The server would start up with a too small LSN that it reads from the system tablespace. mariabackup --prepare or mariabackup --copy-back would force the subsequent server startup to read the LSN from there, because there would be no redo log file to start with.

Starting InnoDB with wrong LSN can potentially cause serious corruption of all InnoDB data files.

Before MDEV-22398 is fixed, it is not safe to use the option that was introduced in 10.2, 10.3, 10.4 (not 10.5) by this change:

mariabackup --prepare --rollback-xa …

Just for the note, --apply-log-only was removed in this commit:

commit 8c71c6aa8b9f4c78cfa164fad1d324ba0cf9b888

Author: Marko Mäkelä <marko.makela@mariadb.com>

Date:   Fri Jun 30 10:49:37 2017 +0300

    MDEV-12548 Initial implementation of Mariabackup for MariaDB 10.2

....

    Because MariaDB 10.2 supports indexed virtual columns, the

    undo log processing would need to be able to evaluate virtual column

    expressions. To reduce the amount of code dependencies, we will not

    process any undo log in prepare.

    This means that the --export option must be disabled for now.

    This also means that the following options are redundant

    and have been removed:

            xtrabackup --apply-log-only

            innobackupex --redo-only

...

MDEV-742 did fix the slave side issue that this ticket has in the summary. However --rollback-xa may be needed when
the backup image is planned to be restored on a general non-slave server. That server would not be going to communicate with
the backup donor so can not find out automatically commit-or-rollback decisions for prepared user xa:s.
See MDEV-30794.