subject:"AUTO_INCREMENT with Replication"

Re: AUTO_INCREMENT with Replication

2002-07-04 Thread Eric Frazier


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

Re: AUTO_INCREMENT with Replication

2002-07-04 Thread Marc Prewitt


Eric Frazier wrote:
 
 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?

Doesn't sound insane at all.  We've been using something like that for the
past 4 years (before auto_increment was available in mysql).

Here's what we're using:

sub _NextId {
my $self = shift;
my $pid = 0 x (5 - length($$)) . $$;
$pid .= 0 x (3 - length($COUNTER)) . $COUNTER;
$COUNTER = 0 if $COUNTER++  998;
return time() . $pid;
}

You could use the hires time and get rid of the counter probably.

 
 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

RE: AUTO_INCREMENT with Replication

2002-07-04 Thread 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

AUTO_INCREMENT with Replication

2002-07-03 Thread Eric Frazier


 From the manual 4.10.4
Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID(), 
and TIMESTAMP values.

I am somewhat fearful and curious about how this works. Say we have a 
master web database that gets replicated back to the office slave over the 
Internet. A person on the web puts in an order to the master web db, 
another person in the office enters a phone order, but that order goes into 
the slave because orders get shipped based on information in the office 
slave. How would I not at some point end up with replication errors because 
of duplicate auto_inc values?

Would setting up replication as a circle help? Or would timing issues still 
cause a problem? (The insert on the Master beats the insert on the slave 
that was getting sent at the time) I am using 4.0.2 alpha so I am most 
concerned with how that version is affected.


Thanks,

Eric

sql,querysql,querysql,querysql,querysql,querysql,querysql,query







-
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

Re: AUTO_INCREMENT with Replication

2002-07-03 Thread Jeremy Zawodny


On Wed, Jul 03, 2002 at 10:54:23AM -0700, Eric Frazier wrote:
  From the manual 4.10.4
 Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID(), 
 and TIMESTAMP values.
 
 I am somewhat fearful and curious about how this works. Say we have
 a master web database that gets replicated back to the office slave
 over the Internet. A person on the web puts in an order to the
 master web db, another person in the office enters a phone order,
 but that order goes into the slave because orders get shipped based
 on information in the office slave. How would I not at some point
 end up with replication errors because of duplicate auto_inc values?
 
 Would setting up replication as a circle help? Or would timing
 issues still cause a problem? (The insert on the Master beats the
 insert on the slave that was getting sent at the time) I am using
 4.0.2 alpha so I am most concerned with how that version is
 affected.

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

Re: AUTO_INCREMENT with Replication

2002-07-03 Thread speters


if you set it up so that the slaves occasionally update the master, you can
get around the
problem as follows.

For the *primary* table(s), when you update the master, get the data for 1
row to update from the
slave.
When you insert this row into the master, remove the auto_increment field
from the list of set values.
Then, when the row is inserted, it is assigned a new auto_increment value.

This could be a problem if the value you just dropped is meaningful, like an
invoice # for instance, and
not just a (meaningless) unique id.

If you have *child* tables that use that key like a foreign key, you'll need
to
do some manipulation on the rows from those child tables so that they still
relate
properly.

I have successfully implemented things similar to this for an order /billing
system where orders
could come from on-line, phone orders, etc. It is a bit of work though, and
depending on the app,
there may be some things you can't do quite right.
I had to work around some issues that i never resolved :(

hope this helps
sean peters
[EMAIL PROTECTED]


- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Eric Frazier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, July 03, 2002 1:07 PM
Subject: Re: AUTO_INCREMENT with Replication


 On Wed, Jul 03, 2002 at 10:54:23AM -0700, Eric Frazier wrote:
   From the manual 4.10.4
  Replication will be done correctly with AUTO_INCREMENT,
LAST_INSERT_ID(),
  and TIMESTAMP values.
 
  I am somewhat fearful and curious about how this works. Say we have
  a master web database that gets replicated back to the office slave
  over the Internet. A person on the web puts in an order to the
  master web db, another person in the office enters a phone order,
  but that order goes into the slave because orders get shipped based
  on information in the office slave. How would I not at some point
  end up with replication errors because of duplicate auto_inc values?
 
  Would setting up replication as a circle help? Or would timing
  issues still cause a problem? (The insert on the Master beats the
  insert on the slave that was getting sent at the time) I am using
  4.0.2 alpha so I am most concerned with how that version is
  affected.

 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



-
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

Re: AUTO_INCREMENT with Replication

2002-07-03 Thread Eric Frazier


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

Re: AUTO_INCREMENT with Replication

Re: AUTO_INCREMENT with Replication

RE: AUTO_INCREMENT with Replication

AUTO_INCREMENT with Replication

Re: AUTO_INCREMENT with Replication

Re: AUTO_INCREMENT with Replication

Re: AUTO_INCREMENT with Replication

7 matches

Site Navigation

Mail list logo

Footer information