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
