Re: Replication question...

[Madscientist]

At 04:02 AM 7/4/2003 -0500, woody at nfri dot com wrote:

What kind of traffic volume is generated with replication, our database
has a pretty steady read/update volume throughout the day and its pretty
much 50/50 read/write.  I do plan to offload some of the reads (such as
for daily reports and informational sites to the slave.
According to this, the network traffic over your replication path will be 
about half of the normal traffic to your master plus any duties you 
assigned to your slave. Think of it as every write to the master happening 
again to the slave, so if you have a 50/50 ratio then half of the traffic 
will be duplicated in the network.

In very high availability systems we typically use two networks. The first 
network provides access to services - this would be your current network. 
The second network (you were thinking of using a cross-over cable) would be 
used for administration and backups. Rather than using a cross-over cable 
for your replication, you should consider using a separate network that you 
can eventually build to all of your critical boxes. Plan to use this 
network for all of your admin, backup, and replication mechanisms.

that said, if your system is and will remain small, and if you currently 
could add half again as much traffic to your current network, then you will 
be fine to stay with the one network you have - just be sure you have a 
good switch in place (preferably only one) between your master and primary 
slave servers.

Hope this helps,
_M


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Java/JDBC - Connection refused on Linux

[Madscientist]

At 05:59 PM 6/17/2003 -0700, Jeff Weeks wrote:
I can't access MySQL using Java on Linux!  It's killing my project.   I 
can access that very MySQL from PHP just fine.   I have the same database 
running on Windows so I can develop what I need to.  Just can't run 
anything for our users!!!

I don't think it's the firewall because I get the same error when I run 
locally on the box.

Anyone face this before?   I need HELP.


I've had this kind of thing turn up. It always turned out to be securities 
on the MySQL server. Check that appropriate access is granted to the 
correct IP and/or IPs. Sometimes it's not quite so obvious that this is 
correct. I've never yet had this problem without it being a grant issue.

hope this helps,
_M


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Triggers

[Madscientist]

At 02:46 PM 6/17/2003 -0400, Kerry Colligan wrote:
I think I mentioned this. The problem is, I need reproducible results from
both web interface AND command line. If I implement the triggers in PHP, I
only get the results from the web.
I would recommend that you build application objects in java to connect to 
your MySQL database (this is what we do). In the end you can build a 
command line interface to your objects, or you can wrap them in a gui, or 
you can access them from the web using JSP. The application logic and data 
integrity model is then completely portable (relatively speaking).

I also recommend that you make use of transaction support in innodb tables 
so that your objects can enforce arbitrarily complex interactions without a 
risk of introducing data integrity problems.

This approach may also allow you to port your application to a different 
database server if that should ever be necessary. This is not always so 
easy when relying on embedded trigger and active SQL technologies in some 
databases.

_M

Pete McNeil (Madscientist)
President, MicroNeil Research Corporation 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: soft real-time database

[Madscientist]

At 05:03 PM 6/12/2003 -0600, Chris Webster wrote:
Curtis Maurand wrote:
I'd write a perl script to pool the device and send the data to the database.
Sorry for not being clearer.  I can get the data into the database 
fine.  Assuming new values or rows are added once per second, how would a 
client program go about polling the database to see when a new value was 
ready?  Check # of rows for a column and when it's incremented grab the 
latest value?

--Chris
How about this:

Keep a table with a single row as a status indicator. Whenever you add a 
new row of data, update the status record with a new count of samples. 
(samples = samples + 1)... You may even find other interesting things to 
put in this row for diagnostic monitoring.

Your client application can then check the single row to determine if new 
data is available for analysis. It may even be possible to "prescale" some 
of the analysis in the status indicator row... for example summary 
statistics. Using this model you can also move on to a new status record 
when a new time period begins (or with some other criteria) so that you 
have a record of summary trends available.

Updating a single record is not likely to cause significant loading since 
the same record is always in the database cache and in precisely the same 
location. A prepared statement can also help here.

Hope this helps,

_M

Pete McNeil (Madscientist)
President, MicroNeil Research Corporation.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Question

[Madscientist]

Usually the best way to do this is to store the images in an accessible
directory and then store the URL for the image in the database. It's a
fairly simple exercise also to create an upload function that will associate
the two for you automatically.

Hope this helps,
_M

]-Original Message-
]From: McGraw, Robert--G3(Contractor-ANTEON Corp)
][mailto:[EMAIL PROTECTED]]
]Sent: Tuesday, February 18, 2003 10:24 AM
]To: '[EMAIL PROTECTED]'
]Subject: Question
]
]
]Sir/Ma'am,
]
]I am building a database (using MySQL) consisting of a list of
]emplyees with
]specific and unique information for each.  I want to link an
]employees badge
]number with their photo.  I have photos for all employees in JPG format.
]How can I link the badge number to a photo?  Ideally, I would like to click
]on the badge number and the photo would come up.  Any help is greatly
]appreciated.
]
]Thanks much.
]
]
]Robert D. McGraw
]Anteon Corporation
]Database Administrator
]Battle Simulation Center
]Ft. Hood, TX
]DSN 738 3061 Comm. (254) 288 3061
]
]
]
]-
]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: Fractions

