Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8003

Under the multi-source-replication, when performing "grant ..." statement of the loop execution occurs.

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.0
        • Fix Version/s: 10.0
        • Component/s: Replication
        • Environment:

          Description

          There is no problem when I execute "insert into d1.table1 ..." on A. But when I execute the "grant ...; flush privileges;" when there is a problem in the C and D, the occurrence of a loop execution "grant ...; flush privileges;".

          then modification my.cnf on C and D. Add the following
          _m1.replicate_wild_ignore_table=mysql.%
          m1.replicate_wild_ignore_table=information_schema.%
          m1.replicate_wild_ignore_table=performance_schema.%
          m2.replicate-ignore-db=d1_
          still loop execution,but just execute "flush privileges;".

          and the bin log like this:

          # at 573930687
#150416 11:31:07 server id 231  end_log_pos 573930725 	GTID 0-231-517276191
/*!100001 SET @@session.gtid_seq_no=517276191*//*!*/;
# at 573930725
#150416 11:31:07 server id 231  end_log_pos 573930800 	Query	thread_id=1826986	exec_time=1339	error_code=0
SET TIMESTAMP=1429155067/*!*/;
flush privileges
/*!*/;
# at 573930800
#150416 11:31:07 server id 231  end_log_pos 573930838 	GTID 0-231-517276191
/*!100001 SET @@session.gtid_seq_no=517276191*//*!*/;
# at 573930838
#150416 11:31:07 server id 231  end_log_pos 573930913 	Query	thread_id=1826986	exec_time=1339	error_code=0
SET TIMESTAMP=1429155067/*!*/;
flush privileges
/*!*/;
# at 573930913
#150416 11:31:07 server id 231  end_log_pos 573930951 	GTID 0-231-517276191
/*!100001 SET @@session.gtid_seq_no=517276191*//*!*/;
# at 573930951
#150416 11:31:07 server id 231  end_log_pos 573931026 	Query	thread_id=1826986	exec_time=1339	error_code=0
SET TIMESTAMP=1429155067/*!*/;
flush privileges
/*!*/;
# at 573931026
#150416 11:31:07 server id 231  end_log_pos 573931064 	GTID 0-231-517276191
/*!100001 SET @@session.gtid_seq_no=517276191*//*!*/;
# at 573931064
#150416 11:31:07 server id 231  end_log_pos 573931139 	Query	thread_id=1826986	exec_time=1339	error_code=0
SET TIMESTAMP=1429155067/*!*/;
flush privileges
/*!*/;
# at 573931139
#150416 11:31:07 server id 231  end_log_pos 573931177 	GTID 0-231-517276191
/*!100001 SET @@session.gtid_seq_no=517276191*//*!*/;
# at 573931177
#150416 11:31:07 server id 231  end_log_pos 573931252 	Query	thread_id=1826986	exec_time=1339	error_code=0
SET TIMESTAMP=1429155067/*!*/;
flush privileges
/*!*/;

          For more information see bin.log

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                Thanks for the report.

                In case of INSERTs, you are protected by your ignore-db options; but there is no protection for the "global" statements.
                I'm afraid it's one of multi-source assumptions (much like having non-conflicting data) that there will be no circular updates coming from different sources. However, I will assign it to Michael Widenius to confirm.

                ------------------

                Summary for Monty

                Replication topology is this:

                S3 <-> S4
^      ^
|      |
S1 <-> S2

                S1 issues a global statement, e.g. 'flush privileges'.
                It comes to S3 from S1, to S4 via S2, and then starts bouncing between S3 and S4. Neither can ignore it, because it was initially generated by S1 and has its server_id.

                gtid_strict_mode would detect it, but all it can do is abort replication completely.

                Here is a simple test case to reproduce the problem:

                t1.cnf
                !include include/default_mysqld.cnf
!include include/default_client.cnf

[mysqld.1]
server-id=1
log-bin=master-bin
log-warnings=2
log-slave-updates
gtid-domain-id=1
gtid-strict-mode=off

[mysqld.2]
server-id=2
log-bin=master-bin
log-warnings=2
log-slave-updates
gtid-domain-id=2
gtid-strict-mode=off

[mysqld.3]
server-id=3
log-bin=master-bin
log-warnings=2
log-slave-updates
gtid-domain-id=3
gtid-strict-mode=off

[mysqld.4]
server-id=4
log-bin=master-bin
log-warnings=2
log-slave-updates
gtid-domain-id=4
gtid-strict-mode=off

[ENV]
SERVER_MYPORT_1= @mysqld.1.port
SERVER_MYSOCK_1= @mysqld.1.socket
SERVER_MYPORT_2= @mysqld.2.port
SERVER_MYSOCK_2= @mysqld.2.socket
SERVER_MYPORT_3= @mysqld.3.port
SERVER_MYSOCK_3= @mysqld.3.socket
SERVER_MYPORT_4= @mysqld.4.port
SERVER_MYSOCK_4= @mysqld.4.socket
                t1.test
                
