[Dovecot] Quotas with MySQL

[djonas at vitalwerks.com]

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