[Madscientist]

You might also consider using two integer type fields - one for the
numerator and another for the denominator. This way storage and recovery are
easy and the mathematics are wide open.
_M

]-Original Message-
]From: MySQL [mailto:[EMAIL PROTECTED]]
]Sent: Thursday, September 19, 2002 7:51 PM
]To: [EMAIL PROTECTED]
]Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
]Subject: Re: Fractions
]
]
]   Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
](http://www.ezmlm.org)
]   X-Sender: [EMAIL PROTECTED]@teton.kitebird.com
]   Date: Thu, 19 Sep 2002 08:54:55 -0500
]   From: Paul DuBois <[EMAIL PROTECTED]>
]   News-Group: list.mysql
]   Reply-To: <[EMAIL PROTECTED]>
]
]   At 20:47 -0500 9/18/02, William Martell wrote:
]   >Hello.
]   >
]   >Can anyone tell me whether we can represent fractions in MySQL
]4.0.  I have
]   >data representing different lengths of objects measured in
]inches.  Can I
]   >load this data as is into a field in MySQL or do I have to
]convert it first
]   >into an floating point interger or decimal value???
]
]   You could store them as strings, but you'd have to interpret them on
]   the client side, most likely.
]
]In which case you might as well store them as reals, with char
]substitution (interpret).  As reals, later when the inevitable desire
]occurs to add different lengths, it will be simple.
]
]-
]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

What's the workaround for this...

[Madscientist]

We've got a sloppy database that we must import to from another sloppy
database. The slop is due to user data entry and process inconsistencies
- that can't be fixed here.

The query we need to do (loosely) is this:

insert into table B
  select SPO, Field2, Field3, etc... from table A 
where A.SPOMangle1 not in (select SPO from table B) and
  A.SPOMangle2 not in (select SPO from table B) and
  A.SPOMangle3 not in (select SPO from table B) and
  ...

If you get the idea - the problem is that any of the incoming order
records from table A might have already been either coded or mangled in
a particular way by the users of the system and we must avoid importing
any duplicate records - so we must search for all of the possible
mangled versions of the SPO (ticket number) and exclude those records
from our import.

Another reason for this craziness is what we call "over sampling" the
import data to account for temporary communications failures in the
incoming stream - That is, we use a sample of the incoming data that is
certain to overlap with data that is already in the database by a number
of days so that missing systems will be automatically recovered once
they rejoin the conversation.

We create the import table A from a utility that captures incoming data
from a POS system and creates the possible mangled versions of the SPO
for convenience in this process.

We're currently doing this on MS*SQL - but we hate that and want to move
to MySQL. (Lots of good reasons - replication, speed, and cost among
them.)

We would like to avoid an interactive process if we can.

Since MySQL doesn't do sub selects - how would we work around this?

Thanks!
_M

  


-
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

[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]>
Troub

Circular replication

[Madscientist]

Hi,

We're doing something weird (what else is new). We're hoping to use
MySQL as the base of a distributed database with peer replication. Most
of the peering and control mechanisms will be in the core application
code, but we're counting on MySQL's ability to replicate for some of the
lower-level peer synchronization mechanisms. It appears from the docs
that most of this will be straightforward, however it is not clear (or I
missed it) if replication works with all table types.

This is important because the issue of complex transactions has recently
come up and we may need to use innodb tables.

It is not entirely clear wether replication will work with innodb
tables.
We're hoping to use MySQL 4.x

Can we build reliable, circular replication with innodb tables in MySQL?

We will have the application pick a single master node for any
particular table and/or database until all nodes are synchronized...
Then the app will vote for a new master if needed.

For example.

   NODE1   NODE2NODE3
   MASTER OF A MASTER OF B  MASTER OF C

...--->[ ABC ]>[ ABC ]->[ ABC ]...

Will this work with innodb.
If not directly, then how?

Thanks in advacnce,
_M


-
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

Installation weirdness.

[Madscientist]

I can't seem to find help for this in the docs or with the search
engine.

I am installing the new 4.x version an rather than run it over 3.23 I
decided to erase the old packages first and start fresh. This is a
RedHat Linux 7.1 box. I am using the RPMs to do this.

After erasing all RPMs from 3.23, I installed the RPMs for 4.xx...

At the end of the process... It claims to attempt restarting MySQL, but
then after a few seconds complains that the startup script in init.d did
not exist.

When I go to find MySQL, only pieces of it are there and none of the
scripts necessary for initializing the database etc...

I checked rpm and it claims the package is installed... (rpm -qa | grep
MySQL)

Frustrated, I started over and removed (rpm -e ... ) the package... Then
installed (rpm -i) the old packages that had been working fine.

I get the same result and broken installation.
It seems that the RPM mechanism is hosed somehow.

Question 1... Am I hosed? -- that is, has RPM somehow become so
corrupted it just won't work anymore?

Question 2... Is there anyway to get this working again without
rebuilding the server from scratch?

Thanks,
_M


-
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