disk fragmentation problems

[speters]

Hi, all
I have a mysql server that has been suffereing performance problems due to
disk fragmentation problems.

The server is on a red hat 7.3 box, and the tables in question are MyIsam.

The problem seems to be occuring because there are millions of inserts and
millions of deletes each week.
(probably 1 million plus records each day.)

So we've ended up with data all over the place on the drive. Basically, the
drive was spending the vast
majority of its time seeking. So, we're looking for good defrag tools, or
programming ideas on how to keep this problem
from recurring on a regular basis. (this problem will never be fully
alleviated, but minimizing it would be great)

Not that it probably matters, but the inserts / deletes are all handled thru
perl scripts with the DBI  DBD modules.
All programs are written in-house, so we can adjust them as we need to.

thanks,
sean peters
[EMAIL PROTECTED]



-
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

Year data type

[speters]

It seems odd to me that the data type Year, should only allow the limited
range of 1901-2155.
i understand that this is so that it will store in 1 byte, but whats the
point?
i wanted to use a year field for historical data, such as the year famous
people were born, such
as abe lincoln - 1809, but surprise surprise 1809 is unacceptable.

so i guess i dont see the point of the year datatype.

i would be interented in any insight as to why the year type is like this.

thanks,
sean peters
[EMAIL PROTECTED]



mysql, 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

weird auto increment behaviour (bug???)

[speters]

Hi all, I have recently encountered some strange (to me) behaviour with an
auto
increment column. It resulted because of an oversight in table creation.
Here is some SQL statements that will illustrate my issue:

CREATE TABLE test (
ID INT AUTO_INCREMENT,
Name CHAR(30),
KEY (Name, ID)
)TYPE=MyISAM;

INSERT INTO test SET Name=bob;
INSERT INTO test SET Name=tom;


Now, both of those columns got ID=1 from the insert.

A few observations:
1) MySQL requires AUTO_INCREMENT columns to have an index. This table
uses ID as an index, but it's not a leftmost-prefix of any index, which
i thought would give
a create error. (is this a bug?)
2) considering this non auto increment behaviour, i would think that ID
should be NULL, since
ID is allowed NULL values, and no value was assigned to the ID in the
insert statements.

Also, this works the same if ID is defined asID INT NOT NULL
AUTO_INCREMENT


It seems that this is what's happening:
When MySQL executes the insert queries, it correctly recognizes that ID is
an AUTO_INCREMENT
column, so it tries to get the new auto increment value. But, because ID
isnt properly indexed for
an auto increment column, the value is incorrectly reported, and the column
is set to 1 every time.

weird, huh?
sean peters
[EMAIL PROTECTED]


mysql, 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

Efficiency of SET column type queries

[speters]

I've bene trying to find info on how efficient queries are for the SET
datatype, specifically, how does
MySQL use indexes for SET column types.
I have looked in the O'Reilly MySQL Reference Manual, and at mysql.com and
have found effectively nothing on this subject.
For example, if i have a table :

CREATE TABLE some_table (
...
Set_columnSET(A,B,C,D,E),
...
KEYset_key(Set_Column)
);


i'm wondering how this column is indexed. I understand that the values
stored are bit representations, i.e.
if i store A,D, the value stored is 01001 = 9
so i'd assume that the index is on the numeric values like above. So, If I
want to find all rows where
Set_column contains a C = 00100 = 4

SELECT * FROM some_table WHERE FIND_IN_SET(C, Set_column)  0;

for instance, the MySQL engine couldn't just look in the index for all
columns with the (effective) value 4,
because C,D = 01100 = 12 would also contain a C.
In fact, exactly half of the possible numeric values would represent sets
with a C in them.
It doesnt seem reasonable that the query engine would look at half of the
index values, so i dont really see how an index on a SET column would ever
be particularly useful.

I havent used sets much, in faact i've intentionally stayed away from them,
but i have an application where SET column types seem to make a lot of
sense, but i want to figure out the indexing issue before implementing it.

