Bug #109763 MySQL Replication setup with Replicate_Do_DB breaks replication

• Submitted: 24 Jan 2023 19:33
• Modified: 25 Jan 2023 13:10
• Reporter: Chanakya Kondam
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 8.0.30
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any
• Tags: MySQL, mysql8

[24 Jan 2023 19:33] Chanakya Kondam

Description:
Hello, 

We have a MySQL chain replication setup. A->B->C. With replication filter only on replica C. 
Replication breaks when a create table statement is executed after "use replicationfilerdb"; create table `otherdb..tablename` on replica C with following error:
Error 'Unknown database 'otherdb'' on query. Default database: 'replicationfilerdb'. Query: 'CREATE TABLE `otherdb`.`tablename`

DB Versions:
All three nodes are on 8.0.30 with replica C running with replicate-do-db= replicationfilerdb in my.cnf

How to repeat:
Set up a chain replication A->B->C with Instance C replicate-do-db= replicationfilerdb in my.cnf
On source database execute a create table statement as following:

Node A>use replicationfilerdb
Node A> create table otherdb.test(`svtest_id` bigint unsigned NOT NULL DEFAULT '0');

Statement replicates to replica B but fails on replica C with following error:
Error 'Unknown database 'otherdb'' on query. Default database: 'replicationfilerdb'. Query: 'CREATE TABLE `otherdb`.`tablename`

[24 Jan 2023 19:57] Chanakya Kondam

binlog-format is ROW

[25 Jan 2023 13:10] MySQL Verification Team

Hi,

> use replicationfilerdb"; create table `otherdb..tablename`

This is not a bug, this is how MySQL replication filters work. It does not work as how many would expect but it is properly documented.

Thank you for using MySQL