dovecot - Dec 2011 - sql dict: commit failed: MySQL server has gone away

Following on from this thread: 
http://www.dovecot.org/list/dovecot/2010-September/052704.html 
<https://owa2010.webfusion.com/owa/redir.aspx?C=a447887e3d204944a8c766142df41453&URL=http%3a%2f%2fwww.dovecot.org%2flist%2fdovecot%2f2010-September%2f052704.html>
in 2.0.16 I am seeing the same issues. I believe this would be related 
to some timeouts set on our mysql servers which are pretty low (30 or 60 
seconds or so to drop idle connections). This is happening for imap, pop 
and lmtp processes when they try to update quotas stored in a mysql 
database; presumably it is the dict process that actually has these 
issues - the lmtp delivery isn't lasting longer than 1 second so 
shouldn't be seeing any timeouts there. This is always followed by the 
"Error: dict quota: Quota update failed, it's now desynced" line; 
presumably it is telling the truth but I'm not sure how I could tell.

We are also seeing this "MySQL server has gone away" issue in login 
processes but it isn't causing an issue there as presumably dovecot 
transparently retries rather than just giving up as it seems to in the 
quota updates. Any thoughts or advice would be appreciated.

Mark

On Wed, 2011-12-07 at 20:38 +0200, Mark Zealey wrote:
> Following on from this thread: 
> http://www.dovecot.org/list/dovecot/2010-September/052704.html 
>
<https://owa2010.webfusion.com/owa/redir.aspx?C=a447887e3d204944a8c766142df41453&URL=http%3a%2f%2fwww.dovecot.org%2flist%2fdovecot%2f2010-September%2f052704.html>
> in 2.0.16 I am seeing the same issues. I believe this would be related 
> to some timeouts set on our mysql servers which are pretty low (30 or 60 
> seconds or so to drop idle connections). 
Yes, most likely. But it only happens if the quota isn't enforced (e.g.
unlimited quota), because otherwise the previous "current quota usage"
lookup would have automatically reconnected.
> This is happening for imap, pop 
> and lmtp processes when they try to update quotas stored in a mysql 
> database; presumably it is the dict process that actually has these 
> issues - the lmtp delivery isn't lasting longer than 1 second so 
> shouldn't be seeing any timeouts there. This is always followed by the 
> "Error: dict quota: Quota update failed, it's now desynced"
line;
> presumably it is telling the truth but I'm not sure how I could tell.
Yes. All sql dict updates go through dict process, which keeps a long
running sql connection open.
> We are also seeing this "MySQL server has gone away" issue in
login
> processes but it isn't causing an issue there as presumably dovecot 
> transparently retries rather than just giving up as it seems to in the 
> quota updates. Any thoughts or advice would be appreciated.
You mean auth processes. In case of auth the lookup is retried though.

Anyway, v2.1 now retries the commit:
http://hg.dovecot.org/dovecot-2.1/rev/782f09d13ece

Thanks Timo, this patch applied clean against 2.0.16 just running it on live for
the past few minutes & seems to have fixed the issues.

Mark
________________________________________
From: Mark Zealey
Sent: 08 December 2011 07:14
To: Timo Sirainen
Cc: dovecot at dovecot.org
Subject: RE: [Dovecot] sql dict: commit failed: MySQL server has gone away

Correct we're not enforcing the quotas at present just want an easy way to
monitor everyone's mailbox usage.

Thanks for the patch; will it work ok against 2.0.16? if so I'll recompile
and give it a test today.

Mark