Bug #102100 DCL commands are not replicated witch certain replication filters

• Submitted: 31 Dec 2020 10:58
• Modified: 31 Dec 2020 12:01
• Reporter: Sveta Smirnova (OCA)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.7.31, 8.0.22, 5.7.32
• OS: Any
• CPU Architecture: Any

[31 Dec 2020 10:58] Sveta Smirnova

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

However, if I add 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.

[31 Dec 2020 10:59] Sveta Smirnova

Test case for MTR

Attachment: bug102100.test (application/octet-stream, text), 731 bytes.

[31 Dec 2020 12:01] MySQL Verification Team

Hello Sveta,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh