rasqlinsert and SQL TRIGGERS

[Matt Brown]

1) Thanks... I'll look into performance monitoring in general with MySQL.
It's obviously a robust topic.

2) Are you averse to using SQL syntax in the file?  But, yes, variable
replacement would have to be done (specifically with the table name or any
other variant).  You could delimit the variable names with a character that
isn't used in SQL syntax ('|' maybe?).

3) I am trying to create something close-to-realtime that sees when a new
saddr+daddr pair occurs.
The design would be:
a) insert new saddr+daddr pair.
b) also insert an mtime value into a status table.
c) have a client "watch" (check once every few seconds) this status table,
and if mtime is greater than previous mtime, do Y.

I am curious,  using `autoid`, would it also be possible to continue the
incrementing of `id` from table-to-table?  This would be a sort of unique
ID for a given flow record.

Thanks for the pointer to rasqlinsert() to capture unique saddr daddr
pairs.  This would also work in luie of a TRIGGER, how can I do this?
 Please also refer to an email I will send shortly as to what my overall
goal is, as this puts a lot more context to what I am trying to do.

I'm guessing another viable alternative might be to use a VIEW, but this
might be slow when dealing with many records repeatedly(?).


Thanks,

Matt

On Jun 30, 2013, at 11:10 AM, Carter Bullard <carter at qosient.com> wrote:

rasqlinsert() has been rewritten to be more threaded, and to do better
logging.  We do have a database performance debug statement that
you get from -D2, and it periodically tells how many inserts, updates,
cache misses, etc....  Doesn't mysql have some logging for performance?

I don't have any problems supporting anything that makes the database
stuff work better.  So not problem with providing something like a list of
post table formation commands, however, we need a syntax for the file.

Am I simply going to pass whatever strings to a mysql_real_query()
statement?
Or do I need to do any form of variable substitution?

I don't use triggers, primarily because I generally don't have events to
trigger on.  I mostly poll my existing tables looking for specific things,
or
have multiple rasqlinserts() to generate the specific views I want.

For your example, so what are you doing?  Creating a table of unique saddr
daddr
pairs, with a first seen timestamp?  Seems easy enough to do with
rasqlinsert() ?
Or daily with a command line mysql call ?  Always lots of ways to do
stuff....

Carter

On Jun 28, 2013, at 6:58 PM, Matt Brown <matthewbrown at gmail.com> wrote:

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/20130702/071c9a66/attachment.html>