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

[Klaus Tachtler]

Mark,

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?


Thank you!

> 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
>


Thank you, so much!
Klaus.


--

------------------------------------------------
e-Mail  : klaus at tachtler.net
Homepage: http://www.tachtler.net
DokuWiki: http://www.dokuwiki.tachtler.net
------------------------------------------------