[Maria-developers] Best way of starting multi-source connections with active slave.
Hi, I am setting up multi-source replication with four nodes, where nodes 1, 2, and 3 are masters and node 4 is the slave of the first 3. The slave is *active*, meaning that it has a binary log and runs transactions of its own. All is well if I run 'reset master' in all nodes before setting up replication. CHANGE MASTER 'node1' TO master_host='host1', master_port=3306, master_user='some_slave', master_password='some_slave', master_use_gtid=current_pos; CHANGE MASTER 'node2' TO master_host='host2', master_port=3306, master_user='some_slave', master_password='some_slave', master_use_gtid=current_pos; CHANGE MASTER 'node3' TO master_host='host3', master_port=3306, master_user='some_slave', master_password='some_slave', master_use_gtid=current_pos; start all slaves; However, if host 4 has a binary log and has logged one or more transactions before starting replication, we get a conflict. Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-104-1, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra erroneous transactions' I get the same result if I use 'slave_pos' instead of 'current_pos'. Notice that the changes in the slave do not conflict with anything that happens in the masters. For example: #1 node4 (fan-in slave): create user distribution_user identified by 'something'; #2 node4 CHANGE MASTER ... #3 node4 start all slaves #4 node1 (master): create table t1 #5 node2 (master): create table t2 #6 node3 (master): create table t3 This sequence of events fails. If I execute event #1 after #2 and #3, then all works as expected. Or it all works if I say master_use_gtid=no, but that would defeat the purpose of having GTIDs, or am I missing something? Is there a way of enabling multi-source replication with the slave having non-conflicting transactions already logged? Thanks Giuseppe
----- On 6 Aug, 2015, at 4:54 PM, Giuseppe Maxia g.maxia@gmail.com wrote:
Hi, I am setting up multi-source replication with four nodes, where nodes 1, 2, and 3 are masters and node 4 is the slave of the first 3. The slave is *active*, meaning that it has a binary log and runs transactions of its own.
All is well if I run 'reset master' in all nodes before setting up replication.
CHANGE MASTER 'node1' TO master_host='host1', master_port=3306, master_user='some_slave', master_password='some_slave', master_use_gtid=current_pos; CHANGE MASTER 'node2' TO master_host='host2', master_port=3306, master_user='some_slave', master_password='some_slave', master_use_gtid=current_pos; CHANGE MASTER 'node3' TO master_host='host3', master_port=3306, master_user='some_slave', master_password='some_slave', master_use_gtid=current_pos; start all slaves;
However, if host 4 has a binary log and has logged one or more transactions before starting replication, we get a conflict.
each master should be on a different gtid_domain_id https://mariadb.com/kb/en/mariadb/global-transaction-id/#gtid_domain_id also see variable gtid_ignore_duplicates
Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-104-1, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra erroneous transactions'
-- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
Giuseppe Maxia <g.maxia@gmail.com> writes:
I am setting up multi-source replication with four nodes, where nodes 1, 2, and 3 are masters and node 4 is the slave of the first 3. The slave is *active*, meaning that it has a binary log and runs transactions of its own.
Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-104-1, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra erroneous transactions'
I get the same result if I use 'slave_pos' instead of 'current_pos'.
You need to set gtid_domain_id different on each server: https://mariadb.com/kb/en/mariadb/global-transaction-id/#use-with-multi-sour... This way, your slave's GTID position can be multi-dimensional, effectively tracking the position in each server individually, eg: 1-101-50,2-102-100,3-103-90,4-104-1
From the error it looks like you do not have different gtid_domain_id configured, but else do ask again if you still have problems.
Hope this helps, - Kristian.
Thanks for the advice. I did have a different domain ID for each master, but not for the slave,m which had 0. The conflict only happens if the slave keeps the default domain ID regardless of the masters having a definite one or not. In the scenario described in my original message. If I set the domain ID of the slave before running the event #1, there is no conflict. Cheers Giuseppe From: Kristian Nielsen <knielsen@knielsen-hq.org> Reply: Kristian Nielsen <knielsen@knielsen-hq.org>> Date: 6 Aug 2015 at 09:10:51 To: Giuseppe Maxia <g.maxia@gmail.com>> Cc: maria-developers@lists.launchpad.net <maria-developers@lists.launchpad.net>> Subject: Re: [Maria-developers] Best way of starting multi-source connections with active slave. Giuseppe Maxia <g.maxia@gmail.com> writes:
I am setting up multi-source replication with four nodes, where nodes 1, 2, and 3 are masters and node 4 is the slave of the first 3. The slave is *active*, meaning that it has a binary log and runs transactions of its own.
Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-104-1, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra erroneous transactions'
I get the same result if I use 'slave_pos' instead of 'current_pos'.
You need to set gtid_domain_id different on each server: https://mariadb.com/kb/en/mariadb/global-transaction-id/#use-with-multi-sour... This way, your slave's GTID position can be multi-dimensional, effectively tracking the position in each server individually, eg: 1-101-50,2-102-100,3-103-90,4-104-1 From the error it looks like you do not have different gtid_domain_id configured, but else do ask again if you still have problems. Hope this helps, - Kristian.
participants (3)
-
Daniel Black -
Giuseppe Maxia -
Kristian Nielsen