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

Incremental backup prepare with apply-log-only rolls back uncommitted transactions

Details

    • Bug
    • Status: Done
    • High
    • Resolution: Fixed
    • 8.0-2-alpha2
    • 8.0-3-rc1
    • None
    • None
    • CentOS7.5

    Description

      1. Start PS8.0.12 server
      2. Create database and add data in it

      create database sysbench_test_db;
      sysbench /usr/share/sysbench/oltp_insert.lua --table-size=10000 --tables=35 --mysql-db=sysbench_test_db --mysql-user=root --threads=100 --db-driver=mysql --mysql-socket=/home/mchawla/PS240918-percona-server-8.0.12-1-linux-x86_64-debug/socket.sock prepare
      create table sysbench_test_db.t1(c varchar(255)) data directory='/home/mchawla/test_mode/PS240918-percona-server-8.0.12-1-linux-x86_64-debug';
      insert into sysbench_test_db.t1 select c from sysbench_test_db.sbtest1;

      3. Take full backup

      ./xtrabackup --user=root --password='' --no-version-check --backup --target-dir=$HOME/dbbackup_PS8/full -S $HOME/PS240918-percona-server-8.0.12-1-linux-x86_64-debug/socket.sock --datadir=$HOME/PS240918-percona-server-8.0.12-1-linux-x86_64-debug/data --transition-key=top-secret

      4. Start a large transaction which is uncommitted:

      START TRANSACTION; DELETE FROM sbtest9 LIMIT 1000; DELETE FROM sbtest10 LIMIT 1000; SELECT SLEEP(200000);

      5. Take incremental backup

      ./xtrabackup --user=root --password='' --no-version-check --backup --target-dir=$HOME/dbbackup_PS8/inc1 --incremental-basedir=$HOME/dbbackup_PS8/full -S $HOME/PS240918-percona-server-8.0.12-1-linux-x86_64-debug/socket.sock --transition-key=top-secret

      6. Prepare full backup

      ./xtrabackup --no-version-check --prepare --apply-log-only --target-dir=/home/mchawla/dbbackup_PS8/full --transition-key=top-secret

      7. Prepare incremental backup using apply-log-only

      ./xtrabackup --no-version-check --prepare --apply-log-only --incremental-dir=$HOME/dbbackup_PS8/inc1 --target-dir=/home/mchawla/dbbackup_PS8/full --transition-key=top-secret

      Issue: The prepare for incremental backup with apply-log-only rolls back uncommitted transactions. It should not roll back the uncommitted transactions.

      Log:

      InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 2000 row operations to undo
      InnoDB: Trx id counter is 3328
      InnoDB: Removed temporary tablespace data file: "ibtmp1"
      InnoDB: Creating shared tablespace for temporary tables
      InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
      InnoDB: File './ibtmp1' size is now 12 MB.
      InnoDB: Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
      InnoDB: 8.0.11 started; log sequence number 114478135
      Populating InnoDB table cache.
      InnoDB: Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
      InnoDB: Starting in background the rollback of uncommitted transactions
      InnoDB: Rolling back trx with id 2918, 2000 rows to undo
      InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12InnoDB: xtrabackup: Last MySQL binlog file position 68074810, file name binlog.000002
      13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100InnoDB: Rollback of trx with id 2918 completed
      InnoDB: Rollback of non-prepared transactions completed
      

      Full logs attached: prepare incremental backup.txt

      Attachments

        Activity

          People

            sergei.glushchenko Sergei Glushchenko (Inactive)
            manish.chawla Manish Chawla
            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 day, 4 hours
                1d 4h

                Smart Checklist