Menu
▾
▴
sqlgrey-users
|
From: Steffen P. <swp...@am...> - 2005-09-13 20:23:44
|
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,=20 I would like to thank you first for the sqlgrey development. It has been a huge asset to Amherst College.=20 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.=20 mysql> SELECT * FROM connect WHERE src =3D '205.231.86';=20 +-------------+-------------------------+------------+------------------ ------+---------------------+=20 | sender_name | sender_domain | src | rcpt | first_seen |=20 +-------------+-------------------------+------------+------------------ ------+---------------------+=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 06:16:01 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 04:02:40 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 12:06:00 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 17:12:41 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 17:12:41 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 16:51:13 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 16:51:13 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 05:26:01 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 02:56:22 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 13:12:41 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 21:06:00 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 08:46:00 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 10:59:21 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-13 10:59:21 |=20 | nobody | designjet-online.hp.com | 205.231.86 | mkf...@am... | 2005-09-12 19:59:21 |=20 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?=20 Should I trash the connect table?=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: 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 |
|
From: Lionel B. <lio...@bo...> - 2005-09-14 07:55:34
|
Steffen Plotner wrote the following on 14.09.2005 05:53 : >Hi Lionel, > >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? > >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. > >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 > >Should I be running the 1.6.5 version (stable version)? > > If you don't want to be a beta tester, yes :-) I don't see any obvious problem with the software versions, I'll take time to check the code to find out if something introduced a bug in 1.7.x and I'll be back to you. Lionel. |
|
From: Steffen P. <swp...@am...> - 2005-09-14 13:07:53
|
Hi Lionel, I have reverted to sqlgrey-1.6.5.tar.bz2 and within minutes of restarting postfix/sqlgrey I can see duplications in the connect table. The dump below shows the duplications in the count column on the far right (duplications show with a count > 1). I wonder if anybody else sees this kind of behavior? 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- | 168.159.186 | 1 | | -undef- | -undef- | 200.228.0.130 | 1 | | -undef- | -undef- | 203.92.82.98 | 1 | | -undef- | -undef- | 205.254.25.211 | 1 | | aagbe.aagbe | brantbrant.net | 218.187.100.249 | 1 | | aagnl.aagnl | alvaalva.com | 218.187.100.249 | 1 | | account | chase.com | 62.193.214.159 | 4 | | admin | fivecolleges.edu | 24.60.30.10 | 1 | | antifraud | chase.com | 62.193.214 | 1 | | b.dealchamp.0-5e1cf85-519f.amherst.edu.-dacetto | 22.asp070.com | 69.6.65 | 1 | | confirm | chase.com | 62.193.214.159 | 4 | | greggfi | fusemail.com | 221.141.131.14 | 1 | | morgantnwpaxczqu | verizon.net | 70.22.253.236 | 1 | | mrimmer | unb.ca | 206.47.199 | 1 | | nmkidiqafukyo | go.com | 220.190.37.160 | 1 | | north | takuyakimura.com | 201.34.80.220 | 1 | | rtertee3e | ert354tg.dyndns.org | 61.111.231.68 | 1 | | sara.goldstein | teachforamerica.org | 216.82.244 | 1 | | service | chase.com | 62.193.214.159 | 4 | | support | chase.com | 62.193.214 | 1 | | support | chase.com | 62.193.214.159 | 1 | | swig | wigco.com | 207.190.209 | 1 | | symantecsmtpgateway | timetrak.com | 69.223.125 | 1 | +-------------------------------------------------+--------------------- +-----------------+-------+=20 Steffen -----Original Message----- From: sql...@li... [mailto:sql...@li...] On Behalf Of Lionel Bouton Sent: Wednesday, September 14, 2005 3:55 AM To: sql...@li... Subject: Re: [Sqlgrey-users] Duplicate connect records Steffen Plotner wrote the following on 14.09.2005 05:53 : >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 >Should I be running the 1.6.5 version (stable version)? > =20 > If you don't want to be a beta tester, yes :-) I don't see any obvious problem with the software versions, I'll take time to check the code to find out if something introduced a bug in 1.7.x and I'll be back to you. 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 |
|
From: Lionel B. <lio...@bo...> - 2005-09-14 13:52:55
|
Steffen Plotner wrote the following on 14.09.2005 15:07 : >Hi Lionel, > >I have reverted to sqlgrey-1.6.5.tar.bz2 and within minutes of >restarting postfix/sqlgrey I can see duplications in the connect table. >The dump below shows the duplications in the count column on the far >right (duplications show with a count > 1). > > Is there any way you could switch to another database ? PostgreSQL (provided with your distribution and probably the most standard-compliant database supported by SQLgrey) for example. Testing MySQL 4.1.10a will be a long process for me (not in Portage) and I'm wondering if it could be an odd 'INTERVAL' syntax bug. MySQL is known for its quirkness in some places, changes of behaviour between versions (already had to modify SQLgrey to take one into account) and there already is a specific case for MySQL in the INTERVAL handling. If the bug exists with PostgreSQL too it would surprise me and I've not yet a lead to what would be responsible in this case. If PosgreSQL solves your problem, then I'll ask you to run some SQL commands on your MySQL installation to isolate the problem. Lionel. |
|
From: Michael S. <Mic...@lr...> - 2005-09-14 14:43:25
|
On Wed, 14 Sep 2005, Steffen Plotner wrote: > Hi Lionel, > > I have reverted to sqlgrey-1.6.5.tar.bz2 and within minutes of > restarting postfix/sqlgrey I can see duplications in the connect table. > The dump below shows the duplications in the count column on the far > right (duplications show with a count > 1). > > I wonder if anybody else sees this kind of behavior? > > Hi Steffen, I don't think there is a problem. It seems you forgot that in table connect 4 items are stored sender_name, sender_domain, src AND rcpt. If an email goes to several recipients than you get several entries with stored sender_name, sender_domain and src. Just use select * from connect where src = "IP-ADDR" order by rcpt; with IP-ADDR replaced by one of the entries where count > 1. This should sjow that the difference in the entries are in field rcpt. Michael Storz ------------------------------------------------- Leibniz-Rechenzentrum ! <mailto:St...@lr...> Barer Str. 21 ! Fax: +49 89 2809460 80333 Muenchen, Germany ! Tel: +49 89 289-28840 |
|
From: Lionel B. <lio...@bo...> - 2005-09-14 14:53:35
|
Michael Storz wrote the following on 14.09.2005 16:43 : >On Wed, 14 Sep 2005, Steffen Plotner wrote: > > > >>Hi Lionel, >> >>I have reverted to sqlgrey-1.6.5.tar.bz2 and within minutes of >>restarting postfix/sqlgrey I can see duplications in the connect table. >>The dump below shows the duplications in the count column on the far >>right (duplications show with a count > 1). >> >>I wonder if anybody else sees this kind of behavior? >> >> >> >> > >Hi Steffen, > >I don't think there is a problem. It seems you forgot that in table >connect 4 items are stored sender_name, sender_domain, src AND rcpt. >If an email goes to several recipients than you get several entries with >stored sender_name, sender_domain and src. Just use > >select * from connect where src = "IP-ADDR" order by rcpt; > >with IP-ADDR replaced by one of the entries where count > 1. This should >sjow that the difference in the entries are in field rcpt. > > The first table extract showed several entries for the same RCPT... As a side note, there isn't a UNIQUE INDEX on the entries in this table (which would have prevented even a bug ins SQLgrey to create multiple entries), because... MySQL doesn't support them on such long rows :-( Lionel. |
|
From: Lionel B. <lio...@bo...> - 2005-09-14 15:16:12
|
A quick note: is there anything obvious returned by a diff between the sqlgrey.conf files on the two greylisting servers? You could have surprising problems if they aren't synchronised properly. Lionel. |
|
From: Steffen P. <swp...@am...> - 2005-09-14 15:24:02
|
Hi Lionel, I use a shell script using scp to sync the sqlgrey.conf files on both servers. I just checked them and they match.=20 Steffen=20 -----Original Message----- From: sql...@li... [mailto:sql...@li...] On Behalf Of Lionel Bouton Sent: Wednesday, September 14, 2005 11:16 AM To: sql...@li... Subject: Re: [Sqlgrey-users] Duplicate connect records A quick note: is there anything obvious returned by a diff between the sqlgrey.conf files on the two greylisting servers? You could have surprising problems if they aren't synchronised properly. 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 |
|
From: Steffen P. <swp...@am...> - 2005-09-14 18:51:27
|
Hello Lionel,
Yesterday, I really did have duplications for whatever reason I have not
determined yet. The duplications I thought I had today were incorrectly
queried for. The query should have been:
select sender_name, sender_domain, src,
count(sender_name+sender_domain+src+rcpt) as count=20
from connect=20
group by sender_name, sender_domain, src, rcpt=20
having count(sender_name+sender_domain+src+rcpt) > 1;
Which includes the rcpt field in the group and I have NO duplications.
So, that leaves us with why did I have duplications.
Would you mind explaining the syntax you use in your perl code (I use
perl a bit, however I admit that I am not totally clear about the syntax
below:), in particularly the $#$ part, I understand that the $result is
contains a reference:
if ($#$result !=3D 0) {
return 0; # not a single entry
} else {
return 1; # at least one entry
}
Thinking that it might mean the number of records (?) I wrote a little
script to check this:
' the query SHOULD return ONE record (at least in my connect table, just
substitute the value below...
my $sth =3D $dbconn->prepare("SELECT * FROM connect WHERE sender_name =
=3D
'zwsfkz'");
my $result =3D $sth->fetchall_arrayref();
print "$#$result \n";
$sth->execute();
my $count=3D0;
while ( my $row =3D $sth->fetchrow_hashref() )
{
$count++;
}
print "$count\n";
And I find that the first value is always one less than the actual
count. Hmmm.=20
-----Original Message-----
From: sql...@li...
[mailto:sql...@li...] On Behalf Of Steffen
Plotner
Sent: Wednesday, September 14, 2005 11:24 AM
To: sql...@li...
Subject: RE: [Sqlgrey-users] Duplicate connect records
Hi Lionel,
I use a shell script using scp to sync the sqlgrey.conf files on both
servers. I just checked them and they match.=20
Steffen=20
-----Original Message-----
From: sql...@li...
[mailto:sql...@li...] On Behalf Of Lionel
Bouton
Sent: Wednesday, September 14, 2005 11:16 AM
To: sql...@li...
Subject: Re: [Sqlgrey-users] Duplicate connect records
A quick note: is there anything obvious returned by a diff between the
sqlgrey.conf files on the two greylisting servers? You could have
surprising problems if they aren't synchronised properly.
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
-------------------------------------------------------
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
|
|
From: Lionel B. <lio...@bo...> - 2005-09-14 19:30:53
|
Steffen Plotner wrote the following on 14.09.2005 20:51 :
>Hello Lionel,
>
>Yesterday, I really did have duplications for whatever reason I have not
>determined yet. The duplications I thought I had today were incorrectly
>queried for. The query should have been:
>
>select sender_name, sender_domain, src,
>count(sender_name+sender_domain+src+rcpt) as count
>from connect
>group by sender_name, sender_domain, src, rcpt
>having count(sender_name+sender_domain+src+rcpt) > 1;
>
>Which includes the rcpt field in the group and I have NO duplications.
>
>So, that leaves us with why did I have duplications.
>
>Would you mind explaining the syntax you use in your perl code (I use
>perl a bit, however I admit that I am not totally clear about the syntax
>below:), in particularly the $#$ part, I understand that the $result is
>contains a reference:
>
> if ($#$result != 0) {
> return 0; # not a single entry
> } else {
> return 1; # at least one entry
> }
>
>Thinking that it might mean the number of records (?) I wrote a little
>script to check this:
>
>' the query SHOULD return ONE record (at least in my connect table, just
>substitute the value below...
>
>my $sth = $dbconn->prepare("SELECT * FROM connect WHERE sender_name =
>'zwsfkz'");
>
>my $result = $sth->fetchall_arrayref();
>print "$#$result \n";
>
>$sth->execute();
>my $count=0;
>while ( my $row = $sth->fetchrow_hashref() )
>{
> $count++;
>}
>print "$count\n";
>
>And I find that the first value is always one less than the actual
>count. Hmmm.
>
>
>
I just realized that I introduced a bug not so long ago. It seems you
are the first to be hit and you were on the right track above (the logic
is now flawed when deciding if an entry in connect must be created).
SQLgrey serializing each message processing the bug can only hit
installs with multiple greylisting servers: the symptom is mail being
delayed indefinitely (ouch!). It's a race condition that can be hard to
hit : there are 2 cases I can think of :
- the other server must try at least two MXs at the same time without
waiting for the first answer (can't imagine why one normal SMTP server
would try the same delivery on every MX, only SPAM sources might try that).
- one server (or several in the same class-C) delivers several messages
from the same source to the same destination at the exact same time
(given the observed timings, far less than a second apart) and uses
different MX for that.
Until recently, the put_in_connect function added as a precaution a
"DELETE FROM connect ..." cleaning entries that might conflict with the
one being inserted. In order to save a SQL query and speed SQLgrey a
little bit, I removed some dependencies in the code to the fact that no
identical entries (save from the timestamp) must be in connect at the
same time but it seems I forgot to adapt the logic in some places.
Expect a 1.6.6 release with the bugfix shortly tonight.
Lionel.
|
|
From: Lionel B. <lio...@bo...> - 2005-09-13 23:16:46
|
Steffen Plotner wrote the following on 13.09.2005 22:23 : > Hello, > > For the time being I deleted the contents of the connect table and > within minutes I can see duplications: > > select sender_name, sender_domain, src, > count(sender_name+sender_domain+src) from connect group by > sender_name, sender_domain, src; > > 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. > > 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 = '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. |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X