MySQL GTID: gtid_executed split brain after SST transfer

General

Escalation

General

Escalation

Description

Tested with Server version: Server version: 5.7.19-17-57-log Percona XtraDB Cluster (GPL), Release rel17, Revision 35cdc81, WSREP version 29.22, wsrep_29.22

When I deploying PXC Cluster, The new node is added to a cluster, where all nodes are GTID enabled.

I make sure all node wsrep_local_state_comment is SYNCED. And I got same gtid_executed in all node.

pxc-1

mysql> show global variables like '%gtid%';
----------------------------------------------------------------------------+

Variable_name

Value

----------------------------------------------------------------------------+

binlog_gtid_simple_recovery	ON
enforce_gtid_consistency	ON
gtid_executed	db3242e0-0aec-ee18-551c-04f5dfbea3f9:1-6
gtid_executed_compression_period	1000
gtid_mode	ON
gtid_owned
gtid_purged
session_track_gtids	OFF

----------------------------------------------------------------------------+
8 rows in set (0.01 sec)

pxc-2

mysql> show global variables like '%gtid%';
----------------------------------------------------------------------------+

Variable_name

Value

----------------------------------------------------------------------------+

binlog_gtid_simple_recovery	ON
enforce_gtid_consistency	ON
gtid_executed	db3242e0-0aec-ee18-551c-04f5dfbea3f9:1-6
gtid_executed_compression_period	1000
gtid_mode	ON
gtid_owned
gtid_purged	db3242e0-0aec-ee18-551c-04f5dfbea3f9:1-3
session_track_gtids	OFF

----------------------------------------------------------------------------+
8 rows in set (0.02 sec)

pxc-3

mysql> show global variables like '%gtid%';
----------------------------------------------------------------------------+

Variable_name

Value

----------------------------------------------------------------------------+

binlog_gtid_simple_recovery	ON
enforce_gtid_consistency	ON
gtid_executed	db3242e0-0aec-ee18-551c-04f5dfbea3f9:1-6
gtid_executed_compression_period	1000
gtid_mode	ON
gtid_owned
gtid_purged	db3242e0-0aec-ee18-551c-04f5dfbea3f9:1-3
session_track_gtids	OFF

----------------------------------------------------------------------------+
8 rows in set (0.01 sec)

Then I test emptied one of the datadir nodes and triggered SST synchronization

pxc-3 operator steps

