Re: [Sqlgrey-users] Patches for SQLgrey

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

Michal Ludvig a écrit, le 08/24/2009 03:10 PM :
> Lionel Bouton wrote:
>
>   
>> We should fix the case where a spammer sends a message to all MX of a
>> domain at the same time. The problem is that several SQLgrey instances
>> sharing the same database do the following (simplifying a bit) :
>> - check that the message doesn't match a whitelist (no),
>> - check if it is already in the connect table (it isn't),
>> - try to create an entry in the "connect" table.
>> Only one instance can complete the last step, all others get an SQL
>> error (and it's completely normal).
>>     
>
> I don't think this can happen with current SQLgrey as the connect table
> doesn't have a unique or primary index:
>   

I keep forgetting that ! Damn MySQL...

> sub create_connect_table {
>     # Note: no primary key, Mysql can't handle 500+ byte primary keys
>     # connect should not become big enough to make it a problem
>     $self->do("CREATE TABLE $tablename " .
>               '(sender_name varchar(64) NOT NULL, ' .
>               'sender_domain varchar(255) NOT NULL, ' .
>               'src varchar(39) NOT NULL, ' .
>               'rcpt varchar(255) NOT NULL, ' .
>               'first_seen timestamp NOT NULL)')
>         or $self->mydie(...);
> }
>
> sub create_connect_indexes($) {
>     my $self = shift;
>     $self->do("CREATE INDEX $connect" . '_idx ' .
>              "ON $connect (src, sender_domain, sender_name)")
>         or $self->mydie(...);
>     $self->do("CREATE INDEX $connect" . '_fseen ' .
>              "ON $connect (first_seen)")
>         or $self->mydie(...);
> }
>
> Does the problem actually exist at all?
>   

You are right, the problem exists in the from_awl and (less often) in
the domain_awl tables where there are constraints but not the connect
table. I see from_awl errors even on a moderately loaded domain (less
than 10_000 mails/day).

The same logic can solve it though : if we ignore errors writing to the
awl tables, we can still detect DB errors when reading from them and
accessing the other tables.

Lionel