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

[Mark Martinec]

Klaus,

> (!)WARN save_info_final: sql exec: err=1452, 23000, DBD::mysql::st
> execute failed: Cannot add or update a child row: a foreign key
> constraint fails (`amavis`.`msgrcpt`, CONSTRAINT `msgrcpt_ibfk_2`
> FOREIGN KEY (`mail_id`) REFERENCES `msgs` (`mail_id`) ON DELETE
> CASCADE) at (eval 99) line 166.
> 
> The Problem ONLY occurs in the LOG-file, when the daily cron jobs
> startet und a cleanup script from MailZu ist running, I think, OR
> maybe it's only a accident?
> 
> My question is, what ist the Problem and how can i solve this?
> 
> OS                 : Linux CentOS 6.2 (64-bit)
> amavisd-new Version: 2.6.6
>              Release: 2.el6.rf
> MySQL-Version      : 5.1.61
>        Release      : 1.el6_2.1

Looks like the cleanup script is removing a tentative msgs record
while processing of a message was still underway and msgrcpt
records were being added.

The way amavisd adds information to the database is:

- very early when a new mail message arrives to amavisd
  a new 'msgs' record is generated (by save_info_preliminary())
  which only contains some early information, such as
  a log_id and a sender id. Also, uniqueness of a mail_id
  is being checked at that time. Information on recipients
  and on results of mail checking is not yet available.
  Most fields in the 'msgs' record are left at their
  default value (e.g. NULL or empty);

- when processing/checking of a message has completed,
  results of a check and the information on each recipient
  is added to a table 'msgrcpt' by sub save_info_final();

- as a last step, the 'msgs' record (as generated in the first step)
  gets updated with additional information (content type, From,
  Subject, quarantine information, ...).  Only at this stage
  the information between 'msgs' and 'msgrcpt' is consistent.

If a cleanup script removes a 'msgs' record during step 2,
further attempts to add 'msgrcpt' records (which refer to
the 'msgs' record through a foreign key) would fail.
I think this is what is happening in your case.

To fix it, the cleanup script should not be removing reasonably
recent 'msgs' records whose 'msgs.content' IS NULL. It should
also check the 'msgs.time_num' or 'msgs.time_iso' fields.

Alternatively, drop the FOREIGN KEY constraint and do
the purging of old and unreferenced 'msgrcpt' records
explicitly in a cleanup script. You'd risk an occasional
'msgs' record missing while related 'msgrcpt' records
would be there.

  Mark