rasqlinsert and SQL TRIGGERS

Sun Jun 30 11:16:30 EDT 2013

Oh I forgot to mention that the new rasqlinsert() is still being tested.
I'll upload it tomorrow, when I get back to the city.
Carter 

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/20130630/00542b73/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/00542b73/attachment.bin>