[Snort-users] MySQL and ACID Question (Duplicate Key Entries)

Botwick, Jason (Genworth, Contractor) Jason.Botwick at ...12522...
Tue Oct 26 14:54:04 EDT 2004

I had this problem as well. I think it has to do with the fact that queries
that are used to insert data into the acid_event table in the function
CacheSensor() are not mutually exclusive. Maybe that's obvious to you
already. But my solution was to make them so by altering acid_cache.inc as
/* The following four lines used to be placed just before the definition of
I've moved them up to before $update_sql[0] */
/* Then I added the last line shown here to the SELECT statement for
$update_sql[0] */
   if ( $db->acidGetDBVersion() >= 100 )
      $schema_specific[3] = " (sig_name LIKE 'spp_%') ";
      $schema_specific[3] = " (signature LIKE 'spp_%') ";
  /* TCP events */
  $update_sql[0] =
    "INSERT INTO acid_event (sid,cid,signature,timestamp,
     SELECT event.sid as sid, event.cid as cid, signature, timestamp,
            ip_src, ip_dst, ip_proto,
            tcp_sport as layer4_sport, tcp_dport as layer4_dport".
    FROM event
    INNER JOIN iphdr ON (event.sid=iphdr.sid AND event.cid=iphdr.cid)
    LEFT JOIN tcphdr ON (event.sid=tcphdr.sid AND event.cid=tcphdr.cid)
    WHERE (event.sid = $sid AND event.cid > $cid) AND ip_proto = 6
    AND ( NOT ".$schema_specific[3].") ";

In plain english, the first INSERT was inserting all TCP events, including
ones generated by the stream preprocessor. But then the final query is going
back and trying to insert all stream preprocessor events, including the TCP
events that have already been inserted. 
So, that was my quick fix. I'm not sure it's 100% correct. Maybe a better
way to do it would be the reverse, where you eliminate TCP spp_% events from
the final INSERT statement. Any thoughts?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.snort.org/pipermail/snort-users/attachments/20041026/5fde1230/attachment.html>

More information about the Snort-users mailing list