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

--history does not work when autocommit is disabled

    Details

    • Type: Bug
    • Status: Pending Release
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 2.4.12
    • Fix Version/s: 2.4.13, 2.3.NEXT
    • Component/s: None
    • Labels:
      None

      Description

      If we have autocommit disabled for the user that takes the backup, then the PERCONA_SCHEMA.xtrabackup_history table will not be updated with the new backup entry, even if --history is used.

       

      How to repeat:

      1- Set bkpuser as mentioned in the documentation (and add ALL on the percona schema): 

      mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'aoeuAOEU123...';
      mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO
             'bkpuser'@'localhost';
      mysql> GRANT ALL ON PERCONA_SCHEMA.* TO 'bkpuser'@'localhost';

      2- Disable autocommit:

      shell> mysql -e "set global init_connect='set autocommit=0'"

      3- Run backup with that user:

      shell> xtrabackup --backup --target-dir=/data/backups/ --history --user=bkpuser --password='aoeuAOEU123...' 2>&1 | grep -i ok
      180722 18:59:20 completed OK!

      4- Check history table:

      shell> mysql -e "select * from PERCONA_SCHEMA.xtrabackup_history"
      ... empty result ...

       

      To check it is indeed working:

      1- Enable autocommit and clear backup directory:

      shell> mysql -e "set global init_connect='set autocommit=1'"
      shell> rm -rf /data/backups/*

      2- Run backup again:

      shell> xtrabackup --backup --target-dir=/data/backups/ --history --user=bkpuser --password='aoeuAOEU123...' 2>&1 | grep -i ok
      180722 19:06:10 completed OK!

      3- Check history table:

      mysql -Be "select * from PERCONA_SCHEMA.xtrabackup_history\G"
      *************************** 1. row ***************************
      uuid: 4ba1e023-8de2-11e8-a6ae-525400cae48b
      name: NULL
      tool_name: xtrabackup
      tool_command: --backup --target-dir=/data/backups/ --history --user=bkpuser --password=...
      tool_version: 2.4.12
      ibbackup_version: 2.4.12
      server_version: 5.7.22-22
      start_time: 2018-07-22 19:06:06
      end_time: 2018-07-22 19:06:10
      lock_time: 0
      binlog_pos: NULL
      innodb_from_lsn: 0
      innodb_to_lsn: 2640076
      partial: N
      incremental: N
      format: file
      compact: N
      compressed: N
      encrypted: N

       

      How to fix:

      Inspect code, and add an explicit commit after inserting the row to the xtrabackup_history table, so it will not depend on the autocommit functionality being enabled.

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                sergei.glushchenko Sergei Glushchenko (Inactive)
                Reporter:
                agustin.gallego Agustín Gallego
              • Votes:
                1 Vote for this issue
                Watchers:
                4 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 - 2 hours
                  2h