Menu
▾
▴
RE: [Sqlgrey-users] Duplicate connect records
|
From: Steffen P. <swp...@am...> - 2005-09-14 03:53:50
|
Hi Lionel, =20 Thank you for the info about the connect table - good to know that I am = on the right track. What do we have you ask? =20 We run RedHat AS4 on grey1.amherst.edu and grey2.amherst.edu servers. = The mysql database backend is located on another server also running = RedHat AS4 (no mysql replication). All machines are sync'd with NTP to = our DNS servers. =20 Versions: sqlgrey-1.7.0-1.noarch.rpm from sourceforge, using rpmbuild -ta = sqlgrey-<version>.tar.bz2 IO-Multiplex-1.08.tar.gz Net-Server-0.88.tar.gz perl-5.8.5 perl-DBI-1.40 mysql-4.1.10a postfix-2.1.5=20 =20 Should I be running the 1.6.5 version (stable version)?=20 =20 Steffen =20 ________________________________=20 Steffen Plotner=20 Systems Administrator/Programmer=20 Systems & Networking=20 Amherst College=20 PO BOX 5000=20 Amherst, MA 01002-5000=20 Tel (413) 542-2348=20 Fax (413) 542-2626=20 Email: swp...@am...=20 ________________________________=20 ________________________________ From: sql...@li... on behalf of Lionel = Bouton Sent: Tue 9/13/2005 7:16 PM To: sql...@li... Subject: Re: [Sqlgrey-users] Duplicate connect records Steffen Plotner wrote the following on 13.09.2005 22:23 : > Hello, >=20 > For the time being I deleted the contents of the connect table and > within minutes I can see duplications: >=20 > select sender_name, sender_domain, src, > count(sender_name+sender_domain+src) from connect group by > sender_name, sender_domain, src; >=20 > mysql> select sender_name, sender_domain, src, > count(sender_name+sender_domain+src) as count from connect group by > sender_name, sender_domain, src; > > = +---------------------------------------------------+--------------------= -------+-----------------+-------+ > | sender_name | > sender_domain | src | count | > = +---------------------------------------------------+--------------------= -------+-----------------+-------+ > | -undef- | > -undef- | 12.36.12 | 1 | > | -undef- | > -undef- | 217.70.144.197 | 1 | > | admin | > amherst.edu | 24.34.176.205 | 1 | > | salinas | > cableinet.co.uk | 209.60.100.70 | 8 | > | skotowych | > utpress.utoronto.ca | 128.100.205 | 1 | > | stockdal | > rilcomfg.com | 200.168.17.124 | 1 | > | wendy | > marketriskadvisory.com | 69.49.238 | 1 | > = +---------------------------------------------------+--------------------= -------+-----------------+-------+ > One example above is the salinas sender_name. >=20 > Steffen > > = ------------------------------------------------------------------------ > *From:* sql...@li... > [mailto:sql...@li...] *On Behalf Of > *Steffen Plotner > *Sent:* Tuesday, September 13, 2005 3:50 PM > *To:* sql...@li... > *Subject:* [Sqlgrey-users] Duplicate connect records > > Hi Lionel, > > I would like to thank you first for the sqlgrey development. It has > been a huge asset to Amherst College. > > We have encountered an issue today with some emails coming from so far > 2 specific IP addresses that are being continuously being greylisted. > > A quick view of the connect table reveals duplicate connect records. > > mysql> SELECT * FROM connect WHERE src =3D '205.231.86'; > = +-------------+-------------------------+------------+-------------------= -----+---------------------+ > > | sender_name | sender_domain | src | > rcpt | first_seen | > = +-------------+-------------------------+------------+-------------------= -----+---------------------+ > > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 06:16:01 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 04:02:40 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 12:06:00 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 17:12:41 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 17:12:41 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 16:51:13 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 16:51:13 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 05:26:01 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 02:56:22 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 13:12:41 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 21:06:00 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 08:46:00 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 10:59:21 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-13 10:59:21 | > | nobody | designjet-online.hp.com | 205.231.86 | > mkf...@am... | 2005-09-12 19:59:21 | > > > I have put 205.231.86.62 into whitelist.local - mail is going through > from that IP. I have enabled SQL queries in mysql and can see that > SELECT statements that check if there was a connection within the > previous 5 minutes. Then within the last 24 hours and before 5 minutes > ago. Upon that it inserts a connect record. I have debugged those > statements and found that they produce records. > > What are your recommendations? Have you seen this before? > Never! As you saw in the SELECT statements, SQLgrey begins to check if a previous entry exists, then if one exists it is supposed (and never was witnessed to do something else) to either switch to the from_awl table and let the mail pass or signal a "reconnect to early" without creating connect entries depending on the result of its checks. Which SQLgrey/MySQL versions are you using? How did you get SQLgrey (pre-packaged or directly from sourceforge)? Is anything unusual about your setup (MySQL replication, multiple systems whose clocks aren't synchronised with NTP, ...)? > Should I trash the connect table? > If it becomes too large (to the point where performance might suffer on your setup), yes, otherwise you should let SQLgrey do the cleaning. Lionel. ------------------------------------------------------- SF.Net email is sponsored by: Tame your development challenges with Apache's Geronimo App Server. = Download it for free - -and be entered to win a 42" plasma tv or your very own Sony(tm)PSP. Click here to play: http://sourceforge.net/geronimo.php _______________________________________________ Sqlgrey-users mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlgrey-users |
