← Back to team overview

maria-discuss team mailing list archive

Re: Replication and auto-increment



>>>>> "Brian" == Brian Evans <grknight@xxxxxxxxxxxxxx> writes:

Brian> I was reading over the replication discussion on maria-developers
Brian> I thought I'd share some thoughts that are not so technical as I do not
Brian> understand the back end that well.

Brian> Our system is currently a master-master WAN replication setup, each
Brian> writing to a different DB with an offline process to do copies.
Brian> Our databases extensively uses auto-increments on tables (more than
Brian> necessary, but that's another story).

Brian> Would future developments allow synchronous replication to occur on a
Brian> single database when the WAN link has a single point of failure(SPoF)?
Brian> How might an auto-increment system handle such a failure?

Brian> This SPoF is our network link that can be a transient problem out of our
Brian> control.
Brian> Both sides may need to keep inserting data during this transient issue.
Brian> This is usually not an issue 99% of the time, but it's better to prepare
Brian> for downtime than trying to fix data errors in that 1%.

The easyest solution for this is to have the auto-increment as the
second part of your key:

CREATE TABLE (A int, B int auto_increment, key(A,B));

As long you keep A unique per system, you never have to worry about
duplicate keys.

Another solution is to use UUID:s instead of auto-increment.