Hummm, this is very interesting. It looks like using an AUTO_INC will work
ok for a "1 master to many slaves" ok, but for mixed mode replication "many
master to many slave" this would work out quite well, therefor not to
stumble on eachothers unique keys generated. However, what about this
approach:
1. On master server #1, manually generate the counter, but make it every 5,
starting with 1 (ie 1, 6, 11, 16, ...)
2. On master server #1, do the same thing, but start counting at 2 (ie 2, 7,
12, 17, ...)
3. So on, and so forth...
This way, when they cross-replicate to eachother, they will be "filling in
the holes" and not be stepping on eachothers toes. Of course this will lock
you into having a max of 5 master/slaves that can successfully
cross-replicate, but at least its still a INT field, and not a CHAR field,
making indexing much faster. I guess you could at a later date change the
offset from 5 to say 10 if you wanted to in mid-stream and add another 5
master/slaves to the mix - I don't see why this would be a problem.
This would at least give you the added benefit of using multiple servers for
UPDATEs, instead of just one master one.
However, I thought I read somewhere that a future version will be supporting
this feature...
Patrick
# For replication, yes, there will be a problem, but
# this has anything to do with mysql. You'd have the same
# problem with a Sybase identity field replicated to another
# database server.
#
# A possible solution is this:
# 1. have a separate table for generating counter fields.
#
# 2. make the replicated table's primary key char
#
# 3. Identify each replicated server with a character.
# For example, we have a server in NY that's identified
# with an 'N' and one in Miami, that's an 'M'.
#
# 4. When inserting a record get the next counter
# value from the counter table, concatenate it with
# the server identifier and voila, a key that's unique
# across replicated servers. NY server ids will look like
# N1000, N1001, N1002 and Miami M1000, M1001, M1002 and so on.
#
# The downside: you have a char field to index and that
# ain't as fast as an index on a numeric field.
#
# Christopher Lambrou,
# CGL Computer Services, Inc.
# Empire State Building,
# PMB 16J Suite 3304
# New York, NY 10118
# Tel: (212) 971-9723
# Fax: (212) 564-1135
# URL: http://www.cglcomputer.com
# Email: [EMAIL PROTECTED]
#
# On 5/3/2001 18:03:17, you said:
# >This sounds encouraging, but are there any known problems
# with the MySQL
# >replication model currently available?? Would the slave
# servers have the
# >same auto_inc column attributes? Could this conflict somehow
# with the master
# >server??
# >
# >Thanks for the quick reply!
# >Patrick
# >
# ># Personnaly, i have found autoincrement fields to be rock solid.
# ># I use'em all over the place on a 1 Gb database, web based.
# ># Don't do it manually . You'll end up doing the same thing
# ># that mysql gives you for free.
# >#
# ># Christopher Lambrou,
# ># CGL Computer Services, Inc.
# ># Empire State Building,
# ># PMB 16J Suite 3304
# ># New York, NY 10118
# ># Tel: (212) 971-9723
# ># Fax: (212) 564-1135
# ># URL: http://www.cglcomputer.com
# ># Email: [EMAIL PROTECTED]
# >#
# ># On 5/3/2001 15:57:38, you said:
# ># >Hello!
# ># >I have a database with about 10 tables in it. In every
# table I have a
# ># >RECORD_ID
# ># >field so that I can at least uniquely identify a row if I
# ># need to, also its
# ># >used in relationships. The question is should I use the
# ># AUTO_INCREMENT for
# ># >this, or should I manually generate this value, getting the
# ># next highest
# ># >number, then putting it in there. Is there any known
# ># replication problems if
# ># >I use AUTO_INCREMENT??? Would I be safer in just doing this
# ># manually myself
# ># >in my code?? This is going to be a web-based app, so many
# ># users will be
# ># >using the db at the same time.
# ># >Thanks!
# ># >
# ># >Patrick
# ># >
# >#
# >---------------------------------------------------------------------
# ># >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
# >
#
#
# ---------------------------------------------------------------------
# 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
