[Dovecot] Quotas with MySQL

[Neil Smith]

I've just tried changing the variables around in the source code and as 
you say that does at least get the data into the table.

I've noticed another quirk with this quota backend in addition to the 
others you have pointed out. I have two accounts on the same Dovecot 
server. Same recipient name but different domains.
e.g
neil at example.com
neil at example.net

The entire address is used as the user name for Dovecot.

I access both of these accounts from the same client (Thunderbird) on 
the same machine. When the first account is connected to (say 
neil at example.com) the user neil at example.com is looked up in the quota 
table as we'd expect.

When the second account (neil at example.net) connects seconds later, the 
quota looked up in MySQL is neil at example.com and not neil at example.net. 
This also appears to affect the INSERT query with neil at example.com 
always being the specified user in the query.

If I swap the order in which the accounts are initially checked after 
loading Thunderbird, neil at example.net is the account that is always used 
in the query, thus it appears to be that it's the account connected to 
first that is used.

Is anyone else able to replicate this problem?

Regards,
Neil


djonas at vitalwerks.com wrote:
> On Sep 5, 2006, at 12:23 PM, Neil Smith wrote:
>
>> Hello,
>>
>> I've had a look through past messages and I think this has been 
>> brought up but I'm not sure what the outcome of it was. I'm trying to 
>> use quotas with a MySQL dict backend. I am using Dovecot version 
>> 1.0.rc6-1 from the Debian Testing package on a x86 machine.
>>
>> My dovecot.conf file has the following entries which I took from the 
>> Wiki:
>> dict {
>>    quotadict = mysql:/etc/dovecot/dovecot-dict-quota.conf
>> }
>>
>> plugin {
>>    quota = dict:storage=10240 proxy::quotadict
>> }
>>
>>
>> and my dovecot-dict-quota.conf looks like the following, again from 
>> the Wiki:
>> connect = host=127.0.0.1 dbname=mail user=<user> password=<passwd>
>> table = quota
>> select_field = current
>> where_field = path
>> username_field = username
>>
>> and my MySQL table is as follows:
>> create table quota (
>>    username varchar(255) not null,
>>    path varchar(255) not null,
>>    current integer,
>>    primary key (username, path)
>> );
>>
>> Now with this setup, the query for looking up the current quota 
>> appears in the MySQL logs as:
>> SELECT current FROM quota WHERE path = 'quota/storage' AND username  
>> = 'neil at example.com'
>>
>> and the query for Inserting/Updating the quota is:
>> BEGIN;INSERT INTO quota (current, path, username ) VALUES 
>> ('quota/storage', 0, 'neil at example.com') ON DUPLICATE KEY UPDATE 
>> current = current + 0;COMMIT
>>
>> With latter query, it appears to be trying to insert 'quota/storage' 
>> into `current` and 0 into `path`. This seems to be the wrong way 
>> around. Is that correct?
>>
>> If I change my dovecot-dict-quota.conf file to read:
>>
>> connect = host=127.0.0.1 dbname=mail user=<user> password=<passwd>
>> table = quota
>> select_field = path
>> where_field = current
>> username_field = username
>>
>> the two queries now look like:
>> SELECT path FROM quota WHERE current = 'quota/storage' AND username  
>> = 'neil at example.com'
>>
>> and
>>
>> BEGIN;INSERT INTO quota (path, current, username ) VALUES 
>> ('quota/storage', 0, 'neil at example.com') ON DUPLICATE KEY UPDATE 
>> current = current + 0;COMMIT
>>
>> Although the latter query now works as expected and does update the 
>> quota in the database, the former query is now incorrect.
>>
>> Is this a problem with my configuration or is it a bug?
>> If a bug, has it been corrected in Version 1.0.rc7?
>>
>> Thank you all.
>>
>> Neil
>>
>>
>
>
> I brought this up a few weeks ago and didn't get a response. I'd just 
> like to add that I experienced the exact same thing and adjustments to 
> the actual queries is what fixed it (getting the data into the table 
> at least).
>
> Seems like the mysql dict backend could use some attention. It was 
> also throwing some errors that didn't seem to have a reasonable cause 
> and the numbers it stored were, to say the least, odd. Also, it seems 
> that the mysql dict backed, to be a general backend, should not be 
> limited to integer types. I was considering doing a rework to make it 
> use any text field, then let the plugin convert it to a number.
>
> It doesn't seem like this is a very popular plugin yet,  but I'd think 
> it should work.
>
> Regards,
>
> David
>
>
>