Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-7487

DCL commands are not replicated witch certain replication filters

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Cannot Reproduce
    • 5.7.32-35, 8.0.22-13
    • None
    • None

    Description

      Description:

      If replica has filter --replicate-ignore-db=mysql and a DCL statement is issued in another database it is still replicated.

      However, if I add an additional filter replicate-do-table a DCL would not be recorded even if it is executed in the database, not related to the table filter.

      This contradicts with both
      https://dev.mysql.com/doc/refman/8.0/en/replication-rules-examples.html
      and behavior, considered correct, in
      bug #70877
       
      How to repeat:
      1. DCL statement is replicated.

      On the replica:

      stop slave;
      change replication filter REPLICATE_IGNORE_DB = (mysql);
      start slave;
      

      On the source:

      use test;
      create user replicated1;
      

      On the replica:

      select user, host from mysql.user;
      user	host
      replicated1	%
      ...
      

      This behavior is correct and expected.

      2. DCLs are not replicated.

      On the same replica:

      stop slave;
      change replication filter REPLICATE_DO_TABLE = (db1.foo);
      start slave;
      

      On the source:

      create database db1;
      use test;
      create user not_replicated;
      use db1;
      create user also_not_replicated;
      select user, host from mysql.user;
      user	host
      also_not_replicated	%
      not_replicated	%
      ...
      

      On the replica:

      select user, host from mysql.user;
      user	host
      replicated1	%
      root	127.0.0.1
      root	::1
      root	delly-7390
      mysql.session	localhost
      mysql.sys	localhost
      root	localhost
      

      See also attached test case for MTR

      Suggested fix:
      Replicate DCL statements no matter of table filters.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              sveta.smirnova Sveta Smirnova
              Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Smart Checklist