RE: AUTO_INCREMENT with Replication

[Madscientist]

We use a strategy of giving each node a unique ID and using the combination
of the NodeId and the AUTO_INCREMENT value as the key for the objects. The
AUTO_INCREMENT field ensures uniqueness between records in a single node.
The NodeID ensures uniqueness between nodes.

This way nodes that are widely dispersed can continue to operate within the
applications restrictions when they are out of contact and all systems in
the loop can recover w/ replication when contact is recovered.

This also has the advantage that there is no management required for the
allocation of keys to use - only one when a node is established.

The down side is complexity in the primary key - but that is generally not
tough to solve since IDs are typically used as arbitrary symbols.

_M

]-----Original Message-----
]From: Eric Frazier [mailto:[EMAIL PROTECTED]]
]Sent: Thursday, July 04, 2002 11:15 AM
]To: [EMAIL PROTECTED]
]Cc: [EMAIL PROTECTED]
]Subject: Re: AUTO_INCREMENT with Replication
]
]
]Hi,
]
]That kind of bothers me. But I can see how it might be better in some way
]than using a time function. My idea was to use the perl
]Time::HiRes to make
]a unique key adjusted by timezone. As long as the clocks of the two
]machines are fairly in since, it should work :) I guess I could
]also add in
]a unique machine id like time integer key + M for master or S for slave S2
]S3 etc. Does that sound insane?
]
]
]Thanks,
]
]Eric
]
]mysql is good
]
]At 05:16 PM 2002-07-03 -0700, you wrote:
]>to somewhata simulate auto increments, each slave (and master) could be
]>periodically
]>assigned a chunk of keys to use, with the stipulation that only
]that server
]>can
]>use those keys. for instance at time 0 (arbitrarily)
]>
]>server 1 gets:     0-999
]>server 2 gets:    1000-1999
]>server 3 gets:    2000-2999
]>etc
]>
]>you'll have to keep track of this info on each server, whether in
]a table or
]>file.
]>then when that chunk is used up, that client can request another chunk of
]>keys.
]>(you could also update those key chunks daily, weekly, etc. instead.)
]>
]>The size of the chunks would want to be determined by the application,
]>frequency of
]>communication between master & slave, and volume of records going in.
]>
]>I have *not* implemented such a system, but this could work, depending on
]>your app.
]>
]>sean
]>
]>
]>----- Original Message -----
]>From: "Eric Frazier" <[EMAIL PROTECTED]>
]>To: <[EMAIL PROTECTED]>
]>Sent: Wednesday, July 03, 2002 2:32 PM
]>Subject: Re: AUTO_INCREMENT with Replication
]>
]>
]> > Well. Good to know. So I guess the only alternative would be
]to generate
]> > keys by date/time?
]> > I was hoping to avoid that. I am still worried about the timestamp type
]>not
]> > having good enough resolution. Seconds are pretty broad.
]> >
]> > Thanks,
]> >
]> > Eric
]> >
]> >
]> >
]> >
]> > >You're asking for trouble. :-)
]> > >
]> > >AUTO_INCREMENTS are not safe for use in a mutli-master environment.
]> > >The scenario you painted will result in a primary key violation on the
]> > >master when it reads the value inserted on the slave.
]> > >
]> > >Jeremy
]> > >--
]> > >Jeremy D. Zawodny, <[EMAIL PROTECTED]>
]> > >Technical Yahoo - Yahoo Finance
]> > >Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936
]> > >
]> > >MySQL 3.23.51: up 34 days, processed 779,275,123 queries
](258/sec. avg)
]> > >
]> > >---------------------------------------------------------------------
]> > >Before posting, please check:
]> > >    http://www.mysql.com/manual.php   (the manual)
]> > >    http://lists.mysql.com/           (the list archive)
]> > >
]> > >To request this thread, e-mail <[EMAIL PROTECTED]>
]> > >To unsubscribe, e-mail
]><[EMAIL PROTECTED]>
]> > >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
]> >
]> > Leading Edge Marketing Inc.
]> > 250-360-2992
]> >
]> >
]> > ---------------------------------------------------------------------
]> > Before posting, please check:
]> >    http://www.mysql.com/manual.php   (the manual)
]> >    http://lists.mysql.com/           (the list archive)
]> >
]> > To request this thread, e-mail <[EMAIL PROTECTED]>
]> > To unsubscribe, e-mail
]><[EMAIL PROTECTED]>
]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
]> >
]
]Leading Edge Marketing Inc.
]250-360-2992
]
]
]---------------------------------------------------------------------
]Before posting, please check:
]   http://www.mysql.com/manual.php   (the manual)
]   http://lists.mysql.com/           (the list archive)
]
]To request this thread, e-mail <[EMAIL PROTECTED]>
]To unsubscribe, e-mail
]<[EMAIL PROTECTED]>
]Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
]
]


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php