MySQL Bugs: #106970: Replicate_Wild_Do

Bug #106970	Replicate_Wild_Do_Table don't take effect
Submitted:	8 Apr 2022 16:55	Modified:	15 Apr 2022 18:10
Reporter:	xincheng xie	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	8.0.21	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
Hello ,
i set up a set of cluster with masters and one slave ,both of them are setted by 
binlog_format =row.
Premise is no fault.

in second master my.cnf:
binlog-do-db=itpuxdb1

in slave 's my.cnf:
replicate_wild_do_table=itpuxdb1.%
replicate_do_db = itpuxdb1

On the master , when i use wrong dbname,and do statement " insert into itpuxdb1.table ",master 's binlog is filtering correctly,"insert into" is logged by rows. But on the slave ,gtid is transformed to relay.log and be executed ,in
the binlog ,it is empty after setting gtid_next=XXXXXX.
The point is that both of them is binlog_format =row.
And i use correct dbname;
use itpuxdb1;
And execute statment ,everthing is ok,and i try to use change replication filter
,it still same thing.

How to repeat:
change master to master_host='192.168.0.51', master_port=3306, master_user='repuser', master_password='repuser123', master_auto_position=1 for channel 'master51';
change master to master_host='192.168.0.52', master_port=3306, master_user='repuser', master_password='repuser123', master_auto_position=1 for channel 'master52';
change replication filter replicate_do_db =( itpuxdb ) for channel 'master51';
change replication filter replicate_wild_do_table =('itpuxdb.%') for channel 'master51';

change replication filter replicate_do_db =(itpuxdb1) for channel 'master52';
change replication filter replicate_wild_do_table =('itpuxdb1.%') for channel 'master52';

create database itpuxdb1;
use itpuxdb;
create table itpuxdb1.itpuxbak11 (id int primary key,name varchar(40)); 

last ddl will lost on the slave;
==================================================================
in passing what in the binlog 
master:
SET @@SESSION.GTID_NEXT= '4fd3980a-b627-11ec-b2e9-000c2987611a:10'/*!*/;
# at 2477
#220409  0:22:24 server id 523306  end_log_pos 2602 CRC32 0x2f3310b5    Query   thread_id=14    exec_time=0     error_code=0    Xid = 208
SET TIMESTAMP=1649434944/*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database itpuxdb1
/*!*/;
# at 2602
#220409  0:22:28 server id 523306  end_log_pos 2679 CRC32 0x1ea2e87d    GTID    last_committed=10       sequence_number=11      rbr_only=no     original_committed_timestamp=1649434948884314   immediate_commit_timestamp=1649434948884314 transaction_length=240
# original_commit_timestamp=1649434948884314 (2022-04-09 00:22:28.884314 CST)
# immediate_commit_timestamp=1649434948884314 (2022-04-09 00:22:28.884314 CST)
/*!80001 SET @@session.original_commit_timestamp=1649434948884314*//*!*/;
/*!80014 SET @@session.original_server_version=80021*//*!*/;
/*!80014 SET @@session.immediate_server_version=80021*//*!*/;
SET @@SESSION.GTID_NEXT= '4fd3980a-b627-11ec-b2e9-000c2987611a:11'/*!*/;
# at 2679
#220409  0:22:28 server id 523306  end_log_pos 2842 CRC32 0xf26fad64    Query   thread_id=14    exec_time=0     error_code=0    Xid = 209
SET TIMESTAMP=1649434948/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table itpuxdb1.itpuxbak11 (id int primary key,name varchar(40))
/*!*/;
# at 2842

slave :
SET @@SESSION.GTID_NEXT= '4fd3980a-b627-11ec-b2e9-000c2987611a:10'/*!*/;
# at 1797
#220409  0:22:24 server id 523306  end_log_pos 1922 CRC32 0x194daf5a    Query   thread_id=14    exec_time=0     error_code=0    Xid = 58
SET TIMESTAMP=1649434944/*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database itpuxdb1
/*!*/;
# at 1922
#220409  0:22:28 server id 523306  end_log_pos 2006 CRC32 0x189c2ca1    GTID    last_committed=8        sequence_number=9       rbr_only=no     original_committed_timestamp=1649434948884314   immediate_commit_timestamp=1649434948814028 transaction_length=237
# original_commit_timestamp=1649434948884314 (2022-04-09 00:22:28.884314 CST)
# immediate_commit_timestamp=1649434948814028 (2022-04-09 00:22:28.814028 CST)
/*!80001 SET @@session.original_commit_timestamp=1649434948884314*//*!*/;
/*!80014 SET @@session.original_server_version=80021*//*!*/;
/*!80014 SET @@session.immediate_server_version=80021*//*!*/;
SET @@SESSION.GTID_NEXT= '4fd3980a-b627-11ec-b2e9-000c2987611a:11'/*!*/;
# at 2006
#220409  0:22:28 server id 523306  end_log_pos 2082 CRC32 0x699d734e    Query   thread_id=14    exec_time=0     error_code=0
SET TIMESTAMP=1649434948/*!*/;
BEGIN
/*!*/;
# at 2082
#220409  0:22:28 server id 523306  end_log_pos 2159 CRC32 0xddfc208a    Query   thread_id=14    exec_time=0     error_code=0
SET TIMESTAMP=1649434948/*!*/;
COMMIT
/*!*/;
# at 2159

Hi,

This is not a bug. Filters apply only on "default" database, if you use database.tablename in your query filters will ignore the database part and work only on default database as per design

Hi, i understand what you said ,but after readingde reference
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#option_mysqld_rep...
"
Row-based replication.  Tells the replication SQL thread to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. Suppose that the replica is started with --replicate-do-db=sales and row-based replication is in effect, and then the following statements are run on the source:

USE prices;
UPDATE sales.february SET amount=amount+100;
"
So ,if i do that 
"  --replicate-do-db=db2
use  db1 ; create db2.table1; "
According to the reference ,it will  work,actually ,it dont.

i use the  "binlog_format=row"

You are right!
I think this is because some of the statements are logged as statements even if binlog is set to row. Anyhow this create should have worked.

thanks, apologies for not understanding the issue at first