xtrabackup_56 does not roll back prepared XA transactions

[Vadim Tkachenko]

I start Percona Server 5.6 on prepared backup and it fails with following:

2013-11-23 03:08:33 18497 [Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: Transaction 459959599 was in the XA prepared state.
InnoDB: Transaction 459959599 was in the XA prepared state.
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 0 row operations to undo
InnoDB: Trx id counter is 459962112
2013-11-23 03:08:33 18497 [Note] InnoDB: 128 rollback segment(s) are active.
InnoDB: Starting in background the rollback of uncommitted transactions
2013-11-23 03:08:33 7ffb28bff700 InnoDB: Rollback of non-prepared transactions completed
2013-11-23 03:08:33 18497 [Note] InnoDB: Waiting for purge to start
2013-11-23 03:08:33 18497 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.14-60.3 started; log sequence number 460967558203
2013-11-23 03:08:33 7ffeb516e780 InnoDB: Starting recovery for XA transactions...
2013-11-23 03:08:33 7ffeb516e780 InnoDB: Transaction 459959599 in prepared state after recovery
2013-11-23 03:08:33 7ffeb516e780 InnoDB: Transaction contains changes to 2 rows
2013-11-23 03:08:33 7ffeb516e780 InnoDB: 1 transactions in prepared state after recovery
2013-11-23 03:08:33 18497 [Note] Found 1 prepared transaction(s) in InnoDB
2013-11-23 03:08:33 18497 [ERROR] Found 1 prepared transactions! It means that mysqld was not shut down properly last time and critical recovery information (last binlog or tc.log file) was manually deleted after a crash. You have to start mysqld with --tc-heuristic-recover switch to commit or rollback pending transactions.
2013-11-23 03:08:33 18497 [ERROR] Aborting

[Vadim Tkachenko (vadim-tk)]

In addition I am not sure about binary log postion
cat xtrabackup_binlog_info
mysql-bin.000810 882347277

cat xtrabackup_binlog_pos_innodb
mysql-bin.000810 882317925

[Vadim Tkachenko (vadim-tk)]

I was able to start mysqld with --tc-heuristic-recover=ROLLBACK option.

But it seems the correct position was
in xtrabackup_binlog_info,

because I started as the slave with
mysql-bin.000810 882317925
and slave failed with DUPLICATE KEY error on INSERT statement.
so I had to skip 1 statement.

[Alexey Kopytov (akopytov)]

I think it is a regression introduced when porting XtraBackup patches to 5.6. Patches against lower server versions contain the following changes in trx_lists_init_at_db_start():

--- a/storage/innobase/trx/trx0trx.c
+++ b/storage/innobase/trx/trx0trx.c
@@ -522,9 +522,8 @@
       (ullint) trx->id);

      if (srv_force_recovery == 0) {
-
- trx->conc_state = TRX_PREPARED;
- trx_n_prepared++;
+ /* xtrabackup should rollback it */
+ trx->conc_state = TRX_ACTIVE;
      } else {
       fprintf(stderr,
        "InnoDB: Since"

I.e. prepared XA transactions are converted to active and thus, rolled back.

However, I don't see those changes in https://bazaar.launchpad.net/~laurynas-biveinis/percona-xtrabackup/BT-28340-2.0/revision/506

[Alexey Kopytov (akopytov)]

Not sure if the binlog issue is related or a separate one. Please report separately if still reproducible when this bug is fixed.

[Laurynas Biveinis (laurynas-biveinis)]

Yes, it looks like a port regression. That missing patch bit was one of the bits that were not exercised by the testsuite at the time. Most of them were applied nevertheless, but this one fell through the cracks. Bug 1119451 could be related.

[Alexey Kopytov (akopytov)]

OK, this bug is not as serious as it seems. The original report doesn't mention it, but I'm fairly sure the problem can only be observed on the server with TokuDB patches applied. More specifically, it is triggered by the fix for bug http://bugs.mysql.com/bug.php?id=47134. So it must be either MariaDB 5.5+, or MySQL 5.7 or (not released yet) Percona Server 5.6.15-64.0.

Without the fix for http://bugs.mysql.com/bug.php?id=47134 (and with a single XA storage engine), the server is able to auto-recover from prepared XA transactions even with the default (i.e. empty) value of --tc-heuristic-recover.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-675