List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:July 20 2005 4:51pm
Subject:Re: Conditional insert or update.
View as plain text  
Ok, 

The entire table looks like this:

ip | hostname | mac_current | mac_change | port_current | port_change

The IP addresses are harvested via netflow (a different table) and or
arpwatch (an event). This new table is to augment the current
information that is within the netflow tables but specifically
targetting host info.

There is a shell script that runs every 5 minutes, or as triggered by
arpwatch. The former simply selects all the addresses from the netflow
table and pairs them up with it's MAC address which are extracted from
the flat-file arpwatch database. If arpwatch sees a new address it
will also input the ip/mac as it occurs. The MAC addresses are then
used as arguments for snmp lookups on the switches which will return
which port they are currently plugged into.

In the event that a known mac appears on a different port or belongs
to a different address I want to make note of the change. I just
thought it would be easier to compare the mac_current mac_change
port_current port_change via a select query as opposed to having to do
full selects on the entire table then compare the entries before I
plug them in.

For example, the on duplicate key could work if it was available for
update. It would see that that the ip exists (primary key) which would
mean that mac_current already exists so update mac_new.

Or something like that.. :)

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=c+1;

On 7/20/05, Gleb Paharenko <gleb.paharenko@stripped> wrote:
> Hello.
> 
> 
> 
> 
> 
> Please, provide more information on your's application logic.
> 
> To catch events which occurs for the table use triggers. See:
> 
>   http://dev.mysql.com/doc/mysql/en/triggers.html
> 
> 
> 
> Unfortunately they're available only in MySQL 5.0.x, which is still
> 
> beta.
> 
> 
> 
> 
> 
> Paul Halliday <paul.halliday@stripped> wrote:
> 
> > Hi,=20
> 
> >
> 
> > I have a table that looks something like this:
> 
> >
> 
> > ip | mac_current | mac_change
> 
> >
> 
> > now if I have an entry that looks like this
> 
> >
> 
> > 10.0.0.1 | 11:11:11:11:11:11:11 | NULL
> 
> >
> 
> > If the next time the script runs and the mac has changed, how can I
> 
> > catch this and put the new MAC into mac_change?
> 
> >
> 
> > Ultimately, I would also like to roll the values too. For example if
> 
> > the MAC changes again, put mac_change into mac_current...
> 
> >
> 
> >
> 
> > Thanks.
> 
> > --=20
> 
> >
> 
> > Paul Halliday
> 
> >
> 
> > "Diplomacy is the art of saying "Nice doggie!" till you can find a rock."
> 
> >
> 
> 
> 
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>    __  ___     ___ ____  __
>   /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
>  / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
>        <___/   www.mysql.com
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 


-- 
_________________
Paul Halliday
http://dp.penix.org

"Diplomacy is the art of saying "Nice doggie!" till you can find a rock."
Thread
Conditional insert or update.Paul Halliday20 Jul
  • Re: Conditional insert or update.Gleb Paharenko20 Jul
    • Re: Conditional insert or update.Paul Halliday20 Jul