[Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does.

[dovecot at corwyn.net]

dovecot-sql.conf:
driver = mysql
connect = host=localhost dbname=postfix user=postfix password=postfix
default_pass_scheme = MD5-CRYPT
password_query = \
   SELECT username, domain, password \
   FROM mailbox WHERE username = '%u' AND domain = '%d' AND active = '1'
user_query = \
    SELECT 1015 as uid, 105 as gid, '/var/spool/mail/%d/%n' as home, \
    'maildir:/var/spool/mail/%d/%n/Maildir' as mail, \
    concat('*:storage=', quota, 'B') as quota_rule, \
         FROM mailbox WHERE username = '%u' AND domain = '%d' and active = '1'

dovecot.conf
   quota = maildir
   quota_rule = *:storage=102400
   quota_warning = storage=95%% /usr/local/sbin/quota_notify 95
   quota_warning2 = storage=80%% /usr/local/sbin/quota_notify 80

Telnet:
Connected to 127.0.0.1.
Escape character is '^]'.
* OK Dovecot ready.
. login test111 at example.com Test111
. OK Logged in.
. getquotaroot inbox
* QUOTAROOT "inbox" ""
* QUOTA "" (STORAGE 67 102400)
. OK Getquotaroot completed.

Quota still shows what's in dovecot.conf

/var/log/maillog
Oct  8 17:16:23 webmail dovecot: auth(default): client in: 
AUTH^I1^IPLAIN^Iservice=imap^Isecured^Ilip=127.0.0.1^Irip=127.0.0.1^Ilport=143^Irport=49601^Iresp=<hidden>
Oct  8 17:16:23 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): query: SELECT username, domain, 
password FROM mailbox WHERE username = 'test111 at example.com' AND 
domain = 'example.com' AND active = '1'
Oct  8 17:16:23 webmail dovecot: auth(default): client out: 
OK^I1^Iuser=test111 at example.com
Oct  8 17:16:23 webmail dovecot: auth(default): master in: 
REQUEST^I11^I23281^I1
Oct  8 17:16:23 webmail dovecot: auth(default): 
passwd(test111 at example.com,127.0.0.1): lookup
Oct  8 17:16:23 webmail dovecot: auth(default): 
passwd(test111 at example.com,127.0.0.1): unknown user
Oct  8 17:16:23 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): SELECT 1015 as uid, 105 as gid, 
'/var/spool/mail/example.com/test111' as home, 
'maildir:/var/spool/mail/example.com/test111/Maildir' as mail, 
concat('*:storage=', quota, 'B') as quota_rule, FROM mailbox WHERE 
username = 'test111 at example.com' AND domain = 'example.com' and active = '1'
Oct  8 17:16:23 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): User query failed: You have an 
error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'FROM mailbox 
WHERE username = 'test111 at example.com' AND domain = 'example.c' at line 1
Oct  8 17:16:23 webmail dovecot: auth(default): master out: 
USER^I11^Itest111 at example.com
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Loading 
modules from directory: /usr/local/lib/dovecot/imap
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Module 
loaded: /usr/local/lib/dovecot/imap/lib10_quota_plugin.so
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Module 
loaded: /usr/local/lib/dovecot/imap/lib11_imap_quota_plugin.so
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Effective 
uid=1015, gid=105, home=
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Quota 
root: name= backend=maildir args=
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Quota 
rule: root= mailbox=* bytes=104857600 (0%) messages=0 (0%)
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Quota 
warning: bytes=99614720 (95%) messages=0 (0%) 
command=/usr/local/sbin/quota_notify 95
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Quota 
warning: bytes=83886080 (80%) messages=0 (0%) 
command=/usr/local/sbin/quota_notify 80
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): maildir: 
data=/var/spool/mail/example.com/test111/Maildir
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): 
maildir++: root=/var/spool/mail/example.com/test111/Maildir, index=, 
control=, inbox=/var/spool/mail/example.com/test111/Maildir
Oct  8 17:16:23 webmail dovecot: imap-login: Login: 
user=<test111 at example.com>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured


It looks like the error is in all likelihood here:
'/var/spool/mail/example.com/test111' as home, 
'maildir:/var/spool/mail/example.com/test111/Maildir' as mail, 
concat('*:storage=', quota, 'B') as quota_rule, FROM mailbox WHERE 
username = 'test111 at example.com' AND domain = 'example.com' and active = '1'
Oct  8 17:16:23 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): User query failed: You have an 
error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'FROM mailbox 
WHERE username = 'test111 at example.com' AND domain = 'example.c' at line 1

What's interesting is that it's not truncated in the line above it

wt?

Rick


At 04:46 PM 10/8/2008, Timo Sirainen wrote:
>On Wed, 2008-10-08 at 16:29 -0400, dovecot at corwyn.net wrote:
> > >Enable auth_debug=yes and mail_debug=yes and look at the logs. It should
> > >show quota_rule being sent as part of "master out" line.
> >
> > I enabled both of those. in /var/log/maillog I'm don't see a "master" line.
>
>It should have been before the "Module loaded" etc stuff. Inside the
>authentication. But anyway:
>
> > Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota
> > root: name=storage=10240 backend=maildir args=
>
>This looks wrong. Your quota root name is "storage=10240", which really
>isn't what you want.
>
> > Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota
> > rule: root=storage=10240 mailbox=* bytes=104857600 (0%) messages=0 (0%)
>
>This is ok.
>
> >     concat('maildir:storage=', quota) as quota, \
>
>Remove this entirely.
>
> >     concat('*:storage=', quota, '') as quota_rule, \
>
>You also broke this now. :)
>