rasqlinsert and SQL TRIGGERS

Fri Jun 28 19:49:53 EDT 2013

I'm not sure if you need to do this in near real time. If not, you can do
something pretty down, dirty, and very fast on a regular basis:

mysql> create table arguspairs (
saddr varchar(64) not null,
daddr varchar(64) not null,
firstTime double(18,6) unsigned, 
primary key (saddr, daddr)
);

mysql> insert ignore into arguspairs select saddr, daddr, ltime from
argus.matrix_2013_06_05;
Query OK, 449 rows affected, 1 warning (0.06 sec)
Records: 496  Duplicates: 47  Warnings: 1

You can even add a bit of insert logic to update a field showing the most
recent incident of the address pair.

The warning has to do with binary replication and insert ignore not being
safe on the remote system. 

--Dave

From: Matt Brown [mailto:matthewbrown at gmail.com] 
Sent: Friday, June 28, 2013 6:58 PM
To: David Edelman
Cc: Carter Bullard; argus-info at lists.andrew.cmu.edu
Subject: Re: [ARGUS] rasqlinsert and SQL TRIGGERS

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/20130628/13d519f2/attachment.html>