[MDEV-36329] with Master/Slave replication from Galera4 node, MariaDB ignores replicate_do_table - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.5.19
Fix Version/s: N/A
Component/s: Replication
Labels:
None

Description

on on Galera4 (master) node
1. do the full backup with maria-backup

on standalone (slave) node:
2. maria-backup prepare backup
3. create table, discard tablespace, import tablespace from backup
4. set global replicate_do_db='ctac';
5. SET GLOBAL replicate_do_table='ctac.writetest';
6. CHANGE MASTER 'ppes' TO MASTER_HOST='ppes2', master_user='root', master_password='password', MASTER_PORT=3406, master_log_file='pes2-logbin.003137', master_log_pos=5789;
7. check the error.log:
2025-03-09 13:00:55 2743 [Note] Master 'ppes': Slave I/O thread: connected to master 'root@ppes2:3406',replication started in log 'pes2-logbin.003137' at position 5789
2025-03-09 13:00:55 2744 [ERROR] Master 'ppes': Slave SQL: Could not execute Update_rows_v1 event on table heartbeat.heartbeat; Can't find record in 'heartbeat', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log pes2-logbin.003137, end_log_pos 6171, Gtid 144-85-7041435, Internal MariaDB error code: 1032
2025-03-09 13:00:55 2744 [ERROR] Master 'ppes': Slave SQL: Node has dropped from cluster, Gtid 144-85-7041435, Internal MariaDB error code: 1047
2025-03-09 13:00:55 2744 [Note] Master 'ppes': Slave SQL thread exiting, replication stopped in log 'pes2-logbin.003137' at position 5789

=================
where ctac.writetest - table to be replicated, heartbeat.heartbeat - local to galera4 cluster (master) with pt-heartbeat manual emulation, which we don't want to replicate

Attachments

Activity

Ascending order - Click to sort in descending order

Stanislav Sukholet added a comment - 3 days ago

Galera4 disables changing of BINLOG_FORMAT to other than ROW

MariaDB [(none)]> SET GLOBAL binlog_format='MIXED';
ERROR 1231 (42000): Variable 'binlog_format' can't be set to the value of 'MIXED'

MariaDB [(none)]> SET GLOBAL binlog_format='ROW';
Query OK, 0 rows affected (0.000 sec)

Stanislav Sukholet added a comment - 3 days ago Galera4 disables changing of BINLOG_FORMAT to other than ROW MariaDB [(none)] > SET GLOBAL binlog_format='MIXED'; ERROR 1231 (42000): Variable 'binlog_format' can't be set to the value of 'MIXED' MariaDB [(none)] > SET GLOBAL binlog_format='ROW'; Query OK, 0 rows affected (0.000 sec)

Stanislav Sukholet added a comment - 3 days ago

point 6.5 in description:
6.5 start slave 'ppes';

Stanislav Sukholet added a comment - 3 days ago point 6.5 in description: 6.5 start slave 'ppes';

Stanislav Sukholet added a comment - 2 days ago - edited

what I found, trying to configure replication:

MariaDB [(none)]> stop replica 'ppes';

Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> SET GLOBAL *replicate_do_table*='ctac.writetest';CHANGE MASTER 'ppes' TO MASTER_HOST='ppes2',master_user='root

',master_password='ultra_secret_password',MASTER_PORT=3406,master_log_file='pes2-logbin.000004',master_log_pos=5548379;

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.007 sec)

MariaDB [(none)]> SHOW SLAVE 'ppes'STATUS \G

*************************** 1. row ***************************

                Slave_IO_State:

                   Master_Host: ppes2

                   Master_User: root

                   Master_Port: 3406

                 Connect_Retry: 60

               Master_Log_File: pes2-logbin.000004

           Read_Master_Log_Pos: 5548379

                Relay_Log_File: pprw1-relay-ppes.000001

                 Relay_Log_Pos: 4

         Relay_Master_Log_File: pes2-logbin.000004

              Slave_IO_Running: No

             Slave_SQL_Running: No

               *Replicate_Do_DB*:

           Replicate_Ignore_DB:

            *Replicate_Do_Table*:

        Replicate_Ignore_Table:

       Replicate_Wild_Do_Table:

   Replicate_Wild_Ignore_Table:

===
so there is some kind of incorrect parameter setting?

Stanislav Sukholet added a comment - 2 days ago - edited what I found, trying to configure replication: MariaDB [(none)]> stop replica 'ppes'; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [(none)]> SET GLOBAL *replicate_do_table*='ctac.writetest';CHANGE MASTER 'ppes' TO MASTER_HOST='ppes2',master_user='root ',master_password='ultra_secret_password',MASTER_PORT=3406,master_log_file='pes2-logbin.000004',master_log_pos=5548379; Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.007 sec) MariaDB [(none)]> SHOW SLAVE 'ppes'STATUS \G *************************** 1. row *************************** Slave_IO_State: Master_Host: ppes2 Master_User: root Master_Port: 3406 Connect_Retry: 60 Master_Log_File: pes2-logbin.000004 Read_Master_Log_Pos: 5548379 Relay_Log_File: pprw1-relay-ppes.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: pes2-logbin.000004 Slave_IO_Running: No Slave_SQL_Running: No *Replicate_Do_DB*: Replicate_Ignore_DB: *Replicate_Do_Table*: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: === so there is some kind of incorrect parameter setting?

Stanislav Sukholet added a comment - 2 days ago

and, finally, I've found that replicate_do_table can be set on unnamed connection only:

MariaDB [(none)]> reset replica all;

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show replica status\G

Empty set (0.000 sec)

MariaDB [(none)]> SET GLOBAL replicate_do_table='ctac.writetest';CHANGE MASTER 'ppes' TO MASTER_HOST='ppes2', master_user='root', master_password='password', MASTER_PORT=3406, master_log_file='pes2-logbin.000004', master_log_pos=5548379;

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.009 sec)

