AUTO_INCREMENT - configuration for replication with dual masters

I'm looking at setting up two mysql servers in replication with both
being masters. The most obvious snag in this scenario is
auto_increment, as disconnections between servers could allow user
interaction to generate duplicate keys.

In a moment of zen I thought, "What if one server dealt only in even
numbers, and the other only in odd numbers?" This solution would keep
things simple and manageable, while also keeping the servers off
eachothers' toes.

Has anyone ever tried this? Is it even possible? if not, is there a
similar solution allowing for the same kind of integration?

Going further, I wonder if it's possible to do the same thing with
three or more servers, giving each an algorythm to generate a series
of auto_increment numbers that doesnt collide with the other master
servers in the chain.

re: AUTO_INCREMENT - configuration for replication with dual masters

Dark Matt wrote:
[color=blue]
>I'm looking at setting up two mysql servers in replication with both
>being masters. The most obvious snag in this scenario is
>auto_increment, as disconnections between servers could allow user
>interaction to generate duplicate keys.
>
>In a moment of zen I thought, "What if one server dealt only in even
>numbers, and the other only in odd numbers?" This solution would keep
>things simple and manageable, while also keeping the servers off
>eachothers' toes.
>
>Has anyone ever tried this? Is it even possible? if not, is there a
>similar solution allowing for the same kind of integration?
>
>Going further, I wonder if it's possible to do the same thing with
>three or more servers, giving each an algorythm to generate a series
>of auto_increment numbers that doesnt collide with the other master
>servers in the chain.
>
>[/color]
Went through a similar challenge with 3 separate Oracle databases and
dispersed servers (hundreds of tables - ya' got love ERP - millions of
records each), several years ago. Resolved by adding a column in the
incoming tables which was named "src" char. DB1 was named ROW, DB2
named EMM and DB3 simply APN. In addition to doing the individual
replication sets, we were able to bring all 3 tables into a single set
using their unique rid+src as a key/index.

Just a thought, not an endorsement *grin*.

email nospam1001 at nonags dot com

re: AUTO_INCREMENT - configuration for replication with dual masters

Dark Matt wrote:
[color=blue]
> I'm looking at setting up two mysql servers in replication with both
> being masters. The most obvious snag in this scenario is
> auto_increment, as disconnections between servers could allow user
> interaction to generate duplicate keys.
>
> In a moment of zen I thought, "What if one server dealt only in even
> numbers, and the other only in odd numbers?" This solution would keep
> things simple and manageable, while also keeping the servers off
> eachothers' toes.
>
> Has anyone ever tried this? Is it even possible? if not, is there a
> similar solution allowing for the same kind of integration?
>
> Going further, I wonder if it's possible to do the same thing with
> three or more servers, giving each an algorythm to generate a series
> of auto_increment numbers that doesnt collide with the other master
> servers in the chain.
>
>[/color]
Went through a similar challenge with 3 separate Oracle databases and
dispersed servers (hundreds of tables - ya' got love ERP - millions of
records each), several years ago. Resolved by adding a column in the
incoming tables which was named "src" char. In its simplest form, DB1
was named ROW, DB2 named EMM and DB3 simply APN. In addition to doing
the individual replication sets, we were able to bring all 3 tables into
a single set using their unique rid+src as a key/index.

Just a thought, not an endorsement *grin*.

email nospam1001 at nonags dot com

re: AUTO_INCREMENT - configuration for replication with dual masters

Dark Matt wrote:
[color=blue]
>I'm looking at setting up two mysql servers in replication with both
>being masters. The most obvious snag in this scenario is
>auto_increment, as disconnections between servers could allow user
>interaction to generate duplicate keys.
>
>In a moment of zen I thought, "What if one server dealt only in even
>numbers, and the other only in odd numbers?" This solution would keep
>things simple and manageable, while also keeping the servers off
>eachothers' toes.
>
>Has anyone ever tried this? Is it even possible? if not, is there a
>similar solution allowing for the same kind of integration?
>
>Going further, I wonder if it's possible to do the same thing with
>three or more servers, giving each an algorythm to generate a series
>of auto_increment numbers that doesnt collide with the other master
>servers in the chain.
>
>[/color]
Went through a similar challenge with 3 separate Oracle databases and
dispersed servers (hundreds of tables - ya' got love ERP - millions of
records each), several years ago. Resolved by adding a column in the
incoming tables which was named "src" char. DB1 was named ROW, DB2
named EMM and DB3 simply APN. In addition to doing the individual
replication sets, we were able to bring all 3 tables into a single set
using their unique rid+src as a key/index.

Just a thought, not an endorsement *grin*.

email nospam1001 at nonags dot com

re: AUTO_INCREMENT - configuration for replication with dual masters

Dark Matt wrote:
[color=blue]
> I'm looking at setting up two mysql servers in replication with both
> being masters. The most obvious snag in this scenario is
> auto_increment, as disconnections between servers could allow user
> interaction to generate duplicate keys.
>
> In a moment of zen I thought, "What if one server dealt only in even
> numbers, and the other only in odd numbers?" This solution would keep
> things simple and manageable, while also keeping the servers off
> eachothers' toes.
>
> Has anyone ever tried this? Is it even possible? if not, is there a
> similar solution allowing for the same kind of integration?
>
> Going further, I wonder if it's possible to do the same thing with
> three or more servers, giving each an algorythm to generate a series
> of auto_increment numbers that doesnt collide with the other master
> servers in the chain.
>
>[/color]
Went through a similar challenge with 3 separate Oracle databases and
dispersed servers (hundreds of tables - ya' got love ERP - millions of
records each), several years ago. Resolved by adding a column in the
incoming tables which was named "src" char. In its simplest form, DB1
was named ROW, DB2 named EMM and DB3 simply APN. In addition to doing
the individual replication sets, we were able to bring all 3 tables into
a single set using their unique rid+src as a key/index.

Just a thought, not an endorsement *grin*.

email nospam1001 at nonags dot com