The specific application has to do with parsing lines of text and
identifying certain parts of the text based on keywords. So, I'd have a
table that has a keyword column (CHAR) , and a type_of_keyword column
(SET).
This is because some keywords could indicate multiple things, for instance,
if parsing names, Thomas could be a first name or a last name,
which one would need to be determined by context on a case-by-case basis,
but I need to be able to quickly decide what types of keyword
Thomas could be. Thats easy, but if i want to get all keywords that
represent last names, it seems that a table scan would need to be performed,
and
trying to use an index would be pointless.

Note that this example is contrived, as there would be 100,000+ last names,
etc.
But the example indicates the (perceived) problem.

Any insight into how indexes work in relation to SET columns would be
appreciated.

thanks
sean peters
[EMAIL PROTECTED]




-
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

normalization question

[speters]

I'm working on a design for a database that is leaving me with some
normalization issues that I havent had to deal with before. For example, if
im trying to store
information about car dealerships I end up with the following situation:

I want to store the cars in stock at each physical dealership, with certain
attributes.

So, I have a table Dealership as follows:
Dealer_ID
Name
Address
Etc.

Then there is a Car_Make table (for things like Chevy, Hyundai):
Car_Make_ID
Make_Name
...

And a Model table (for things like Elantra, Neon, etc):
Model:
Model_ID
Car_Make_ID
Model_Name
...

And a table to describe the cars themselves: (one row per car)
Car:
VIN_number(vehicle identification number, what the DMV uses)
Model_ID
other attributes
...

Now, to associate a specific car with a dealer, I want to put Dealer_ID in
the Cars table, but then the Cars table has multiple parent tables,
specifically
Model  Dealership

I'm sure that this is far from normal, and will lead to programming
problems,
but i dont see what to do about it.

Any advice would be greatly appreciated.

 thanks,
sean peters
[EMAIL PROTECTED]


sql, 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

table damaged?

[speters]

I think i have a damaged table in one of my databases.
I can do a SELECT count(*) from table
on every table in the db with no problem, except for 1
of them, the query just sits for a while.

So i assume this table is damaged. does anyone have any suggestions as to
what i can do about this?

tests or repairs are good

thanks
sean peters
[EMAIL PROTECTED]


mysql, 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

GRANT question

[speters]

If i use GRANT and REVOKE to change user privileges, it is my understanding
that i don't have
to issue a FLUSH PRIVILEGES statement.
Is this correct?

Our sysadmin rebooted the MySQL server this morning, and there were some
privilege problems
afterward. I had to reset the privileges for some mysql users.
The only thing i can think of is that there were some pending privilege
changes that
were read by mysqld for the first time when the server restarted.

any other theories about why this would happen?

regards,
sean peters
[EMAIL PROTECTED]



-
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: SQL question

[speters]

How about

SELECT fieldname FROM table ORDER BY CAST(fieldname as SIGNED)

UNSIGNED could replace SIGNED, depending on your needs.

this works for me on Mysql 4.02 alpha on solaris

regards,
sean peters
[EMAIL PROTECTED]


- Original Message -
From: John Almberg [EMAIL PROTECTED]
To: Mysql [EMAIL PROTECTED]
Sent: Friday, September 27, 2002 8:29 AM
Subject: FW: SQL question



 Nope. I've tried every combination I can think of of these ideas. They all
 give syntax errors. I don't think arithmatic is allowed in an ORDER BY
 clause. Doesn't even work on an INT field.

 -- John

  -Original Message-
  From: gerald_clark [mailto:[EMAIL PROTECTED]]
  Sent: Friday, September 27, 2002 9:52 AM
  To: John Almberg
  Cc: Mysql
  Subject: Re: SQL question
 
 
  ORDER BY  0 + fieldname
 
  if this is not quit right try
 
  ORDER BY  0 + fieldname ,  fieldname
 
  John Almberg wrote:
 
  That gives a syntax error, unfortunately.
  
  -- JOhn
  
  
  
  -Original Message-
  From: Mihail Manolov [mailto:[EMAIL PROTECTED]]
  Sent: Thursday, September 26, 2002 9:51 PM
  To: Mysql
  Subject: Re: SQL question
  
  
  Try:
  
  SELECT fieldname FROM table ORDER 0+fieldname;
  
  
  Hope it helps.
  
  
  Mihail
  
  - Original Message -
  From: John Almberg [EMAIL PROTECTED]
  To: Mysql [EMAIL PROTECTED]
  Sent: Thursday, September 26, 2002 8:04 PM
  Subject: SQL question
  
  
  
  
  I'm trying to sort a table on a character-type field that
  
  
  contains mostly
  
  
  numbers. This field always contains either a number or a
  number followed
  
  
  by
  
  
  a character. Like '57' or '57a'.
  
  I'd like to sort the table *numerically* on this field, not
  
  
  *alphabetically*
  
  
  on this field. That is, I'd like the table to be sorted like:
  
  1 ...
  2 ...
  2a ...
  3 ...
  4d ...
  
  NOT like:
  
  1 ...
  11 ...
  111a ...
  2a ...
  22 ...
  
  See what I mean? This is a common problem, I think, when you sort an
  character type field that contains numbers. The sort comes out
  
  
  all wrong.
  
  
  Is there anyway I can achieve this sort using SQL? The target server
