dovecot - Sep 2006 - Quotas with MySQL

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

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