Uploaded image for project: 'Percona XtraBackup'
  1. Percona XtraBackup
  2. PXB-1770

Issues with prepared XA transactions

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 8.0.4
    • Fix Version/s: 8.0.5
    • Component/s: None
    • Labels:
      None

      Description

      Test case:

      start_server
      
      mysql <<EOF
      INSTALL PLUGIN ROCKSDB SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_CFSTATS SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_DBSTATS SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_PERF_CONTEXT SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_PERF_CONTEXT_GLOBAL SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_CF_OPTIONS SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_GLOBAL_INFO SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_COMPACTION_STATS SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_DDL SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_INDEX_FILE_MAP SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_LOCKS SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_TRX SONAME 'ha_rocksdb.so';
      INSTALL PLUGIN ROCKSDB_DEADLOCK SONAME 'ha_rocksdb.so';
      EOF
      
      mysql -e 'CREATE TABLE t (a INT AUTO_INCREMENT PRIMARY KEY, b INT, s VARCHAR(100), KEY(b))' test
      
      ( while true ; do
        echo "INSERT INTO t (b, s) VALUES (FLOOR(1 + RAND() * 10000), UUID());"
      done | mysql test ) &
      
      ( while true ; do
        echo "INSERT INTO t (b, s) VALUES (FLOOR(1 + RAND() * 10000), UUID());"
      done | mysql test ) &
      
      ( while true ; do
        echo "INSERT INTO t (b, s) VALUES (FLOOR(1 + RAND() * 10000), UUID());"
      done | mysql test ) &
      
      xtrabackup --backup --target-dir=$topdir/backup
      xtrabackup --prepare --target-dir=$topdir/backup
      
      stop_server
      
      rm -rf $mysql_datadir
      
      xtrabackup --copy-back --target-dir=$topdir/backup
      
      start_server
      
      mysql -e 'SELECT COUNT(*) FROM t' test
      
      cat ${MYSQLD_ERRFILE}
      
       

       

      often fails due to MySQL unable to start with following error:

      2019-01-27T21:06:43.106886Z 0 [Note] [MY-013032] [InnoDB] Starting recovery for XA transactions...                                                                                                 
      2019-01-27T21:06:43.106942Z 0 [Note] [MY-013033] [InnoDB] Transaction 7914 in prepared state after recovery
      2019-01-27T21:06:43.106955Z 0 [Note] [MY-013034] [InnoDB] Transaction contains changes to 1 rows                                                                                                   
      2019-01-27T21:06:43.106966Z 0 [Note] [MY-013035] [InnoDB] 1 transactions in prepared state after recovery                                                                                          
      2019-01-27T21:06:43.106976Z 0 [Note] [MY-010224] [Server] Found 1 prepared transaction(s) in InnoDB                                                                                                
      2019-01-27T21:06:43.108519Z 0 [ERROR] [MY-010231] [Server] Found 1 prepared transactions! It means that mysqld was not shut down properly last time and critical recovery information (last binlog o
      r 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. 
      

       

      Note that RocksDB engine needs to be enabled, otherwise MySQL starts successfully.

      Test bug1254227.sh doesn't fail, mysql rolls back XA transaction on startup.

      xtrabackup is missing following patch which used to rollback XA transactions in previous versions:

      diff --git i/storage/innobase/trx/trx0trx.cc w/storage/innobase/trx/trx0trx.cc
      index 11f68782365..96fc50163ad 100644
      --- i/storage/innobase/trx/trx0trx.cc
      +++ w/storage/innobase/trx/trx0trx.cc
      @@ -788,8 +788,9 @@ static trx_t *trx_resurrect_insert(
                                      << " was in the XA prepared state.";
      
             if (srv_force_recovery == 0) {
      -        trx->state = TRX_STATE_PREPARED;
      -        ++trx_sys->n_prepared_trx;
      +        /* XtraBackup should rollback prepared XA
      +        transactions */
      +        trx->state = TRX_STATE_ACTIVE;
             } else {
               ib::info(ER_IB_MSG_1205) << "Since innodb_force_recovery"
                                           " > 0, we will force a rollback.";
      @@ -852,12 +853,14 @@ static void trx_resurrect_update_in_prepared_state(
      
           ut_ad(trx->state != TRX_STATE_FORCED_ROLLBACK);
      
      -    if (trx_state_eq(trx, TRX_STATE_NOT_STARTED)) {
      -      ++trx_sys->n_prepared_trx;
      -    } else {
      +    if (!trx_state_eq(trx, TRX_STATE_NOT_STARTED)) {
             ut_ad(trx_state_eq(trx, TRX_STATE_PREPARED));
           }
      
      +    /* XtraBackup should rollback prepared XA
      +    transactions */
      +    trx->state = TRX_STATE_ACTIVE;
      +
           trx->state = TRX_STATE_PREPARED;
         } else {
           trx->state = TRX_STATE_COMMITTED_IN_MEMORY;
      
      

       

      If we put that patch back everything becomes OK with InnoDB. This however needs to be tested with transactions involving both RocksDB and InnoDB.

      Also, considering out approach to RocksDB backup we won't be able to rollback prepared XA transactions in RocksDB. Workaround I have found for rocksdb is to copy MySQL binary log to the backup directory.

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  sergei.glushchenko Sergei Glushchenko
                  Reporter:
                  sergei.glushchenko Sergei Glushchenko
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 1 week, 2 days, 1 hour, 45 minutes
                    1w 2d 1h 45m