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

[Timo Sirainen]

On 19 Oct 2015, at 13:31, Timo Sirainen <tss at iki.fi> wrote:
> 
> 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.

This is pretty much the issue here. The current code works exactly as it should, so I think the old code was buggy.. So what's happening with domain quota is:

 - priv/quota/storage maps to domain.quota field
 - priv/quota/messages maps to quota2.messages field

The domain table has all the domains, so updating it has worked fine. But apparently there were no domain entries in quota2 table? And apparently the old Dovecot code was simply ignoring this and never writing anything in there? So that would be a bug. When either the storage or the messages don't exist, Dovecot does a quota recalculation and writes the resulting recalculated fields. This recalculation does a DELETE + INSERT internally.

So there are 2 things you could do now..:

1) If you manually INSERT all the missing domains to quota table, the existing code continues to work. Because the domains exist there, there's no need for Dovecot to do a quota recalculation and DELETE anything.

2) This has never been 100% safe though. There has always been the possibility of quota recalculation deleting the domain. I've now added a "no-unset" parameter to dict:

  quota = dict:domain_quota:%d:no-unset:proxy::sqldomainquota

This disables the DELETEs completely. However quota recalculation still attempts to do:

INSERT INTO domain (quota,domain) VALUES ('5404','postdomain.lan') ON DUPLICATE KEY UPDATE quota='5404'

Which fails in your case, because MySQL wants the INSERT to be valid as well. You can kludge around it by changing MySQL schema with:

alter table domain alter description set default '';
alter table domain alter transport set default 'dovecot';

Now quota recalculation works as well. Except of course domain quota recalculation doesn't actually work correctly, because it calculates only the single user's quota and places it to the domain quota.