RE: 'mysql_error: Duplicate entry', what am I doing wrong?

["Mark E. Donaldson" <markee () bandwidthco com>]

I ran into the same problem after upgrading to 2.1.1.  With 6 snort sensors
all logging to one MySQL server, I spent over a week troubleshooting this.
Finally I determined it was because I had two MySQL database output Pluggins
activated (both log & alert).  After commenting out the "alert" pluggin line
on all my sensors, the duplication finally stopped.  There was one good
aspect to all of this:  I thought I knew and understood snort well before
this. Needless to say, I learned much more.

-----Original Message-----
From: snort-users-admin () lists sourceforge net
[mailto:snort-users-admin () lists sourceforge net] On Behalf Of JP Vossen
Sent: Thursday, March 18, 2004 9:47 PM
To: Snort Users List
Subject: [Snort-users] 'mysql_error: Duplicate entry', what am I doing
wrong?

I'm getting between 20 and 90 of the following per day.  I have read the
archives, especially [1] and Googled and it didn't help.  The problem
started when I upgraded to 2.1.1-RC1 (on RedHat 8) but I made substantial
configuration changes at the same time, so it's been difficult to track
down.
I have also since upgraded to 2.1.1 gold.  I AM running 2 snort processes
[2], but only 1 is writing to the DB.  However that 1 has 4 database lines,
the default alert and 3 custom log rule types.  The odd construction [3] is
to try and force Snort to apply rules in a certain order [4] for my
honeypot.

Mar  6 21:08:40 TheHost snort: database: mysql_error: Duplicate entry
'13-39575' for key 1 SQL=INSERT INTO event (sid,cid,signature,timestamp)
VALUES ('13', '39575', '52', '2004-03-06 21:08:40-05')

I suspect an interaction between my 1 alert and 3 log rule types, but I
can't track it down.  Part of my confusion is that if I am reading the above
correctly, the third value (52 in the example above) should be the Sig ID.
[It's seems like a really BAD idea for 'sid' to mean two different things,
but above it seems to mean sensor ID where usually it means signature ID.
:-( ] What the heck is signature 52?  I can't find it in the rules, and all
the signatures I see are > 100 (as noted in the User Manual and as
expected).

The Sensor ID is, in fact, 13.  The CIDs seem to be OK, and as I said I only
get a few per day out of an average of about 1,500 alerts per day.  Since
this is a honeypot I log EVERYTHING.  I think that the 'Duplicate entry'
issue happens when I get a packet that matches both my 'everything' and a
'real'
rule.  But I can't tell, since the 'signature' seems bogus.

Out of 1,093 errors, the 'signatures' I got are these.  What ARE they?
    Cnt   'signature'
    605  '52'
    151  '57'
     95  '59'
     76  '58'
     73  '53'
     42  '61'
     31  '54'
      6  '62'
      5  '63'
      3  '64'
      3  '60'
      1  '66'
      1  '65'
      1  '46'

What [fundamental, obvious thing] am I missing?

Thanks,
JP
Long supporting details below this line.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[1] http://marc.theaimsgroup.com/?l=snort-users&m=107574518312594&w=2


[2] /etc/snort-hpt# ps auwx | grep snort
root     10010  0.0 33.1 45568 41876 ?       S    Mar06   0:15
/usr/sbin/snort-hpt -D -i eth1 -c /etc/snort-hpt/snort.conf -l /var/l
root     10032  0.0 35.1 48308 44528 ?       S    Mar06   1:10
/usr/sbin/snort-int -D -i eth0 -c /etc/snort-int/snort.conf -l /var/l
root     10743  0.0  0.3  1436  456 pts/0    S    00:00   0:00 grep snort


[3] /etc/snort-hpt# grep -B4 -A1 '^[[:space:]]*output database'
/etc/snort-hpt/snort
.conf /etc/snort-int/snort.conf
/etc/snort-hpt/snort.conf-# output log_unified: filename snort.log, limit
128
/etc/snort-hpt/snort.conf-
/etc/snort-hpt/snort.conf-
/etc/snort-hpt/snort.conf-#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~
/etc/snort-hpt/snort.conf:output database: alert, mysql, dbname=snort
host=TheHost user=TheUser password=ThePassWD sensor_name=Snorter2_JP
detail=full ignore_bpf=yes /etc/snort-hpt/snort.conf-output alert_syslog:
LOG_AUTH LOG_ALERT
--
/etc/snort-hpt/snort.conf-# Custom rule to allow rule ordering so that rules
trigger in the order needed.
/etc/snort-hpt/snort.conf-ruletype payload /etc/snort-hpt/snort.conf-{
/etc/snort-hpt/snort.conf- type log
/etc/snort-hpt/snort.conf: output database: log, mysql, dbname=snort
host=TheHost user=TheUser password=ThePassWD sensor_name=Snorter2_JP
detail=full ignore_bpf=yes /etc/snort-hpt/snort.conf-}
--
/etc/snort-hpt/snort.conf-# Custom rule to allow rule ordering so that rules
trigger in the order needed.
/etc/snort-hpt/snort.conf-ruletype handshake /etc/snort-hpt/snort.conf-{
/etc/snort-hpt/snort.conf- type log
/etc/snort-hpt/snort.conf: output database: log, mysql, dbname=snort
host=TheHost user=TheUser password=ThePassWD sensor_name=Snorter2_JP
detail=full ignore_bpf=yes /etc/snort-hpt/snort.conf-}
--
/etc/snort-hpt/snort.conf-# Custom rule to allow rule ordering so that rules
trigger in the order needed.
/etc/snort-hpt/snort.conf-ruletype catchall /etc/snort-hpt/snort.conf-{
/etc/snort-hpt/snort.conf- type log
/etc/snort-hpt/snort.conf: output database: log, mysql, dbname=snort
host=TheHost user=TheUser password=ThePassWD sensor_name=Snorter2_JP
detail=full ignore_bpf=yes /etc/snort-hpt/snort.conf-}


[4] /etc/snort-hpt# grep 'config order' snort.conf config order: alert log
payload handshake catchall

------------------------------|:::======|-------------------------------
------------------------------|-
JP Vossen, CISSP              |:::======|         jp{at}jpsdomain{dot}org
My Account, My Opinions       |=========|       http://www.jpsdomain.org/
------------------------------|=========|-------------------------------
------------------------------|-
You used to have to reboot the Windows 9.x series every couple of days
because it would crash.  Now you have to reboot Windows 200x or XP every
couple of days because of a patch.  How is that better or more stable?



-------------------------------------------------------
This SF.Net email is sponsored by: IBM Linux Tutorials Free Linux tutorial
presented by Daniel Robbins, President and CEO of GenToo technologies. Learn
everything from fundamentals to system
administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users




-------------------------------------------------------
This SF.Net email is sponsored by: IBM Linux Tutorials
Free Linux tutorial presented by Daniel Robbins, President and CEO of
GenToo technologies. Learn everything from fundamentals to system
administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users