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@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@example.com,127.0.0.1): query: SELECT username, domain, password FROM mailbox WHERE username = 'test111@example.com' AND domain = 'example.com' AND active = '1' Oct 8 17:16:23 webmail dovecot: auth(default): client out: OK^I1^Iuser=test111@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@example.com,127.0.0.1): lookup Oct 8 17:16:23 webmail dovecot: auth(default): passwd(test111@example.com,127.0.0.1): unknown user Oct 8 17:16:23 webmail dovecot: auth-worker(default): sql(test111@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@example.com' AND domain = 'example.com' and active = '1' Oct 8 17:16:23 webmail dovecot: auth-worker(default): sql(test111@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@example.com' AND domain = 'example.c' at line 1 Oct 8 17:16:23 webmail dovecot: auth(default): master out: USER^I11^Itest111@example.com Oct 8 17:16:23 webmail dovecot: IMAP(test111@example.com): Loading modules from directory: /usr/local/lib/dovecot/imap Oct 8 17:16:23 webmail dovecot: IMAP(test111@example.com): Module loaded: /usr/local/lib/dovecot/imap/lib10_quota_plugin.so Oct 8 17:16:23 webmail dovecot: IMAP(test111@example.com): Module loaded: /usr/local/lib/dovecot/imap/lib11_imap_quota_plugin.so Oct 8 17:16:23 webmail dovecot: IMAP(test111@example.com): Effective uid=1015, gid=105, home= Oct 8 17:16:23 webmail dovecot: IMAP(test111@example.com): Quota root: name= backend=maildir args= Oct 8 17:16:23 webmail dovecot: IMAP(test111@example.com): Quota rule: root= mailbox=* bytes=104857600 (0%) messages=0 (0%) Oct 8 17:16:23 webmail dovecot: IMAP(test111@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@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@example.com): maildir: data=/var/spool/mail/example.com/test111/Maildir Oct 8 17:16:23 webmail dovecot: IMAP(test111@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@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@example.com' AND domain = 'example.com' and active = '1' Oct 8 17:16:23 webmail dovecot: auth-worker(default): sql(test111@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@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@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@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@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. :)