Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-904

Replication filters not working with account management statements (CREATE USER)

    XMLWordPrintable

    Details

    • 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

      Description

      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)
       

        Attachments

          Activity

            People

            Assignee:
            kenn.takara Kenn Takara
            Reporter:
            kenn.takara Kenn Takara
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist