MySQL GTID metadata not synced upon full SST

[Przemek]

Tested with Server version: 5.6.35-81.0-56-log Percona XtraDB Cluster (GPL), Release rel81.0, Revision 7f9b6ae, WSREP version 26.20, wsrep_26.20

When new node is added to a cluster, where all nodes are GTID enabled, after successful full SST, the joiner has unset initial GTID position.

Test case:
* bootstrap first node with settings:
server-id=1
enforce_gtid_consistency=1
gtid_mode=on
log_slave_updates
log-bin=percona1-bin

* execute some writes
* start second node with same settings
* example result:
percona1 mysql> show global variables like 'gtid%';
+----------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------+--------------------------------------------+
| gtid_deployment_step | OFF |
| gtid_executed | 102cd5f1-0628-ee19-4d29-e8233b126f5f:1-317 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+----------------------+--------------------------------------------+
5 rows in set (0.00 sec)

percona2 mysql> show global variables like 'gtid%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| gtid_deployment_step | OFF |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+----------------------+-------+
5 rows in set (0.03 sec)

* execute more writes, and see out of sync positions:
percona1 mysql> show global variables like 'gtid_e%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| gtid_executed | 102cd5f1-0628-ee19-4d29-e8233b126f5f:1-448 |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

percona2 mysql> show global variables like 'gtid_e%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| gtid_executed | 102cd5f1-0628-ee19-4d29-e8233b126f5f:1-131 |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

Quick test on PXC 5.7.16 worked well though, GTID was synced properly.

[Kenn Takara (kenn-takara)]

Hmmm.. everything works ok for me with 5.6.35-26.20 and 5.6.35-26.20.v3 (this is the one that is being used above).

Do you have the logs for the second node?

[Przemek (pmalkowski)]

percona2 mysql> select @@version,@@version_comment\G
*************************** 1. row ***************************
        @@version: 5.6.35-81.0-56-log
@@version_comment: Percona XtraDB Cluster (GPL), Release rel81.0, Revision 7f9b6ae, WSREP version 26.20, wsrep_26.20
1 row in set (0.00 sec)

I commented out all sst related options (earlier had compression enabled), and I can still reproduce at will. Here is my full config, almost same on donor and joiner:

[mysqld]
datadir=/var/lib/mysql
skip_name_resolve
user=mysql
log_error=percona2_error.log
binlog_format=ROW
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.3.2,192.168.3.3,192.168.3.4
wsrep_node_address=192.168.3.3
wsrep_slave_threads=2
wsrep_cluster_name=L1
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=root:***
wsrep_node_name=percona2
innodb_flush_log_at_trx_commit = 0
innodb_autoinc_lock_mode=2
innodb_log_file_size=512M
bind-address=192.168.3.3
log_slave_updates

server-id=22
#support GTID
enforce_gtid_consistency=1
gtid_mode=on

log-bin=percona2-bin
max_allowed_packet = 64M

Even though GTID info is stored from the SST here:

[root@percona2 ~]# cat /var/lib/mysql/xtrabackup_info
uuid = 1ac205e3-3be7-11e7-830f-0800272e951c
name =
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --defaults-group=mysqld --no-version-check --user=root --password=... --socket=/var/lib/mysql/mysql.sock --galera-info --stream=xbstream /tmp/pxc_sst_aPnM7mim/donor_xb_yLk4jW4Q
tool_version = 2.3.8
ibbackup_version = 2.3.8
server_version = 5.6.35-81.0-56-log
start_time = 2017-05-18 18:28:15
end_time = 2017-05-18 18:29:03
lock_time = 0
binlog_pos = filename 'percona1-bin.000006', position '191', GTID of the last change '102cd5f1-0628-ee19-4d29-e8233b126f5f:1-3020'
innodb_from_lsn = 0
innodb_to_lsn = 649078051
partial = N
incremental = N
format = xbstream
compact = N
compressed = N
encrypted = N

The GTID global variable is *empty* on the joiner.

Joiner and donor logs in attachment.

[Przemek (pmalkowski)]

btw, on both:
# xtrabackup --version
xtrabackup version 2.3.8 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 9328211)

[Kenn Takara (kenn-takara)]

Ok, I see, the problem is that the names for the binlog are different. The SST script expects the name to be the same.

log-bin=percona

(for both donor and joiner).

[Kenn Takara (kenn-takara)]

Possible workaround: ensure that the log-bin is the same for the donor/joiner (or ensure that the actual binlog files have the same name on the donor/joiner).

Note that the default is to use <hostname>-bin, however it might be better to be explicit 'log-bin=<filename>'

[Sveta Smirnova (svetasmirnova)]

Progress logged in Jira bug: https://jira.percona.com/browse/PXC-827

[Shahriyar Rzayev (rzayev-sehriyar)]

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