More information about - Dovecot 2.2.x quota mysql and dict

[Timo Sirainen]

On 17 Oct 2015, at 12:41, Timo Sirainen <tss at iki.fi> wrote:
> 
>> Connect   postfix at localhost on postfix
>>           134 Query     SELECT bytes FROM quota2 WHERE username =
>> 'user at mydomain.lan'
>>           135 Query     SELECT bytes FROM quota2 WHERE username =
>> 'user at mydomain.lan'
>>           135 Query     BEGIN
>>           135 Query     UPDATE quota2 SET
>> bytes=bytes+2570,messages=messages+1 WHERE username = 'user at mydomain.lan'
> 
> Here's it's updating quota with user at mydomain.lan
> 
>>           140 Query     SELECT messages FROM quota2 WHERE username =
>> 'mydomain.lan'
>>           140 Query     BEGIN
>>           140 Query     DELETE FROM domain WHERE domain = 'mydomain.lan'
>>           140 Query     DELETE FROM quota2 WHERE username = 'mydomain.lan'
>>           140 Query     INSERT INTO domain (quota,domain) VALUES
>> ('8581','mydomain.lan') ON DUPLICATE KEY UPDATE quota='8581'
>> 151004 15:18:45  140 Quit
> 
> Here it's only mydomain.lan. So something's not right.

After seeing your configs, this makes more sense now, although it's a bit confusing. You're updating quota bytes to "domain" table, but quota messages to "quota2" table where both domain and user quotas are mixed.

>> Now dic + mysql doesn’t work in Dovecot 2.2.19, because first DELETE
>> operator delete information from base about my domain mydomain.lan  
> 
> I can't reproduce this. What's your doveconf -n output? And what's in the dovecot-dict-sql.conf.ext?

I still couldn't reproduce this with your exact config. I think that there is no bug or change in behavior in v2.2.19. There are some situations when Dovecot wants to recalculate the quota (e.g. a message is expunged but its size isn't known). In these situations Dovecot recalculates the quota and does a DELETE + INSERT to dict. This code has been there since the beginning. Maybe you just happened to be finally triggering this situation. So it's not safe to place the quota updates to a table that has other information also. Also this means that the domain-quota doesn't work 100% in all situations and I guess some kind of a daily recalculation for them would be a good idea..