-
Type:
Bug
-
Status: Done
-
Priority:
Medium
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 5.6.40-26.25, 5.7.22-29.26
-
Component/s: None
-
Labels:None
https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1736995
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-wild-do-table = s4_origin.%
replicate-do-db = nX_origin
replicate-wild-do-table = nX_origin.%
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://bugs.launchpad.net/percona-xtradb-cluster/+bug/1706820) from version 5.7.18 onwards, and wondered if this is related in some way, possibly a side-effect of the change?
Version details:
Master:
innodb_version | 5.7.20
protocol_version | 10
slave_type_conversions |
tls_version | TLSv1,TLSv1.1
version | 5.7.20-log
version_comment | MySQL Community Server (GPL)
version_compile_machine | x86_64
version_compile_os | Linux
Slave (Galera node)
innodb_version | 5.7.19-17
protocol_version | 10
tls_version | TLSv1,TLSv1.1,TLSv1.2
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_compile_machine | x86_64
version_compile_os | debian-linux-gnu
wsrep_provider_version | 3.22(r8678538)