[MDEV-21638] Replication filters seems to be incoherent between DDL and tables usages Created: 2020-02-03  Updated: 2020-03-07

Status: Open
Project: MariaDB Server
Component/s: Replication
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Richard DEMONGEOT Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: replication
Environment:

Debian, with MariaDB 10.3.21 pacakge (source : MariaDB)



 Description   

Hello,

I've seen an inconsistency on the replication filter flow.

Host A :
MariaDB 10.3
Database X, and Y.

Host B : MariaDB 10.3
Database Y.

B is a slave of A, with a replication filter "Replicate_Ignore_DB" set to X.
A have the variable "binlog-format = ROW"

Scenario :
mysql -hA
use X
create table Y.test (a int primary key);
insert into Y.test values (1);

This scenario breaks the replication with error "Table Y.test does not exists".
=> That mean the DDL where ignored, while the insert is played.

Alternatively :
mysql -hA
use Y
create table Y.test (a int primary key);
use X
insert into Y.test values (1);
truncate table Y.test;
insert into Y.test values (1);
will break the replication with "Duplicate key".

Once again, DDL (truncate table) were ignored, while insertions were played on the replication.

In my opinion, it should be more coherent to do the same for DDL and insert/update.

Option 1: Ignore both of them. (will break some sub-obtimal use cases where users are logged on the wrong database);
Option 2: Replicate insert/update as now, but replicate also DDLs.

Best regards,
Edit : passed to minor, workarround possible with a replicate_wild_ignore_table="X.%" instead of "replicate_ignore_db=X"



 Comments   
Comment by VAROQUI Stephane [ 2020-02-03 ]

Hi,

As documented.
If you need to be able to support cross-database statement with replication filters and statement-based binary logging, you should use the following replication filters:

replicate_wild_do_table
replicate_wild_ignore_table

The workaround is to add an extra filter replicate_wild_do_table="Y.%" can fixe your issue .

That the replication stopped when mixing row events and statement ddl is indeed questionable

  • it's a feature that let you identified where in the code you have a miss database usage that don't respect filters
  • it's a bug because unpriviledges users can just break the replication if DBA have install filters .

Thanks

Comment by Richard DEMONGEOT [ 2020-02-03 ]

Hello,

Trying with Replicate_wild_ignore_table="A.%" and seems to works.
Need some more tests.

Thans Stephane for this work arround .

Regards,

Generated at Thu Feb 08 09:08:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.