MySQL Bugs: #102100: DCL commands are not replicated witch certain replication filters

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)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7.31, 8.0.22, 5.7.32	OS:	Any
Assigned to:		CPU Architecture:	Any

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.

Test case for MTR

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

Hello Sveta,

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

regards,
Umesh

As best I can tell, DCL works like DDL with respect to replication. It is logged in statement form, which means that db-level filters depend on the default db but table-level filters depend on the table/schema name -- in this case, mysql.user.

Is this understanding correct?