And the replicate_do_table is effective:

MariaDB [(none)]> show replica status\G

*************************** 1. row ***************************

                Slave_IO_State:

                   Master_Host: ppes2

                   Master_User: root

                   Master_Port: 3406

                 Connect_Retry: 60

               Master_Log_File: pes2-logbin.000004

           Read_Master_Log_Pos: 5548379

                Relay_Log_File: pprw1-relay.000001

                 Relay_Log_Pos: 4

         Relay_Master_Log_File: pes2-logbin.000004

              Slave_IO_Running: No

             Slave_SQL_Running: No

               Replicate_Do_DB: ctac

           Replicate_Ignore_DB:

            Replicate_Do_Table: ctac.writetest

        Replicate_Ignore_Table:

       Replicate_Wild_Do_Table:

   Replicate_Wild_Ignore_Table:

                    Last_Errno: 0

                    Last_Error:

                  Skip_Counter: 0

           Exec_Master_Log_Pos: 5548379

               Relay_Log_Space: 256

               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: 0

                Last_SQL_Error:

   Replicate_Ignore_Server_Ids:

              Master_Server_Id: 85

                Master_SSL_Crl:

            Master_SSL_Crlpath:

                    Using_Gtid: No

                   Gtid_IO_Pos:

       Replicate_Do_Domain_Ids:

   Replicate_Ignore_Domain_Ids:

                 Parallel_Mode: optimistic

                     SQL_Delay: 0

           SQL_Remaining_Delay: NULL

       Slave_SQL_Running_State:

              Slave_DDL_Groups: 1

Slave_Non_Transactional_Groups: 0

    Slave_Transactional_Groups: 2171

1 row in set (0.000 sec)

Stanislav Sukholet added a comment - 2 days ago and, finally, I've found that replicate_do_table can be set on unnamed connection only: MariaDB [(none)]> reset replica all; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> show replica status\G Empty set (0.000 sec) MariaDB [(none)]> SET GLOBAL replicate_do_table='ctac.writetest';CHANGE MASTER 'ppes' TO MASTER_HOST='ppes2', master_user='root', master_password='password', MASTER_PORT=3406, master_log_file='pes2-logbin.000004', master_log_pos=5548379; Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.009 sec) And the replicate_do_table is effective: MariaDB [(none)]> show replica status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: ppes2 Master_User: root Master_Port: 3406 Connect_Retry: 60 Master_Log_File: pes2-logbin.000004 Read_Master_Log_Pos: 5548379 Relay_Log_File: pprw1-relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: pes2-logbin.000004 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: ctac Replicate_Ignore_DB: Replicate_Do_Table: ctac.writetest Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 5548379 Relay_Log_Space: 256 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: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 85 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave_DDL_Groups: 1 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 2171 1 row in set (0.000 sec)

Stanislav Sukholet added a comment - 2 days ago

compare to named replication channel:

MariaDB [(none)]> SET GLOBAL replicate_do_table='ctac.writetest'; CHANGE MASTER 'ppes' TO MASTER_HOST='ppes2', master_user='root', master_password='password', MASTER_PORT=3406, master_log_file='pes2-logbin.000004', master_log_pos=5548379;

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.015 sec)

MariaDB [(none)]> show replica 'ppes' status\G

*************************** 1. row ***************************

                Slave_IO_State:

                   Master_Host: ppes2

                   Master_User: root

                   Master_Port: 3406

                 Connect_Retry: 60

               Master_Log_File: pes2-logbin.000004

           Read_Master_Log_Pos: 5548379

                Relay_Log_File: pprw1-relay-ppes.000001

                 Relay_Log_Pos: 4

         Relay_Master_Log_File: pes2-logbin.000004

              Slave_IO_Running: No

             Slave_SQL_Running: No

               Replicate_Do_DB:

           Replicate_Ignore_DB:

            Replicate_Do_Table:

        Replicate_Ignore_Table:

       Replicate_Wild_Do_Table:

   Replicate_Wild_Ignore_Table:

                    Last_Errno: 0

                    Last_Error:

So this bugreport somehow transfers to feature request: create way to apply replication filters to named replication channels

Stanislav Sukholet added a comment - 2 days ago compare to named replication channel: MariaDB [(none)]> SET GLOBAL replicate_do_table='ctac.writetest'; CHANGE MASTER 'ppes' TO MASTER_HOST='ppes2', master_user='root', master_password='password', MASTER_PORT=3406, master_log_file='pes2-logbin.000004', master_log_pos=5548379; Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.015 sec) MariaDB [(none)]> show replica 'ppes' status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: ppes2 Master_User: root Master_Port: 3406 Connect_Retry: 60 Master_Log_File: pes2-logbin.000004 Read_Master_Log_Pos: 5548379 Relay_Log_File: pprw1-relay-ppes.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: pes2-logbin.000004 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: So this bugreport somehow transfers to feature request: create way to apply replication filters to named replication channels

Stanislav Sukholet added a comment - 2 days ago

what was successfully resolved with default_master_connection system variable.

Please close this issue as 'not a bug'

Stanislav Sukholet added a comment - 2 days ago what was successfully resolved with default_master_connection system variable. Please close this issue as 'not a bug'

Brandon Nesterenko added a comment - 2 days ago

Glad you found default_master_connection, CTAC! Closing as requested

Brandon Nesterenko added a comment - 2 days ago Glad you found default_master_connection , CTAC ! Closing as requested

People

Assignee:: Unassigned

Reporter:: Stanislav Sukholet

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 4 days ago 07:49

Updated:: 2 days ago 14:41

Resolved:: 2 days ago 14:41

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration