On Mon, 2007-11-12 at 21:30 +0200, Timo Sirainen wrote:
On 12.11.2007, at 20.14, Rick Romero wrote:
I'm not sure if this would allow multi-master replication though.
I know MySQL offers support for it, but what happens when both masters have added a row that causes a unique key collision? Can you write
triggers to fix those cases automatically?It's taken care of in Mysql 5.0. There's a couple settings: auto_increment_increment = 10 auto_increment_offset = 1 auto_increment_offset = 2 (for other master)
I know that takes care of autoincrement keys, but it's not enough.
There are several other unique keys that can't be handled like that.
Most importantly IMAP UIDs. They must always be increasing.
Actually it wouldn't be enough to handle duplicate key collisions. It would have to be able to handle a situation like:
master 1: add UID 100 master 1: expunge UID 100 master 2: add UID 100
When 1 and 2 are synchronized, master2's UID 100 needs to be given a new UID. If replication runs pre-insert triggers, maybe this could be implemented by keeping track of "next UID" and then if a new UID is added with less than that, its UID would be changed (and also existing UIDs with that or a higher value so client doesn't cache wrong messages).
The above should still happen only rarely when masters aren't communicating with each others. If replication is done asynchronously that could easily happen all the time and it wouldn't work well. It could be prevented with some kind of global locks/sequences. Does MySQL cluster support them?
And what about PostgreSQL's multi-master replication? :) The current mail-sql code works only with it.