is
  running 3.22.32. Any ideas greatly appreciated!
  
  -- John
  
  
  
  -
  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

last_insert_id() question

[speters]

I've read a bit about using LAST_INSERT_ID(expr)
to set the next auto_increment value.

my question is, if i have 2 tables, like follows

CREATE TABLE A (
id int not null auto_increment primary key,
...
)type=MyISAM;

CREATE TABLE B (
id int not null auto_increment primary key,
...
)type=MyISAM;

and if SELECT max(id) FROM A returns 1000
and SELECT max(id) FROM B returns 3000

if i issue the query SELECT LAST_INSERT_ID(1);

then does my next insert into both A and B give A.id the value 1 and
B.id the value 1

The Mysql Reference Manual ny O'Reilly Community Press
(which is basically the on-line docs at mysql.com)
in section 6.3 (page 500 on my copy) says the following:
If expr is given as an arguement to LAST_INSERT_ID(), the value of the
arguement is returned by the function, is set as the next value to be
returned by LAST_INSERT_ID(), and is used as the next AUTO_INCREMENT
value

What i want to do is, regardless of the current max(A.id), i want the next
insert to give A.id the value 100 for instance.
and give B.id the value 200

I know that i can explicitely set the value in the insert query, such as
INSERT INTO A SET id=100, .
INSERT INTO B SET id=200, .

but, im doing a bulk load of 10+ records (plus child records), and dont
want the additional overhead in my
perl script to say if this is the first record in the load, explicitely set
the id field value, otherwise let mysql take care
of it with the normal auto_increment functionality.

another solution is to pre-emptively put a dummy record in each table,
with the desired auto_increment field value,
then delete those records at the end. but this is sloppy, and if the script
bails, those records are left.

thanks,
sean peters
[EMAIL PROTECTED]


filter buster:mysql, 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: creating indexes on production db

[speters]

If i need to create / drop indexes from MyISAM tables how does the blocking
work?
I need to mess with some indexes on a production database, and dont want
downtime.

Should i do this in the middle of the night, when hardly anyone is using the
system, or will the blocking be negligable?

thanks
sean peters
[EMAIL PROTECTED]


filter:  mysql, 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

temporary tables

[speters]

Is there a way to see what temporary tables exist for the connection.

SHOW TABLES
doesnt list the temp tables for the connection. Is there some other command
i can issue?

thanks
sean peters
[EMAIL PROTECTED]


sql, 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

Replace query question

[speters]

If i use a replace query, and there isn't an error, then either
a new row was inserted, or an existing row was updated.

The primary key in the table i'm replacing to is defined as
mprid int not null auto_increment primary key

whether an insert or update actually occurs, i want to get the value of 
mprid that was affected.

if it's a new row, i assume i can use select last_insert_id()
but if its an update, will that work?

thanks,
sean peters
[EMAIL PROTECTED]



-
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

data normalization question

[speters]

Does anyone have or know of any good books that discuss or teach normal
forms in great detail. I'd like to say that
im pretty good with 2NF, i'm looking for info on more advanced
normalizations.

A specific normalization question:
if i have three tables, call them A, B, C is it ever proper to have key
relations that end up like:
A-B-C-A

