--history can not insert table xtrabackup_history

[supersujj]

my environment is mysql 5.7.18 and build a mysql group replication. when i use xtrabackup with --history option,it can create database "percona_schema" and table "xtrabackup_history", but when i finish backup successful, i select percona_schema.xtrabackup_history is empty, and i read binlog it have not record.

my command like this :
innobackupex --login-path=$login_path --backup --history --encrypt=AES256 --encrypt-key-file=$dir/$keyfile --host=$host --port=$port --parallel=8 --throttle=1000 --no-timestamp --stream=xbstream $dir 2> $dir/backup.log| pigz -7 -p 8 > $dir/xbstream_`date +%Y%m%d%H%M`.tgz

[Shahriyar Rzayev (rzayev-sehriyar)]

Hi,
What is your xtrabackup version?

I use the same PS 5.7.18 with ordinary master/slave setup + PXB 2.4.8 and everything looks OK.

xtrabackup --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_18/master/my.sandbox.cnf --user=msandbox --password='msandbox' --target-dir=/home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-08-01_07-17-15 --backup --host=127.0.0.1 --port=20393 --encrypt=AES256 --encrypt-key='VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K' --encrypt-threads=4 --encrypt-chunk-size=65536 --check-privileges --no-version-check --parallel=8 --throttle=1000 --history --stream="xbstream" > /home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-08-01_07-17-15/full_backup.stream

master [localhost] {root} (PERCONA_SCHEMA) > show tables;
+--------------------------+
| Tables_in_PERCONA_SCHEMA |
+--------------------------+
| xtrabackup_history |
+--------------------------+
1 row in set (0.00 sec)

> select * from xtrabackup_history\G
*************************** 1. row ***************************
            uuid: 830b4057-7689-11e7-ac73-002590e9b448
            name: NULL
       tool_name: xtrabackup
    tool_command: --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_18/master/my.sandbox.cnf --user=msandbox --password=... --target-dir=/home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-08-01_07-17-15 --backup --host=127.0.0.1 --port=20393 --encrypt=AES256 --encrypt-key=... --encrypt-threads=4 --encrypt-chunk-size=65536 --check-privileges --no-version-check --parallel=8 --throttle=1000 --history --stream=xbstream
    tool_version: 2.4.7
ibbackup_version: 2.4.7
  server_version: 5.7.18-15-log
      start_time: 2017-08-01 07:17:15
        end_time: 2017-08-01 07:17:43
       lock_time: 0
      binlog_pos: filename 'mysql-bin.000005', position '154', GTID of the last change '00020393-1111-1111-1111-111111111111:1-1511'
 innodb_from_lsn: 0
   innodb_to_lsn: 2892770773
         partial: N
     incremental: N
          format: xbstream
         compact: N
      compressed: N
       encrypted: Y
1 row in set (0.00 sec)

RESULT: could not reproduce. Marking as invalid for now.

As a general rule, I am not suggesting to use innobackupex because it is already deprecated and it is going to be removed in future releases. Please use xtrabackup instead.
As described here:
https://www.percona.com/doc/percona-xtrabackup/LATEST/manual.html

[supersujj (supersujj)]

can you test when master/slave Switching occurs，and i found when SELECT @@global.gtid_executed; have not only one record like ：
SELECT @@global.gtid_executed;
+---------------------------------------------------------------------------------------------------+
| @@global.gtid_executed |
+---------------------------------------------------------------------------------------------------+
| 12361081-90f4-11e6-8413-0050569293fc:1-286495163,
59eaee0c-9289-11e5-8911-005056923a57:1-40235250 |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

when i use --history ,it can not insert record,but when table no exist,it will create. and i see the binlog have not some insert record.

if i reset the gtid_executed when i have only one records like:
SELECT @@global.gtid_executed;
+---------------------------------------------------------------------------------------------------+
| @@global.gtid_executed |
+---------------------------------------------------------------------------------------------------+
| 12361081-90f4-11e6-8413-0050569293fc:1-234 |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

it can insert the records.

i try to use 2.3.7,2.3.8 version and the results is the same.

 xtrabackup have not --history, is it have any other way can insert the backup record into database?

[Shahriyar Rzayev (rzayev-sehriyar)]

I did not understand your point about master/slave switch.
This time I took backup with 2.3.9 on PS 5.6 master server with --history option:

xtrabackup --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_6_36/master/my.sandbox.cnf --history --user=msandbox --password='msandbox' --target-dir=/home/shahriyar.rzaev/backup_dir/ps_5.6_master/full/2017-08-02_08-41-18 --backup --host=127.0.0.1 --port=23901 --encrypt=AES256 --encrypt-key='VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K' --encrypt-threads=4 --encrypt-chunk-size=65536 --stream="xbstream" > /home/shahriyar.rzaev/backup_dir/ps_5.6_master/full/2017-08-02_08-41-18/full_backup.stream

And I did it twice:

> select count(*) from xtrabackup_history;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

So the history is there.

Still could not reproduce.

[supersujj (supersujj)]

thank you for support me. I means is if "SELECT @@global.gtid_executed;" have not only one values,it like this have the other server_uuid after switch,it can not insert the records with --history
SELECT @@global.gtid_executed;
+---------------------------------------------------------------------------------------------------+
| @@global.gtid_executed |
+---------------------------------------------------------------------------------------------------+
| 12361081-90f4-11e6-8413-0050569293fc:1-286495163,
59eaee0c-9289-11e5-8911-005056923a57:1-40235250 |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

12361081-90f4-11e6-8413-0050569293fc is master,59eaee0c-9289-11e5-8911-005056923a57 is slave.

[Shahriyar Rzayev (rzayev-sehriyar)]

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

[Kapil (kapilsingla)]

Hi, Is there any update on this issue?

I experienced the same issue on MySQL 5.7.22 (Oracle - community edition), while having the master-master setup. Versions:

root@server:~# mysql --version
mysql Ver 14.14 Distrib 5.7.22, for linux-glibc2.12 (x86_64) using EditLine wrapper

root@server:~# xtrabackup --version
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)

Query used:
$XTRABACKUP --defaults-file=$DEFAULTS_FILE --login-path=xtrab --compress --compress-threads=1 --encrypt-threads=1 --parallel=2 --target-dir="$BACKUP_DIR/$DATE" --encrypt=AES256 --encrypt-key-file=$encryption_key_file --backup --history 2> "$BACKUP_LOG/$DATE/Xtra-backup-progress.log"

What happens:
1) It creates the schema PERCONA_SCHEMA
2) It creates an empty table with no records.
3) After running the xtrabackup (full backup), it does not create any entry into the table.

mysql> use PERCONA_SCHEMA
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------------+
| Tables_in_PERCONA_SCHEMA |
+--------------------------+
| xtrabackup_history |
+--------------------------+
1 row in set (0.00 sec)

mysql> select uuid, start_time, end_time, incremental, compact, compressed, encrypted from PERCONA_SCHEMA.xtrabackup_history order by start_time desc;
Empty set (0.00 sec)