rasqlinsert and SQL TRIGGERS

[Matt Brown]

Thanks Dave.

It seems that Carter is okay with implementing a method within
rasqlinsert() to handle arbitrary SQL after table creation (as per his last
response).


However, I am attempting to insert records that contain unique saddr+daddr
pairs from the existing [live] table (`argus`.`argusTable_2013_06_28`) into
another table:

DELIMITER |
 CREATE TRIGGER `argus`.`after_argus_insert`
 AFTER INSERT ON `argus`.`argusTable_2013_06_28` FOR EACH ROW
 BEGIN
 SET @a = UNIX_TIMESTAMP(sysdate(6));
 INSERT INTO argus.historic_argus_saddrdaddr(inserttime, saddr, daddr)
 (
 SELECT @a, NEW.saddr, NEW.daddr
 FROM (SELECT NEW.saddr, NEW.daddr) as derived
 WHERE NOT EXISTS (SELECT saddr, daddr FROM argus.historic_argus_saddrdaddr
where historic_argus_saddrdaddr.saddr = NEW.saddr and
historic_argus_saddrdaddr.daddr
= NEW.daddr)
 );
 IF ROW_COUNT() > 0 THEN
 UPDATE argus_status SET saddrdaddrlastmodtime=@a;
 END IF;
 END;
 |
 DELIMITER ;

I just added this today at close of business, and quickly monitored (`ps -o
rss`) of rasqlinsert, and didn't see much of a jump.


This brings up another point...

Carter: what do you think of including a count of buffered bytes or
buffered records-to-be-inserted on a -D (level)?
Maybe write the info to syslog every N seconds, to include the per-second
count, the per N/second average, and the per N count?  I ask because I'm
not sure how to accurately keep an eye on how much badness this TRIGGER
will introduce (is tracking `ps -o rss` over time enough)?


Am I way off base with regards to my methodology for tracking unique
saddr/daddr pairs?


Thanks very much,

Matt


On Fri, Jun 28, 2013 at 6:44 PM, David Edelman <dedelman at iname.com> wrote:

> Matt,
>
> A trigger is defined on a table so by definition, a table has to exist
> before the trigger can be created. A trigger exists only for the lifetime
> of
> its associated table. If you drop the table, you drop the trigger. If you
> rename a table that has an associated trigger, the trigger is updated to
> reflect the new table name.
>
> Can you give a more concrete example of something that you would like to do
> with a trigger? There may be another way to achieve the same goal.
>
> --Dave
>
> -----Original Message-----
> From: argus-info-bounces+dedelman=iname.com at lists.andrew.cmu.edu
> [mailto:argus-info-bounces+dedelman=iname.com at lists.andrew.cmu.edu] On
> Behalf Of Matt Brown
> Sent: Thursday, June 27, 2013 5:56 PM
> To: Carter Bullard
> Cc: argus-info at lists.andrew.cmu.edu
> Subject: Re: [ARGUS] rasqlinsert and SQL TRIGGERS
>
> Thanks for the fast reply.
>
> File or argument based would be great!
>
> Do you have experience using TRIGGERs on your target tables?
>
>
> Thanks,
>
> Matt
>
>
>
> On Jun 27, 2013, at 5:39 PM, Carter Bullard <carter at qosient.com> wrote:
>
> > I've been working on rasqlinsert() to fix some problems, and have some
> fixes ready after this weekend.
> > If there are some post table creation  mysql statements you want to make,
> for each table,
> > we can just attach a file that has the statements in them, and I'll run
> them with each new table.
> > Or something like that could work?
> >
> > Carter
> >
> >
> > On Jun 27, 2013, at 5:30 PM, Matt Brown <matthewbrown at gmail.com> wrote:
> >
> >> Hello,
> >>
> >> I'm interested in attaching TRIGGERs to the table(s) created by
> rasqlinsert.
> >>
> >> I've spent some time hunting down various solutions to the problem of
> >> creating TRIGGERs upon table creation, including reading that you
> >> can't attach TRIGGERs to tables in the information_schema schema.
> >>
> >> Carter/Dave/etc: Do you all have any idea on how something like this
> >> can be handled?  I began researching something along the lines of
> >> `CREATE TABLE n LIKE x`, but it doesn't appear that `LIKE` carries
> >> TRIGGERs from the template table.
> >>
> >>
> >> Any assistance is appreciated.
> >>
> >>
> >> Thanks,
> >>
> >> Matt
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20130628/d3124028/attachment.html>