--connect (server_1,127.0.0.1,root,,test,$SERVER_MYPORT_1)
--connect (server_2,127.0.0.1,root,,test,$SERVER_MYPORT_2)
--connect (server_3,127.0.0.1,root,,test,$SERVER_MYPORT_3)
--connect (server_4,127.0.0.1,root,,test,$SERVER_MYPORT_4)

--enable_connect_log

--connection server_1
eval change master 'm1' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_2, master_user='root', master_use_gtid=slave_pos;
start all slaves;

--connection server_2
eval change master 'm1' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos;
start all slaves;

--connection server_3
eval change master 'm1' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos;
eval change master 'm2' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_4, master_user='root', master_use_gtid=slave_pos;
start all slaves;

--connection server_4
eval change master 'm1' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_2, master_user='root', master_use_gtid=slave_pos;
eval change master 'm2' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_3, master_user='root', master_use_gtid=slave_pos;
start all slaves;

--connection server_1

flush privileges;

--connection server_4
show binlog events;
--sleep 2
show binlog events;
                Show
                elenst Elena Stepanova added a comment - Thanks for the report. In case of INSERTs, you are protected by your ignore-db options; but there is no protection for the "global" statements. I'm afraid it's one of multi-source assumptions (much like having non-conflicting data) that there will be no circular updates coming from different sources. However, I will assign it to Michael Widenius to confirm. ------------------ Summary for Monty Replication topology is this: S3 <-> S4 ^ ^ | | S1 <-> S2 S1 issues a global statement, e.g. 'flush privileges'. It comes to S3 from S1, to S4 via S2, and then starts bouncing between S3 and S4. Neither can ignore it, because it was initially generated by S1 and has its server_id. gtid_strict_mode would detect it, but all it can do is abort replication completely. Here is a simple test case to reproduce the problem: t1.cnf !include include/default_mysqld.cnf !include include/default_client.cnf [mysqld.1] server-id=1 log-bin=master-bin log-warnings=2 log-slave-updates gtid-domain-id=1 gtid-strict-mode=off [mysqld.2] server-id=2 log-bin=master-bin log-warnings=2 log-slave-updates gtid-domain-id=2 gtid-strict-mode=off [mysqld.3] server-id=3 log-bin=master-bin log-warnings=2 log-slave-updates gtid-domain-id=3 gtid-strict-mode=off [mysqld.4] server-id=4 log-bin=master-bin log-warnings=2 log-slave-updates gtid-domain-id=4 gtid-strict-mode=off [ENV] SERVER_MYPORT_1= @mysqld.1.port SERVER_MYSOCK_1= @mysqld.1.socket SERVER_MYPORT_2= @mysqld.2.port SERVER_MYSOCK_2= @mysqld.2.socket SERVER_MYPORT_3= @mysqld.3.port SERVER_MYSOCK_3= @mysqld.3.socket SERVER_MYPORT_4= @mysqld.4.port SERVER_MYSOCK_4= @mysqld.4.socket t1.test --connect (server_1,127.0.0.1,root,,test,$SERVER_MYPORT_1) --connect (server_2,127.0.0.1,root,,test,$SERVER_MYPORT_2) --connect (server_3,127.0.0.1,root,,test,$SERVER_MYPORT_3) --connect (server_4,127.0.0.1,root,,test,$SERVER_MYPORT_4) --enable_connect_log --connection server_1 eval change master 'm1' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_2, master_user='root', master_use_gtid=slave_pos; start all slaves; --connection server_2 eval change master 'm1' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos; start all slaves; --connection server_3 eval change master 'm1' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos; eval change master 'm2' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_4, master_user='root', master_use_gtid=slave_pos; start all slaves; --connection server_4 eval change master 'm1' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_2, master_user='root', master_use_gtid=slave_pos; eval change master 'm2' to master_host='127.0.0.1', master_port=$SERVER_MYPORT_3, master_user='root', master_use_gtid=slave_pos; start all slaves; --connection server_1 flush privileges; --connection server_4 show binlog events; --sleep 2 show binlog events;
                Hide
                Permalink
                wenlong wenlong added a comment -

                Thank you very much ～～

                Show
                wenlong wenlong added a comment - Thank you very much ～～

                  People

                  • Assignee:
                    monty Michael Widenius
                    Reporter:
                    wenlong wenlong
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:

                      Time Tracking

                      Estimated:
                      Original Estimate - 4 weeks
                      4w
                      Remaining:
                      Remaining Estimate - 4 weeks
                      4w
                      Logged:
                      Time Spent - Not Specified
                      Not Specified