rasqlinsert and SQL TRIGGERS

[Carter Bullard]

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/20130630/7b6dcb84/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 6837 bytes
Desc: not available
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20130630/7b6dcb84/attachment.bin>