Bug #101306 relay log copy database events that not included in replicate do db filter
Submitted: 24 Oct 2020 22:07 Modified: 28 Oct 2020 9:45
Reporter: mohamed atef Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S1 (Critical)
Version:5.7.24 OS:Windows
Assigned to: MySQL Verification Team CPU Architecture:Any

[24 Oct 2020 22:07] mohamed atef
Description:
relay log write events that not belong to database in 
replicate_do_db filter list
and the server unable to skip this transactions

relay log write events that belong to database in 
replicate_ignore_db filter list
and the server unable to skip this transactions

How to repeat:
i had master server server1 had 3 databases
slave server server2 had 2 databases
in my.ini file for server1
binlog_do_db=db1
binlog_do_db=db2
binlog_do_db=db3
enforce_gtid_consistency = ON
binlog-format = ROW
gtid-mode = ON

in my.ini file for server2
binlog_do_db=db1
binlog_do_db=db2

replicate_do_db=db1
replicate_do_db=db2
enforce_gtid_consistency = ON
binlog-format = ROW
gtid-mode = ON
in master server ==> server1
in db3 i had update
use db3;
update db3.tbl1 set id=1 where id=2;
the server wrote this transaction in binlog

in slave server ==> server2
relay log copy this transaction from binlog
and write it 
and the replication halted in this transaction
and server can`t skip this transaction

then i reset slave;
and added this line to my.ini file in server2
replicate_ignore_db=db3

the replication halted in this transaction
and server can`t skip this transaction

Suggested fix:
server should skip transaction for databases not included in
replicate_do_db filter
[27 Oct 2020 22:29] MySQL Verification Team
Hi,

Using binlog-do-db is dangerous if you are not sure what you are doing.

If you look at the test where my.cnf has binlog-do-db=db1

you will see that table test.test is not in binlog as you would expect, but table test.t2 is in binlog as you probably don't expect but is according to how filtering in mysql server work.

All best
Bogdan

mysql [127.0.0.1:8022] {msandbox} ((none)) > use test;
Database changed
mysql [127.0.0.1:8022] {msandbox} (test) > create table test (x int);
Query OK, 0 rows affected (0.00 sec)

mysql [127.0.0.1:8022] {msandbox} (test) > use db1
Database changed
mysql [127.0.0.1:8022] {msandbox} (db1) > create table test.t2 (x int);
Query OK, 0 rows affected (0.01 sec)

mysql [127.0.0.1:8022] {msandbox} (db1) > \q
Bye
[arhimed@localdev msb_8_0_22]$ ./stop
stop /home/arhimed/sandboxes/msb_8_0_22
[arhimed@localdev msb_8_0_22]$ cd data
[arhimed@localdev data]$ ~/opt/mysql/8.0.22/bin/mysqlbinlog --base64-output=never binlog.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201027 23:20:04 server id 1  end_log_pos 125 CRC32 0xeeebd5d5  Start: binlog v 4, server v 8.0.22 created 201027 23:20:04 at startup
ROLLBACK/*!*/;
# at 125
#201027 23:20:04 server id 1  end_log_pos 156 CRC32 0xb429f5b5  Previous-GTIDs
# [empty]
# at 156
#201027 23:20:11 server id 1  end_log_pos 233 CRC32 0xf5811c64  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no       original_committed_timestamp=1603837212001647   immediate_commit_timestamp=1603837212001647     transaction_length=182
# original_commit_timestamp=1603837212001647 (2020-10-27 23:20:12.001647 CET)
# immediate_commit_timestamp=1603837212001647 (2020-10-27 23:20:12.001647 CET)
/*!80001 SET @@session.original_commit_timestamp=1603837212001647*//*!*/;
/*!80014 SET @@session.original_server_version=80022*//*!*/;
/*!80014 SET @@session.immediate_server_version=80022*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 233
#201027 23:20:11 server id 1  end_log_pos 338 CRC32 0x2f7b8ab1  Query   thread_id=8     exec_time=0     error_code=0    Xid = 4
SET TIMESTAMP=1603837211/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database db1
/*!*/;
# at 338
#201027 23:20:57 server id 1  end_log_pos 415 CRC32 0x00ce1bef  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no       original_committed_timestamp=1603837257855559   immediate_commit_timestamp=1603837257855559     transaction_length=192
# original_commit_timestamp=1603837257855559 (2020-10-27 23:20:57.855559 CET)
# immediate_commit_timestamp=1603837257855559 (2020-10-27 23:20:57.855559 CET)
/*!80001 SET @@session.original_commit_timestamp=1603837257855559*//*!*/;
/*!80014 SET @@session.original_server_version=80022*//*!*/;
/*!80014 SET @@session.immediate_server_version=80022*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 415
#201027 23:20:57 server id 1  end_log_pos 530 CRC32 0x89bf09d8  Query   thread_id=8     exec_time=0     error_code=0    Xid = 18
use `db1`/*!*/;
SET TIMESTAMP=1603837257/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table test.t2 (x int)
/*!*/;
# at 530
#201027 23:24:48 server id 1  end_log_pos 553 CRC32 0x68368834  Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[arhimed@localdev data]$
[27 Oct 2020 22:44] mohamed atef
Look i know what im doing
But you can't understand
In server 1 when i add binlog-do-db=db3
I add it as i want to be replicated by third server 
But here not the problem 
In server 2 i didnot want it to replicate db3
So i add only to conf file 
replicate-do-db=db1
Replicate-do-db=db2
Why server 2 relay log 
Can't skip the transactions 
For tables of db3 
And replication halted in this transaction 
Especialy if the transaction was in the end of relay log file 
And server can't rotate to next relay log file
[27 Oct 2020 22:51] mohamed atef
Repeat my case and check both binlog and relay log files
[28 Oct 2020 9:05] MySQL Verification Team
Hi,

That is not how do-db filtering works.

kind regards
Bogdan
[28 Oct 2020 9:45] mohamed atef
Hi your reply is amazing!!!!
In slave server 
Replication filter
Said dont replicate db3 
Only replicate db1,db2
Dont binlog db3
Only binlog db1,db2
Why slave server stacked at event 
In db3 
And relay log cant skip this trabsaction
The transaction is update db3.tbl1 set id=1 where id=2;
Why slave server cant skip it 
And the transaction is the last one at relaylog slave cant rotate to next relay log
Answer my question
I had check replication filter documentation
And find that the slave shoukd skip this transaction