rasqlinsert and SQL TRIGGERS

Tue Jul 2 16:17:54 EDT 2013

Comments inline.
Carter

On Jul 2, 2013, at 2:22 PM, Matt Brown <matthewbrown at gmail.com> wrote:

> 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?).

If we can figure out the syntax, I have no problem supporting a file of SQL commands.
Variables like $table, $db, $dbuser, $dbpass...  can easily be supported, as rasqlinsert()
has these internal variables already.  If you can show example SQL commands, we
can figure out what will need to be done.

> 
> 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.
> 

This is trivial to implement.   To do exactly as you want, with good performance, you may need to write your own client, and structure your own master IP matrix table, but I can do what you're interested in, with the existing code set.  
Would need to add some code and an option to generate the event (new ip address pair x.y.z.w and w.y.z.x).

Now, creating an effective system based on these types of alarms is the more interesting part. 

> 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.

autoid is a mysql function.  not likely that it can be useful for multiple tables.
All argus records have a seq number, though, that could be useful.

> 
> 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/bf8d2062/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/20130702/bf8d2062/attachment.bin>