PXC 5.7.19-17 - Node acting as replication slave filters CREATE USER but other nodes do not

[Glyn Astill]

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)

[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.

[Kenn Takara (kenn-takara)]

Also verified on 5.6.37

[Kenn Takara (kenn-takara)]

Note: Re-reading the bug, I believe the behavior should end up as:
'dave' appears on async master
'dave' is not on N1
'dave' is not on N2 <-- change from current behavior

I think this is what the Glyn expects
'dave' appears on async master
'dave' is on N1 <-- change from current behavior
'dave' is on N2

CREATE USER goes through a different codepath, as it does not care what the default database is. It will always try to access mysql.* tables. So the filter on N1 rejects the change.

[Glyn Astill (lorddef)]

Thanks Kenn.

Yes that's correct, and you're right in pointing out what I said I expected was incorrect and the user really should not replicate at all in this instance.

[Shahriyar Rzayev (rzayev-sehriyar)]

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