Re: [Sqlgrey-users] MySQL: first_seen updated when it shouldn't

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

Michel Bouissou wrote:

>Le Jeudi 02 Juin 2005 10:28, Lionel Bouton a =E9crit :
> =20
>
>>MySQL really doesn't want to behave :-(
>>
>>If there isn't a workaround to disable this behaviour, then switching
>>first_seen and last_seen would be a quick fix.
>>   =20
>>
>
>Using MySQL-4.1.12, I found a workaround to this issue by executing the=20
>following commands :
>
>alter table from_awl modify first_seen timestamp default 0;
>
>alter table from_awl modify last_seen timestamp default CURRENT_TIMESTAM=
P on=20
>update CURRENT_TIMESTAMP;
>
>alter table domain_awl modify first_seen timestamp default 0;
>
>alter table domain_awl modify last_seen timestamp default CURRENT_TIMEST=
AMP on=20
>update CURRENT_TIMESTAMP;
>
>alter table connect modify first_seen timestamp default CURRENT_TIMESTAM=
P;
> =20
>

CURRENT_TIMESTAMP won't work with SQLite. If I need to change the table
creation, I'd better change the order of the columns.

Although I have found another way: if the UPDATE statements are modified
to look like:
UDPATE <awl> SET first_seen =3D first_seen, last_seen =3D <now> WHERE
<cond>. MySQL doesn't change first_seen (at least 4.1.12 doesn't). As
the 2 timestamp columns are forced, I can't imagine a way for any MySQL
version to change the values.

This is a quick fix, won't create ugly special cases and the database
system will probably optimize the query properly.

>Other subject: Lionel, have you had time to think again about the tarpit=
ting /=20
>throttling feature that I had suggested ? I still would like it ;-)
>
> =20
>

I'm not yet convinced it's a good idea. I'll sum up what I understand
about tarpitting below for you to point mistakes or missing points.

Tarpitting (refusing to create new connect entries if there are already
<n> existing entries with the same source, with refinements to disable
tarpitting when domain_awl holds entries for the source or enough
entries exist in from_awl) could help preventing pollutions of the
connect table from one single src.
This would have two main benefits :
B1/ faster DB access to the connect table (which probably is the most
used on common config under heavy Zombie pressure...) from SQLgrey.
B2/ easier analysis of the connect table by a curious sysadmin.

Here are the risks I'm worried about :
R1/ using tarpitting will also interfere with legit mails that don't
match AWLs (more retries).
R2/ SQLgrey will have to do another query on the connect table which
would most probably kill the performance advantage we get from a smaller
connect table and for the refinements, one query involving domain_awl
and if needed another involving from_awl.

My main problem for me was with R1 until the number of queries piled up
to avoid the fact that in some cases (big ISPs consolidating email
infrastructure, new smtp-out adresses, ...) you might very well
introduce huge delays or even bounce mails (which was a show stopper for
me). Now I think R2 will make the whole thing pointless.

Lionel.