[MDEV-8003] Under the multi-source-replication, when performing "grant ..." statement of the loop execution occurs. Created: 2015-04-16  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.0, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: wenlong Assignee: Andrei Elkin
Resolution: Unresolved Votes: 0
Labels: multisource, replication, verified
Environment:

A:mysql Ver 15.1 Distrib 10.0.13-MariaDB, for Linux (x86_64) using readline 5.1
B:mysql Ver 15.1 Distrib 10.0.13-MariaDB, for Linux (x86_64) using readline 5.1
C:mysql Ver 15.1 Distrib 10.0.17-MariaDB, for Linux (x86_64) using readline 5.1
D:mysql Ver 15.1 Distrib 10.0.17-MariaDB, for Linux (x86_64) using readline 5.1

A master is B(mastername: m1), and replicate datasource d1
B master is A(mastername: m1), and replicate datasource d1
C master is A(mastername: m1) and D(mastername: m2), and replicate datasource d1 d2
D master is B(mastername: m1) and C(mastername: m2), and replicate datasource d1 d2

Attachments: File bin.log    

 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

 Comments   
Comment by Elena Stepanova [ 2015-04-16 ]

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 monty 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;

Comment by wenlong [ 2015-04-17 ]

Thank you very much ～～

Generated at Thu Feb 08 07:23:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.