PXC 5.7.19-17 - Node acting as replication slave filters CREATE USER but other nodes do not
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC |
Confirmed
|
Undecided
|
Kenn Takara |
Bug Description
Hi,
I've a test setup with a vanilla MySQL 5.7.20 Master replicating into a 3 node Percona 5.7.19 Cluster.
I've host "S4" as the MySQL master, and nodes "N1", "N2" and "N3" making up the cluster, with node "N2" being the slave. GTIDs are on all hosts.
Replication filters exist on all PXC nodes as follows:
replicate-do-db = s4_origin
replicate-
replicate-do-db = nX_origin
replicate-
Thus I'm expecting to replicate the "s4_origin" database from the standalone node into the cluster, and then just "nX_origin" and "s4_origin" databases within the cluster.
So before we start I want to make a note that I'm aware that CREATE USER is treated as DDL, replicated at the statement level, and thus filtered based on the default database. With the filters above I should expect create user executed in the "mysql" database to not be replicated (and it isn't), but when executed from "s4_origin" database I should expect it to replicate.
On server S4:
(localhost) [mysql]> select user, host from mysql.user where user = 'dave';
Empty set (0.00 sec)
(localhost) [mysql]> use s4_origin
Database changed
(localhost) [s4_origin]> create user 'dave'@'%';
Query OK, 0 rows affected (0.00 sec)
(localhost) [s4_origin]> select user, host from mysql.user where user = 'dave';
+------+------+
| user | host |
+------+------+
| dave | % |
+------+------+
1 row in set (0.00 sec
On node N2: (slave)
(localhost) [none]> select user, host from mysql.user where user = 'dave';
Empty set (0.00 sec)
On node N1:
(localhost) [(none)]> select user, host from mysql.user where user = 'dave';
+------+------+
| user | host |
+------+------+
| dave | % |
+------+------+
On node N3:
(localhost) [(none)]> select user, host from mysql.user where user = 'dave';
+------+------+
| user | host |
+------+------+
| dave | % |
+------+------+
1 row in set (0.00 sec)
This makes no sense to me, I'm willing to accept it's a configuration issue somewhere but I'm unable to see anything that I think would cause this. We have Percona clusters with the same setup on version 5.7.16-10 that don't exhibit this behavior, thus I'm inclined to think it's a bug.
Hunting for existing bugs, I noticed there was a change to how sql_log_bin is treated between galera nodes (https:/
Version details:
Master:
innodb_version | 5.7.20
protocol_version | 10
slave_
tls_version | TLSv1,TLSv1.1
version | 5.7.20-log
version_comment | MySQL Community Server (GPL)
version_
version_
Slave (Galera node)
innodb_version | 5.7.19-17
protocol_version | 10
tls_version | TLSv1,TLSv1.
version | 5.7.19-17-57-log
version_comment | Percona XtraDB Cluster (GPL), Release rel17, Revision c10027a, WSREP version 29.22, wsrep_29.22
version_
version_
wsrep_
Changed in percona-xtradb-cluster: | |
assignee: | nobody → Kenn Takara (kenn-takara) |
Verfied this with
(1) PXC 5.7.20 (not released yet) standalone (wsrep disabled) as async master
(2) A 2-node PXC 5.7.20 cluster (Nodes N!, N2) with N! as async slave
Result:
After running the steps above ('use s4_origin; create user 'dave'@'%'; )
'dave' appears on async master
'dave' is not on N1 (PXC cluster node, async slave)
'dave' is on N2 (PXC cluster node)
The CREATE USER is being replicated to the other nodes.