[MDEV-17090] Hybrid replication replicate_ignore_table not working Created: 2018-08-29  Updated: 2018-08-29  Resolved: 2018-08-29

Status: Closed
Project: MariaDB Server
Component/s: Configuration, Replication
Affects Version/s: 10.0.36, 10.3.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: ton wittenberg Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Debian 9 mariadb 10.3.9


 Description   

Hello,

I think i kinda ran into a strange issue here

could there be any reason that Replicate_Ignore_Table does not work when using row based replication? i noticed that in 10.0.36 and in 10.3.9 its not working when i slave from a master that uses row based replication however if i slave from a different master which uses statement based replication. then it works just fine.

here is the proof:

im in the proces of moving a single master(mariadb 10.0.15) to a multi node galera cluster(10.3.9)
so i currently have one galera node replicating from the current master.
i also have a 3th slave that is currently replicating from the current master.
so i did a change master on the 3th slave to check if i can replicate from the galera node.

and the following happened:

this is while replicating from the current master. everything is fine here:

*************************** 1. row ***************************
 
Slave_IO_State: Queueing master event to the relay log
 
Master_Host: 10.1.214.101
 
Master_User: root
 
Master_Port: 3306
 
Connect_Retry: 60
 
Master_Log_File: mysql-bin.041929
 
Read_Master_Log_Pos: 336868991
 
Relay_Log_File: relay.000609
 
Relay_Log_Pos: 336869248
 
Relay_Master_Log_File: mysql-bin.041929
 
Slave_IO_Running: Yes
 
Slave_SQL_Running: Yes
 
Replicate_Do_DB: test,mysql
 
Replicate_Ignore_DB:
 
Replicate_Do_Table:
 
Replicate_Ignore_Table: test.admoney,test.campaigndaybudgeds,test.campaign daybudgeds_pops,test.campaigndaybudgeds_speed,test .loginattempts
 
Replicate_Wild_Do_Table:
 
Replicate_Wild_Ignore_Table:
 
Last_Errno: 0
 
Last_Error:
 
Skip_Counter: 0
 
Exec_Master_Log_Pos: 336868953
 
Relay_Log_Space: 336869624
 
Until_Condition: None
 
Until_Log_File:
 
Until_Log_Pos: 0
 
Master_SSL_Allowed: No
 
Master_SSL_CA_File:
 
Master_SSL_CA_Path:
 
Master_SSL_Cert:
 
Master_SSL_Cipher:
 
Master_SSL_Key:
 
Seconds_Behind_Master: 0
 
Master_SSL_Verify_Server_Cert: No
 
Last_IO_Errno: 0
 
Last_IO_Error:
 
Last_SQL_Errno: 0
 
Last_SQL_Error:
 
Replicate_Ignore_Server_Ids:
 
Master_Server_Id: 101
 
Master_SSL_Crl:
 
Master_SSL_Crlpath:
 
Using_Gtid: Current_Pos
 
Gtid_IO_Pos: 0-101-152368736455
 
Replicate_Do_Domain_Ids:
 
Replicate_Ignore_Domain_Ids:
 
Parallel_Mode: conservative
 
SQL_Delay: 0
 
SQL_Remaining_Delay: NULL
 
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 
Slave_DDL_Groups: 0
 
Slave_Non_Transactional_Groups: 35173761
 
Slave_Transactional_Groups: 12

but when i change to the new master(galera node) i get the following error:

*************************** 1. row ***************************
 
Slave_IO_State: Waiting for master to send event
 
Master_Host: 10.1.214.100
 
Master_User: root
 
Master_Port: 3306
 
Connect_Retry: 60
 
Master_Log_File: mysql-bin.000573
 
Read_Master_Log_Pos: 406381234
 
Relay_Log_File: relay.000586
 
Relay_Log_Pos: 172156660
 
Relay_Master_Log_File: mysql-bin.000573
 
Slave_IO_Running: Yes
 
Slave_SQL_Running: No
 
Replicate_Do_DB: test,mysql
 
Replicate_Ignore_DB:
 
Replicate_Do_Table:
 
Replicate_Ignore_Table: test.admoney,test.campaigndaybudgeds,test.campaign daybudgeds_pops,test.campaigndaybudgeds_speed,test .loginattempts
 
Replicate_Wild_Do_Table:
 
Replicate_Wild_Ignore_Table:
 
Last_Errno: 1146
 
Last_Error: Error executing row event: 'Table 'test.loginattempts' doesn't exist'
 
Skip_Counter: 0
 
Exec_Master_Log_Pos: 401792970
 
Relay_Log_Space: 176745219
 
Until_Condition: None
 
Until_Log_File:
 
Until_Log_Pos: 0
 
Master_SSL_Allowed: No
 
Master_SSL_CA_File:
 
Master_SSL_CA_Path:
 
Master_SSL_Cert:
 
Master_SSL_Cipher:
 
Master_SSL_Key:
 
Seconds_Behind_Master: NULL
 
Master_SSL_Verify_Server_Cert: No
 
Last_IO_Errno: 0
 
Last_IO_Error:
 
Last_SQL_Errno: 1146
 
Last_SQL_Error: Error executing row event: 'Table 'test.loginattempts' doesn't exist'
 
Replicate_Ignore_Server_Ids:
 
Master_Server_Id: 100
 
Master_SSL_Crl:
 
Master_SSL_Crlpath:
 
Using_Gtid: Current_Pos
 
Gtid_IO_Pos: 0-101-152368802207
 
Replicate_Do_Domain_Ids:
 
Replicate_Ignore_Domain_Ids:
 
Parallel_Mode: conservative
 
SQL_Delay: 0
 
SQL_Remaining_Delay: NULL
 
Slave_SQL_Running_State:
 
Slave_DDL_Groups: 0
 
Slave_Non_Transactional_Groups: 35230746
 
Slave_Transactional_Groups: 24

i already have slave-skip-errors = all

i just state that test.loginattempts should be ignored so why it fails on that one?

the only difference i can think of is that the current master uses statement based replication. and the new master uses row based replication.
it seems to me that Replicate_Ignore_Table simply is not working when using row based replication.

i hope someone can clear this up cause i dont understand why this is happening.

 Comments   
Comment by Elena Stepanova [ 2018-08-29 ]

Evaluating replication filtering rules is complicated. Yes, among other factors, it depends on the binlog format, and it's not obvious for either statement or row. It also depends on the correlation between table-level rules and database-level rules, which you also have, and which, in turn, can be quite confusing depending on the binlog format.

We can't tell what's wrong with your setup based on the little information above, but you can, if you study the rules carefully. Judging by the "simply isn't working" complaint, you haven't yet done so, because nobody who has ever said that anything is simple there.

You can use MySQL manual in addition to MariaDB KB. MySQL manual might contain more detailed and verbose explanation of how it works.

If after studying the rules you find out that you need help with your setup, you might want to acquire a support contract.
If after studying the rules you still think you encounter an actual bug, then please comment and the issue will be re-open, but we'll ask you to specify what exactly doesn't work as documented and provide a (non-)working example.

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