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

[dovecot at corwyn.net]

(and oops forgot to get my last post on the list)
Hmm, I pulled a cleaner log file and got this:

Oct  8 16:44:51 webmail dovecot: auth(default): new auth connection: pid=14573
Oct  8 16:44:55 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=50346^Iresp=<hidden>
Oct  8 16:44:55 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 16:44:55 webmail dovecot: auth(default): client out: 
OK^I1^Iuser=test111 at example.com
Oct  8 16:44:55 webmail dovecot: auth(default): master in: 
REQUEST^I28^I14248^I1
Oct  8 16:44:55 webmail dovecot: auth(default): 
passwd(test111 at example.com,127.0.0.1): lookup
Oct  8 16:44:55 webmail dovecot: auth(default): 
passwd(test111 at example.com,127.0.0.1): unknown user
Oct  8 16:44:55 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('maildir:storage=', quota) as quota, concat('*:storage=', 
quota, '') as quota_rule, FROM mailbox WHERE username = 
'test111 at example.com' AND domain = 'example.com' and active = '1'
Oct  8 16:44:55 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 = 'exampl' at line 1
Oct  8 16:44:55 webmail dovecot: auth(default): master out: 
USER^I28^Itest111 at example.com
Oct  8 16:44:55 webmail dovecot: imap-login: Login: 
user=<test111 at example.com>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Loading 
modules from directory: /usr/local/lib/dovecot/imap
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Module 
loaded: /usr/local/lib/dovecot/imap/lib10_quota_plugin.so
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Module 
loaded: /usr/local/lib/dovecot/imap/lib11_imap_quota_plugin.so
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Effective 
uid=1015, gid=105, home=
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Quota 
root: name=storage=10240 backend=maildir args=
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Quota 
rule: root=storage=10240 mailbox=* bytes=104857600 (0%) messages=0 (0%)
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Quota 
warning: bytes=99614720 (95%) messages=0 (0%) 
command=/usr/local/sbin/quota_notify 95
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Quota 
warning: bytes=83886080 (80%) messages=0 (0%) 
command=/usr/local/sbin/quota_notify 80
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): maildir: 
data=/var/spool/mail/example.com/test111/Maildir
Oct  8 16:44:55 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


I see a sql error in there, but it doesn't make any sense. Why would 
it hack off the last part of the domain? (domain names have been 
changed to protect the innocent, it was truncated at 9 characters)

rick


At 04:29 PM 10/8/2008, Rick Steeves wrote:
>At 03:27 PM 10/8/2008, Timo Sirainen wrote:
>>Note that dirsize is superslow with maildir. quota=maildir is much
>>better.
>
>Yeah, I knew that, but was trying the simplest option. I've now 
>switched everything over to maildir.
>
>> >     concat('*:storage=', quota, 'B') as quota_rule, \
>>
>>Looks correct. A bit simpler would be concat('*:bytes=',quota)
>>
>>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.
>Here's the log files:
>
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Module 
>loaded: /usr/local/lib/dovecot/imap/lib10_quota_plugin.so
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Module 
>loaded: /usr/local/lib/dovecot/imap/lib11_imap_quota_plugin.so
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): 
>Effective uid=1015, gid=105, home=
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota 
>root: name=storage=10240 backend=maildir args=
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota 
>rule: root=storage=10240 mailbox=* bytes=104857600 (0%) messages=0 (0%)
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota 
>warning: bytes=99614720 (95%) messages=0 (0%) 
>command=/usr/local/sbin/quota_notify 95
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota 
>warning: bytes=83886080 (80%) messages=0 (0%) 
>command=/usr/local/sbin/quota_notify 80
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): maildir: 
>data=/var/spool/mail/example.com/test111/Maildir
>Oct  8 16:30:32 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
>
>
>I've been tweaking things as I try to figure it out, so I include 
>the current ones just in case it's critical to what I'm doing wrong
>
>I DO get dovecot to report quotas, they just don't match the quota 
>in sql (which for this user currently is 0:
>| test111 at example.com  | encryptedpassword | Test TEST    | 
>example.com/test111/  |         0 | example.com | 2008-09-30 17:24:2
>
>dovecot.conf:
>   quota = maildir:storage=10240
>   quota_rule = *:storage=102400
>   quota_warning = storage=95%% /usr/local/sbin/quota_notify 95
>   quota_warning2 = storage=80%% /usr/local/sbin/quota_notify 80
>
>dovecot-sql.conf:
>driver = mysql
>connect = host=localhost dbname=postfix user=postfix password=pfix1723
>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('maildir:storage=', quota) as quota, \
>    concat('*:storage=', quota, '') as quota_rule, \
>         FROM mailbox WHERE username = '%u' AND domain = '%d' and active = '1'
>
>
>
>Thanks
>
>Rick
>
>
>