Re: [Dovecot] Quotas with MySQL

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@example.com'

and the query for Inserting/Updating the quota is: BEGIN;INSERT INTO quota (current, path, username ) VALUES ('quota/ storage', 0, 'neil@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@example.com'

and

BEGIN;INSERT INTO quota (path, current, username ) VALUES ('quota/ storage', 0, 'neil@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