Re: [Sqlgrey-users] Duplicate connect records

[Lionel B. <lio...@bo...>]

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.