where X-Y means that records in X are associated with records in Y through
a foreign key type relation.

This seems implicitly wrong, but data doesnt always seem to model itself in
a tree like structure.

sean peters
[EMAIL PROTECTED]

mysql, 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

InnoDB and disk geometry

[speters]

A problem I have with InnoDB tables, which forced me to use MyISAM tables in
a recent project, is as follows:

(I am running MySQL on Solaris, not that it matters much for this problem.)

With MyISAM tables, I can easily get different databases to reside on
separate physical drives.

The base directory for database files is /var/mysql/
Then I mounted a drive at  /var/mysql/db_a
and another at /var/mysql/db_b

for instance. Then databases by the names db_a and db_b reside on
different physical drives.

But with InnoDB, you specify a tablespace file(s) and then the database you
create go into
those files. I cant figure out any reasonable way to tie specific databases
to specific drives,
except by running a mysqld server for each drive I want a database on.
For large databases, proper planning of disk geometry can have a significant
effect
on performance. I very much like InnoDB, but can't seem to get around this
problem.


Any insight would be greatly appreciated.

sean peters
[EMAIL PROTECTED]



-
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

InnoDB enum and set datatypes

[speters]

I was wondering about the Enum and Set column types in InnoDB tables.
I was able to create an InnoDB table with an enum column, but i
get errors when trying to insert values into that column.

I'm thinking that InnoDB doesnt support those data types, but the MySQL
interface to InnoDB may have some not quite consistent behavior.

no biggie, just wondering if anyone has encountered this.

sean peters
[EMAIL PROTECTED]



-
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

ORDER BY question

[speters]

I am performing a query along the lines of the following:

SELECT DISTINCT property.Internal_ID
FROM property, owner_names
WHERE property.Internal_ID = owner_names.Internal_ID
AND [ other conditions ]
ORDER BY owner_names.Name

Without the order by clause this is a pretty quick query, but with it,
things
slow down considerably. The query then takes 5-10 times as long. :(
the property table has 1,000,000+ records
and there are an average of about 1.4 owner_names records for each property
record

I'm not exactly sure what happens with the ORDER BY clause, because
if a property record has more than one owner_name record associated with
it, how does MySQL decide which to use for sorting?

I'd guess that if there are more than one, it will take the first one in
sort order.

The owner_names table also has a field named Display_Order, which determines
in what order to display the names for mailing labels, etc.
I'd really like the ORDER BY to only consider records with Display_Order =
1,
but the SELECT to consider all owner_name records.

I know you can put some types of expressions in an order by clause, but i
don't
know how to express the above concept in SQL.

thanks
sean peters
[EMAIL PROTECTED]



-
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

Answer: how to get 64 indexes

[speters]

After many headaches, rantings, and emails, i have FINALLY figured out how
to get 64 indexes out
of MySQL tables.

There are 3 changes you need to make to the source code.

in file mysql_priv.h
change the line:typedef ulong key_map
to:typedef ulonglong key_map


in file include/myisam.h
change:#define MI_MAX_KEY 32
to:#define MI_MAX_KEY 64


in file include/unireg.h
change:#define MAX_KEY 32
to:#define MAX_KEY 64

then recompile mysqld.

I think its a sad situation that i could find numerous references to using
64 keys, none
more specific than: you need to change some things in the source code
or you need to change some things in myisam.h and unireg.h, and some other
things

regards
sean peters
[EMAIL PROTECTED]



-
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

InnoDB tables max number of keys

[speters]

Is there an upper limit on the number of indexes in InnoDB tables in MySQL?
I can't find anything about it in their documentation at www.innodb.com

I am designing a database where one table has around 70 keys so far.
yes, it's normal, and all of those keys are commonly searched on.

thanks
sean peters
Senior Programmer, WireData Inc.
[EMAIL PROTECTED]






-
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

64 indexes in MyISAM tables

[speters]

I've read that it is possible to use 64 indexes in MyISAM tables in MySQL.
I haven't been able to find any documentation on how to actually do this
though.

any references to appropriate documentation would be greatly appreciated.

regards
Sean Peters
[EMAIL PROTECTED]



-
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

[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