amavisd-new SQL-Error... Please Help!

[Mark Martinec]

Klaus,

> thank you for your answer to my problem.
> 
> I have stopped the execution of the scripts, and the errors gona away.
> 
> I used a "cleanup"-script, which is part of the "MailZu" installation.
> Maybe, this ist not the best way, preventing the Database from growing
> up?
> 
> Can you give me some advice to solve the problem, of preventing the
> growth of the database?

A quick glance through MailZu's mz_db_clean.pl doesn't show an obvious
problem. Perhaps try running mz_db_clean.pl with a verbose option
and correlate the timestamp of a SQL purging directive which was running
at the time, with a timestamp of a problem logged by amavisd.

I find it much more effective to base purges on a partition tag (and
drop foreign keys references). The partition tag can be derived by a
week number (or based on a day of a week number if one can only afford
to keep a one week's worth of data in a database).

E.g.:
    $partition_tag =
      sub { my($msginfo)=@_; iso8601_week($msginfo->rx_time) };

adjusting week numbers range according to the current week number, e.g.

  DELETE FROM msgs       WHERE partition_tag >= 13 AND partition_tag <= 23;
  DELETE FROM msgrcpt    WHERE partition_tag >= 13 AND partition_tag <= 23;
  DELETE FROM quarantine WHERE partition_tag >= 13 AND partition_tag <= 23;
  DELETE FROM maddr      WHERE partition_tag >= 13 AND partition_tag <= 23;


Mark