Wednesday 12 October 2011

MySql Replication - Master Master - Producing Conflict

Conflict is when the Master database are not in sync.
MySQL Master Master have a cool conflict. When the latency between two server is high you could get a case where two database
could be modifying the same row and instead of clobbering each other. The data from the database swaped instead.

 
Server A            Server B
row 1: c            row 1: c
change row 1 = a    change row 1 = b
High Latency
get change from b   get change from a
set row 1 = b       set row 1 = a 
row 1: b            row 1: a

Trying it your Self

sql1.example.com
create database test;
use test; 
CREATE TABLE animals (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM;
insert into animals set name = "chicken";

Now both database will have one animal in the animals table.
id:1, name:chicken

sql1.example.com
stop slave

sql2.example.com
stop slave

sql1.example.com
update animals set name = "dog" where id=1;

sql2.example.com
update animals set name = "cat" where id=1;

sql1.example.com
start slave

sql2.example.com
start slave


Now the bug kick in and we get a dog in sql2 and a cat in sql1


We can also produce conflicting by simultaneously insert a record in each server with conflicting primary/unique key. 

No comments:

Post a Comment