Matt Rude wrote:

Hello, Im having problems getting Dovecot to read the quota info from MySQL. My current dovecot.conf file is below.

If I run the user_query of:

user_query = SELECT 1000 AS uid, 1000 AS gid, maildir AS home FROM mailbox WHERE username = '%u' AND active = '1'

Everything works fine but if I run the user_query of:

user_query = SELECT 1000 AS uid, 1000 AS gid, maildir AS home, concat('quota=maildir:storage=', floor( mailbox.quota / 1024 )) AS quota FROM mailbox WHERE username = '$u' AND active = '1'

if I were to run the query from MySQL the I get:

+------+------+----------------------------------+------------------------------+

| uid | gid | home | quota | +------+------+----------------------------------+------------------------------+

| 1000 | 1000 | mattrude.com/lists@mattrude.com/ | quota=maildir:storage=512000 | +------+------+----------------------------------+------------------------------+

If I run the above query I am unable to log into the server:

Feb 4 20:06:11 dovecot: imap-login: Internal login failure: user=<lists@mattrude.com mythtv@mattrude.com From=akstcaloprestimnsdgs@alopresti.com File=/tmp/clamav/virus.7Q

...

, method=PLAIN, rip=192.168.1.10, lip=192.168.1.1, TLS

## Dovecot configuration file #protocols = imap imaps pop3 pop3s protocols = imap imaps login_user = postfix listen = ssl_listen = ssl_disable = no ssl_cert_file = /etc/pki/dovecot/certs/dovecot.pem ssl_key_file = /etc/pki/dovecot/private/dovecot.pem auth_cache_size = 128 auth_cache_ttl = 600

mail_location = maildir:/var/spool/virtualmailboxes/%d/%u/imap/:INBOX=/var/spool/virtualmailboxes/%d/%u/:INDEX=/var/spool/virtualmailboxes/%d/%u/imap/index/

protocol imap { mail_plugins = quota imap_quota }

protocol pop3 { mail_plugins = quota }

protocol lda { postmaster_address = postmaster@mattrude.com hostname = samantha.mattrude.com mail_plugins = cmusieve mail_plugins = quota sieve_global_path = /var/spool/sieve/dovecot.sieve mail_plugin_dir = /usr/lib/dovecot/lda auth_socket_path = /var/run/dovecot/auth-master }

auth default { mechanisms = plain digest-md5 cram-md5

userdb sql { args = /etc/dovecot-mysql.conf } passdb sql { args = /etc/dovecot-mysql.conf } user = root count = 2 }

dict { }

plugin sql { quota = maildir:storage=1048576 quota = maildir:ignore=Trash }

If anyone sees what im missing please let me know

Thanks -Matt

To answer my own question. Here is the correct user_query entry for MySQL and quotas.

<--This must be on one line for v1.0.10--> user_query = SELECT maildir, 1000 AS uid, 1000 AS gid, CONCAT('maildir:storage=', ROUND( mailbox.quota / 1024 ) ) AS quota FROM mailbox WHERE username = '%u' AND active = '1'

-Matt