MySQL fail-over with different server-ids

Hi,


we're using a MySQL 5.1 Multi-Master setup as our DBMail back-end.
Since there is no native support for read/write splitting (and mysql
replication is asynchronous) we're always using only one database node
directly, while the other is intended for fail-over.


The MySQL server configurations include:

# PRIMARY
server-id                   = 1
auto_increment_increment    = 10
auto_increment_offset       = 1
replicate-same-server-id    = 0

# SECONDARY (fail-over node)
server-id                   = 2
auto_increment_increment    = 10
auto_increment_offset       = 2
replicate-same-server-id    = 0

This way we get rid of collisions on auto-incrementing columns.

If I recall correctly DBMail uses some auto-incrementing columns to
identify new messages and announce them to IMAP clients in IDLE (and
probably more)?

So, let's suppose we have a sequential auto-incrementing column:
1 11 21 31 41 51

Now we fail-over to the secondary node and the column ends up
non-sequential:
1 2 11 12 21 22 31 32 41 42 51 52 62 72 82


What exacly happens now? Which parts of DBMail will be mislead by this?


In addition, I found an interesting old mail (Jun 2004):

http://www.mail-archive.com/dbmail-dev@.../msg01706.html

> The main problem is the requirement of the IMAP protocol to have a unique,
> persistent, incrementing, 32 bit number for each message in each mailbox.
> AFAIK, there's no guaranteed way of doing this in multimaster :-[

After reading this I'm pretty sure something won't work as expected
after fail-over. Is this still the case?

How do you guys handle this problem?


Thanks,
John
_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Eww, let me rephrase that:

> So, let's suppose we have a sequential auto-incrementing column:
> 1 11 21 31 41 51
>
> Now we fail-over to the secondary node and the column ends up
> non-sequential:
> 1 2 11 12 21 22 31 32 41 42 51 52 62 72 82

The resulting column's keys are sequential, but the correct order of
inserted messages can't be derived from this anymore.
_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Josh Marshall-2]

On Tue, 2009-09-29 at 00:16 +0200, John Feuerstein wrote:

> Eww, let me rephrase that:
>
> > So, let's suppose we have a sequential auto-incrementing column:
> > 1 11 21 31 41 51
> >
> > Now we fail-over to the secondary node and the column ends up
> > non-sequential:
> > 1 2 11 12 21 22 31 32 41 42 51 52 62 72 82
>
> The resulting column's keys are sequential, but the correct order of
> inserted messages can't be derived from this anymore.

Something you've missed is that the mysql will always increment. So it
wont insert like:

1 2 11 12 21 22 31 32 41 42 51 {switchover} 52 62 72 82

But like:

1 11 21 31 41 51 {switchover} 52 62 72 82 92 102 112...

It will only interleave if you have the two masters actively inserting
at the same time in a round-robin fashion. Failover using heartbeat or
ucarp will avoid this.

As long as your db's are in sync you wont get any problems. We've got a
similar setup and have had it change over a number of times with no ill
effects.

_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Josh Marshall wrote:
> It will only interleave if you have the two masters actively inserting
> at the same time in a round-robin fashion. Failover using heartbeat or
> ucarp will avoid this.

Great! I must have missed that... :/

Thanks.

_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Josh Marshall-2]

On Mon, 2009-09-28 at 17:05 -0700, tabris wrote:
> Additionally, we do (at my employer) have master<->master replication
> setup with both sides able to insert, and afaict everything works fine
> except for the occasional clash over the headername table.
It's been advised against doing this, do a search through the mailing
list archives. There is an issue with IMAP message id's (not
auto-increment fields) so some new messages wont be shown on the
logged-in imap clients. Might be ok for mostly POP3 installs or for low
usage. How many messages per day is this setup storing? What happens
when one server starts to lag with the updates?

The clashes in the headername table rings alarm bells for me. If your
setup starts to get under stress or has a lot of concurrent requests
these problems will escalate. If you're going to use master-master
replication setup, just use ucarp or heartbeat and only have one server
active. It will save you headaches down the track.

_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[tabris]

Josh Marshall wrote:
> It will only interleave if you have the two masters actively inserting
> at the same time in a round-robin fashion. Failover using heartbeat or
> ucarp will avoid this.
>
> As long as your db's are in sync you wont get any problems. We've got a
> similar setup and have had it change over a number of times with no ill
> effects.
Additionally, we do (at my employer) have master<->master replication
setup with both sides able to insert, and afaict everything works fine
except for the occasional clash over the headername table.





_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[tabris]

Josh Marshall wrote:

> On Mon, 2009-09-28 at 17:05 -0700, tabris wrote:
>  
>> Additionally, we do (at my employer) have master<->master replication
>> setup with both sides able to insert, and afaict everything works fine
>> except for the occasional clash over the headername table.
>>    
> It's been advised against doing this, do a search through the mailing
> list archives. There is an issue with IMAP message id's (not
> auto-increment fields) so some new messages wont be shown on the
> logged-in imap clients.

    So far this has been minimal, albeit probably not nonexistent.

> Might be ok for mostly POP3 installs or for low
> usage. How many messages per day is this setup storing? What happens
> when one server starts to lag with the updates?
>  

    Don't know how many messages per day (don't have a quick way to
measure it at the moment) but it's just ~60users or so. And actually,
the lag hasn't been too bad, esp given the note below. I expect we can
find ways around that problem eventually [programmatically].

> The clashes in the headername table rings alarm bells for me. If your
> setup starts to get under stress or has a lot of concurrent requests
> these problems will escalate. If you're going to use master-master
> replication setup, just use ucarp or heartbeat and only have one server
> active. It will save you headaches down the track.
>  

    I'm sure that what I'm doing is undocumented. And no, we can't use
heartbeat for this, it's being done across the Atlantic, so that any
user can login to either server at either site and still retrieve their
email. The only rule is they're not supposed to access both servers from
IMAP at the same time.



_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Paul J Stevens]

Josh Marshall wrote:

> As long as your db's are in sync you wont get any problems. We've got a
> similar setup and have had it change over a number of times with no ill
> effects.

Basically, mysql failover is ok, but multi-master insertion is not.

--
  ________________________________________________________________
  Paul Stevens                                      paul at nfg.nl
  NET FACILITIES GROUP                     GPG/PGP: 1024D/11F8CD31
  The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail