rasqlinsert and SQL TRIGGERS

[David Edelman]

Carter,
 
Is the rewritten version of rasqlinsert available on the website?
 
--Dave
 
From: Carter Bullard [mailto:carter at qosient.com] 
Sent: Sunday, June 30, 2013 11:11 AM
To: Matt Brown
Cc: David Edelman; argus-info at lists.andrew.cmu.edu
Subject: Re: [ARGUS] rasqlinsert and SQL TRIGGERS
 
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
<mailto: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:iname.com at lists.andrew.cmu.edu> 
[mailto:argus-info-bounces+dedelman <mailto:argus-info-bounces%2Bdedelman>
=iname.com at lists.andrew.cmu.edu <mailto: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 <mailto: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
<mailto: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
<mailto: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/7ba2ddc8/attachment.html>