Clean out the sql maillog used by mailwatch? {Scanned by HJMS }

[Furnish, Trever G]

Well, answering my own question for the benefit of others who may wonder,
the following statement seemed to work:

delete low_priority quick
        from maillog
        where timestamp < (now() - interval 7 day);
optimize table maillog;

Table file size stayed the same till the optimize, which cut it sharply.

--
Trever


> -----Original Message-----
> From: Furnish, Trever G [mailto:TGFurnish at HERFF-JONES.COM]
> Sent: Monday, September 29, 2003 1:29 PM
> To: MAILSCANNER at JISCMAIL.AC.UK
> Subject: Clean out the sql maillog used by mailwatch?
> {Scanned by HJMS}
>
>
> For all mailwatch users (and perhaps for users of the
> "default" sql logging
> code as well - I didn't check that schema), how do you prevent your
> mailscanner maillog table from growing indefinitely?
>
> I'm thinking of running the following statement, but I'm not
> sure that the
> "date" column is populated with reliable data - ie if a
> spammer forges the
> date, is the forged date possibly stored in the date column?
>
> delete low_priority quick from maillog where date < (curdate()-7);
>
> If that's the case, then I would expect to have to use the
> timestamp column
> instead:
>
> delete low_priority quick from maillog where timestamp <
> (now() - interval 7
> day);
>
> I'm hesitant to actually run these for fear that I'll screw
> something up I
> don't yet know about, but I don't really see anything in any
> of the other
> tables that refers to the maillog table so I think it's safe.
>
> I wouldn't expect deleting the rows to actually reduce the
> size of the data
> file used to hold the table though - any mysql guru's care to
> share how to
> do that (or to point out problems with the statement above)?
>
> --
> Trever
>