[Dovecot] mail-sql update

[Timo Sirainen]

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.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://dovecot.org/pipermail/dovecot/attachments/20071112/b50cd2d9/attachment-0001.bin