/etc/init.d/mysql stop
rm -fr /var/lib/mysql/*
# /etc/init.d/mysql stop
Check wsrep_local_state_comment is SYNCED

Here is Problem. I got two GTID variables.

pxc-3

mysql> show global variables like '%gtid%';
--------------------------------------------------------------------------------------------------------------------+

Variable_name

Value

--------------------------------------------------------------------------------------------------------------------+

binlog_gtid_simple_recovery	ON
enforce_gtid_consistency	ON
gtid_executed	ae3ee703-f517-11e7-88e7-fa163e6aa620:1, db3242e0-0aec-ee18-551c-04f5dfbea3f9:1-6
gtid_executed_compression_period	1000
gtid_mode	ON
gtid_owned
gtid_purged	db3242e0-0aec-ee18-551c-04f5dfbea3f9:1-6
session_track_gtids	OFF

--------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.02 sec)

The new GTID variables made my replication unable to keep testing with these error message.

Replication ERROR Message

mysql> SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.3
Master_User: sstuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db-bin.000009
Read_Master_Log_Pos: 234
略
Last_Errno: 1524
Last_Error: Error 'Plugin 'auth_socket' is not loaded' on query. Default database: 'mysql'. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket''
略
Last_SQL_Errno: 1524
Last_SQL_Error: Error 'Plugin 'auth_socket' is not loaded' on query. Default database: 'mysql'. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket''
略
Master_UUID: ed591402-f50b-11e7-9a2a-fa163e6aa620
Retrieved_Gtid_Set: ae3ee703-f517-11e7-88e7-fa163e6aa620:1 (主要是這邊不應該接值，正常情況欄位為空)
Executed_Gtid_Set: db3242e0-0aec-ee18-551c-04f5dfbea3f9:1-6
略
1 row in set (0.00 sec)

The following is the modified parameters according to the default settings:

pxc-1: /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf

server-id=101
log_slave_updates
enforce_gtid_consistency=1
gtid_mode=on
log-bin="db-bin."

pxc-1: /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf

wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3

pxc-2: /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf

server-id=102
log_slave_updates
enforce_gtid_consistency=1
gtid_mode=on
log-bin="db-bin."

pxc-2: /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf

wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3

pxc-3: /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf

server-id=103
log_slave_updates
enforce_gtid_consistency=1
gtid_mode=on
log-bin="db-bin."

pxc-3: /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf

wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3

Please help confirm this problem.

Thanks.

Environment

pxc-1:

root@pxc-1:~# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.3 LTS
Release: 16.04
Codename: xenial

Percona Package Version

root@pxc-1:~# dpkg -l | grep percona | awk '{print $2"|"$3}'
percona-release|0.1-4.xenial
percona-xtrabackup-24|2.4.9-1.xenial
percona-xtradb-cluster-client-5.7|5.7.19-29.22-3.xenial
percona-xtradb-cluster-common-5.7|5.7.19-29.22-3.xenial
percona-xtradb-cluster-server-5.7|5.7.19-29.22-3.xenial

pxc-2:

root@pxc-2:~# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.3 LTS
Release: 16.04
Codename: xenial

Percona Package Version

root@pxc-2:~# dpkg -l | grep percona | awk '{print $2"|"$3}'
percona-release|0.1-4.xenial
percona-xtrabackup-24|2.4.9-1.xenial
percona-xtradb-cluster-client-5.7|5.7.19-29.22-3.xenial
percona-xtradb-cluster-common-5.7|5.7.19-29.22-3.xenial
percona-xtradb-cluster-server-5.7|5.7.19-29.22-3.xenial

pxc-3:

root@pxc-3:~# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.3 LTS
Release: 16.04
Codename: xenial

Percona Package Version

root@pxc-3:~# dpkg -l | grep percona | awk '{print $2"|"$3}'
percona-release|0.1-4.xenial
percona-xtrabackup-24|2.4.9-1.xenial
percona-xtradb-cluster-client-5.7|5.7.19-29.22-3.xenial
percona-xtradb-cluster-common-5.7|5.7.19-29.22-3.xenial
percona-xtradb-cluster-server-5.7|5.7.19-29.22-3.xenial

Attachments

10 Jan 2018, 09:05 AM

Smart Checklist

Activity

Show:

Lalit Choudhary November 13, 2018 at 11:52 AM

@Wu Nan Lin Thank you for the update.

Wu Nan Lin June 4, 2018 at 5:05 PM

Can not reproduce this issue with new version.
Please close this issue.
Thanks.

Wu Nan Lin January 24, 2018 at 10:04 AM

Hello Krunal Bauskar,

Have you received this test case procedural?

Ref: https://youtu.be/iIiL_1ac9fM

Thanks.

Wu Nan Lin January 13, 2018 at 1:09 PM

Hello Krunal Bauskar,

It is my record video for reproduce this test case.

Please refer from this Youtube link: https://youtu.be/iIiL_1ac9fM

Thanks.

Wu Nan Lin January 12, 2018 at 1:31 AM

Hello Krunal Bauskar,

This procedure is correct.

Please give me few day for record this issue and reproducible test-case to video file through my program.

Thanks for your test.

Cannot Reproduce

Details

Assignee

Unassigned

Reporter

Wu Nan Lin

Labels

5.7gtidpxc

Affects versions

5.7.22-29.26

Priority

Medium

Smart Checklist

Created January 9, 2018 at 9:26 AM

Updated March 6, 2024 at 11:17 PM

Resolved November 13, 2018 at 11:52 AM

Configure