Gtid_purged and Gtid_executed values not cleared when backup is taken from an instance with running transactions

[Jericho Rivera]

[root@centos5 ~]# mysql -e 'show global variables like "gtid_%"';
+----------------------------------+----------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------+
| gtid_executed | 8eafa203-2d4d-11e6-8324-00163e0d9b9e:1-28158 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+----------------------------------+----------------------------------------------+
[root@centos5 ~]# innobackupex --user=root /var/lib/backups/ 2> ~/backup.log
[root@centos5 ~]# service mysql stop
Stopping mysqld: [ OK ]
[root@centos5 ~]# rm -rf /var/lib/mysql/*
[root@centos5 ~]# innobackupex --apply-log /var/lib/backups/2016-06-08_03-59-53/ 2> ~/prepare.log
[root@centos5 ~]# innobackupex --copy-back /var/lib/backups/2016-06-08_03-59-53/ 2> ~/copyback.log
[root@centos5 ~]# chown -R mysql:mysql /var/lib/mysql
[root@centos5 ~]# tail -1 backup.log
160608 03:59:55 completed OK!
[root@centos5 ~]# tail -1 prepare.log
160608 04:02:12 completed OK!
[root@centos5 ~]# tail -1 copyback.log
160608 04:02:39 completed OK!
[root@centos5 ~]# service mysql start
Starting mysqld: [ OK ]
[root@centos5 ~]# mysql -e 'show global variables like "gtid_%"';
+----------------------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------------+
| gtid_executed | 8eafa203-2d4d-11e6-8324-00163e0d9b9e:1-134 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 8eafa203-2d4d-11e6-8324-00163e0d9b9e:1-134 |
+----------------------------------+--------------------------------------------+
[root@centos5 ~]# mysql -e 'select @@version, @@version_comment'
+--------------+---------------------------------------------------+
| @@version | @@version_comment |
+--------------+---------------------------------------------------+
| 5.7.11-4-log | Percona Server (GPL), Release 4, Revision 5c940e1 |
+--------------+---------------------------------------------------+

On an instance where no transactions are running backup and restore will result in an empty gtid_purged and gtid_executed variables.

How to test:
Install MySQL 5.7 and PXB 2.4.3
Run OLTP sysbench test
Take a backup while sysbench is running.

Why this matters? Normal expected behavior is gtid_executed and gtid_purged should be empty upon restore and user will just have to follow instructions in Step 4 in https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/recipes_ibkx_gtid.html. With this bug, user will have to execute 'reset master' before.

[Jericho Rivera (jericho-rivera)]

PXB 2.3.4 on PS 5.6.30 does not show this problem:

[root@centos1 ~]# mysql -e 'show global variables like "gtid_%"'
+----------------------+---------------------------------------------+
| Variable_name | Value |
+----------------------+---------------------------------------------+
| gtid_deployment_step | OFF |
| gtid_executed | 995255ef-2d55-11e6-ba79-feef2c35fbdc:1-6511 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+----------------------+---------------------------------------------+
[root@centos1 ~]# innobackupex --user=root --host=127.0.0.1 --port=3306 /var/lib/backups/ 2> backup.log
[root@centos1 ~]# innobackupex --apply-log /var/lib/backups/2016-06-08_04-49-35/ 2> prepare.log
[root@centos1 ~]# service mysql stop
Shutting down MySQL (Percona Server).... SUCCESS!
[root@centos1 ~]# rm -rf /var/lib/mysql/*
[root@centos1 ~]# innobackupex --copy-back /var/lib/backups/2016-06-08_04-49-35/ 2> copyback.log
[root@centos1 ~]# chown -R mysql:mysql /var/lib/mysql
[root@centos1 ~]# service mysql start
Starting MySQL (Percona Server). SUCCESS!
[root@centos1 ~]# mysql -e 'show global variables like "gtid_%"'
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| gtid_deployment_step | OFF |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+----------------------+-------+

[Michael Wang (xw73)]

I have the same issue on a quiet server without activity during the backup.

[Sergei Glushchenko (sergei.glushchenko)]

I think the difference between 5.6 and 5.7 is that 5.7 persists GTIDs in the mysql.gtid_executed table, while 5.6 can only get them from binary logs, which are not copied.

https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#replication-gtids-gtid-executed-table

[Jericho Rivera (jericho-rivera)]

Just as an update to this bug report. I can't seem to reproduce this on 5.7.12

@Michael, can you test with 5.7.12?

[Michael Wang (xw73)]

I have tested on 5.7.12 and the result is the same. If the variable is saved in a table as shown below, then it has to be there after the restore with or without transaction, and 5.7.12 has not changed the behavior.

mysql> select @@version;
+--------------+
| @@version |
+--------------+
| 5.7.12-5-log |
+--------------+
1 row in set (0.00 sec)

mysql> show global variables like 'gtid_executed';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| gtid_executed | ae4a529a-c855-11e5-a7df-080027feff75:1-3 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| ae4a529a-c855-11e5-a7df-080027feff75 | 1 | 3 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)

[Jericho Rivera (jericho-rivera)]

Confirmed reproducible on 5.7.x and PXB 2.4.3 whether on an idle or active server.

Source:
mysql> select * from mysql.gtid_executed; [11/1956]
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 3f48abf3-437e-11e6-8191-00163e0d9b9e | 1 | 6028 |
| 3f48abf3-437e-11e6-8191-00163e0d9b9e | 6029 | 12201 |
+--------------------------------------+----------------+--------------+
2 rows in set (0.00 sec)

mysql> show global variables like 'gtid_%';
+----------------------------------+----------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------+
| gtid_executed | 3f48abf3-437e-11e6-8191-00163e0d9b9e:1-22021 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+----------------------------------+----------------------------------------------+
5 rows in set (0.00 sec)

Destination:
mysql> show global variables like 'gtid_%'; [14/1806]
+----------------------------------+----------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------+
| gtid_executed | 3f48abf3-437e-11e6-8191-00163e0d9b9e:1-12201 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 3f48abf3-437e-11e6-8191-00163e0d9b9e:1-12201 |
+----------------------------------+----------------------------------------------+
5 rows in set (0.00 sec)

mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 3f48abf3-437e-11e6-8191-00163e0d9b9e | 1 | 6028 |
| 3f48abf3-437e-11e6-8191-00163e0d9b9e | 6029 | 12201 |
+--------------------------------------+----------------+--------------+
2 rows in set (0.00 sec)

As mentioned before, a RESET MASTER is needed to reset the GTID settings before being able to setup replication no the slave which adds a step to the procedure.

[Sergei Glushchenko (sergei.glushchenko)]

Jericho,

Do you think it will it be OK if we just update our docs to mention the need for "RESET MASTER" for 2.4 + 5.7?

[Jericho Rivera (jericho-rivera)]

Sergei,

That should be fine, re: update docs to include RESET MASTER for PXB 2.4 + 5.7.

[Andrew Garner (muzazzi)]

mysql.gtid_executed is only updated after FLUSH BINARY LOGS or after gtid_executed_compression_period transactions. It seems that if FLUSH BINARY LOGS were run under a lock (that I think xtrabackup already acquiring for GTIDs), mysql.gtid_executed should be consistent on the slave.

It seems that if xtrabackup were to run FLUSH BINARY LOGS in the gtid case, we could have a much simpler slave setup - only needing to flag MASTER_AUTO_POSITION = 1 and rely on mysql.gtid_executed being accurate.

Currently the RESET MASTER + parsing out the right bits from xtrabackup metadata is a little painful.

[Shahriyar Rzayev (rzayev-sehriyar)]

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