[MDEV-8003] Under the multi-source-replication, when performing "grant ..." statement of the loop execution occurs. - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Replication
Labels:
Environment:

Hide
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

Show
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

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

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

bin.log
144 kB
2015-04-16 09:56

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2015-04-16 19:11

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;

Elena Stepanova added a comment - 2015-04-16 19:11 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;

wenlong added a comment - 2015-04-17 05:18

Thank you very much ～～

wenlong added a comment - 2015-04-17 05:18 Thank you very much ～～

People

Assignee:: Andrei Elkin

Reporter:: wenlong

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2015-04-16 09:27

Updated:: 2024-09-10 15:06

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration