[Dovecot] Unknown column username in where clause
Version: Dovecot 2.0 rc2
I'm testing Dovecot with MySQL quotas but I get always this error for incoming mails:
tux dovecot: lmtp(4928): Error: user testuser@dlutt.de: Auth USER lookup failed tux dovecot: auth: Error: mysql: Query failed, retrying: Unknown column 'username' in 'where clause'
I dont' know where the field "username" should come from because I don't use this field (or query).
The relevant files are:
dovecot-sql.conf.ext
password_query = SELECT userid AS user, password,
home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid,
concat('*:bytes=', quota_bytes) as userdb_quota_rule
FROM users WHERE userid = '%u'
auth-sql.conf.ext
passdb { driver = sql args = /etc/dovecot/dovecot-sql.conf.ext }
userdb { driver = prefetch }
userdb { driver = sql args = /etc/dovecot/dovecot-sql.conf.ext }
90-quota.conf
plugin { quota = dict:user::proxy::quota quota_rule = *:storage=0 }
dovecot-dict-sql.conf.ext
map { pattern = priv/quota/storage table = quota username_field = userid value_field = bytes } map { pattern = priv/quota/messages table = quota username_field = userid value_field = messages }
I've grepped through all config files but I can't find any (active) query which uses the above "where username=...".
The quota db gets correctly updated if an user logs in so the problem only exists during mail delivery. My SQL tables were crated as follows:
CREATE TABLE IF NOT EXISTS users (
userid varchar(100) NOT NULL,
password varchar(64) NOT NULL,
home varchar(255) NOT NULL,
uid int(11) NOT NULL,
gid int(11) NOT NULL,
quota_bytes varchar(10) NOT NULL,
PRIMARY KEY (userid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS quota (
userid varchar(100) NOT NULL,
bytes bigint(20) NOT NULL default '0',
messages int(11) NOT NULL default '0',
PRIMARY KEY (userid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- Daniel
Hi,
I dont' know where the field "username" should come from because I don't use this field (or query).
perhabs this is the problem? ;-)
The relevant files are:
Output of "dovecot -n" would be better, together with the additional files.
dovecot-sql.conf.ext
password_query = SELECT userid AS user, password,
home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid,
concat('*:bytes=', quota_bytes) as userdb_quota_rule
FROM users WHERE userid = '%u'
I think you're missing a user_query.
best regards,
Anton
Anton Dollmaier wrote on 19.07.2010:
Hi,
I dont' know where the field "username" should come from because I don't use this field (or query).
perhabs this is the problem?
The queries uses only "userid" instead of "username" so I think if one sql field were missing it should report that "userid" could not be found because the query is "FROM users WHERE userid = '%u'".
I've not select "username" in any sql select so I wonder where does this name come from.
Output of "dovecot -n" would be better, together with the additional files.
dovecot -n
# 2.0.rc2 (7dd7adba1c9e): /etc/dovecot/dovecot.conf # OS: Linux 2.6.33.2 x86_64 Debian 5.0.5 auth_username_format = %Lu dict { quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext } hostname = tux.linuxmail.at listen = 89.238.81.84 mail_location = mdbox:~/mdbox mail_plugins = acl quota mail_privileged_group = vmail managesieve_notify_capability = mailto managesieve_sieve_capability = comparator-i;octet comparator-i;ascii-casemap fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date spamtest spamtestplus virustest namespace { hidden = no inbox = yes location = prefix = separator = / subscriptions = yes type = private } passdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { acl = vfile:/etc/dovecot/global-acls/%d:cache_secs=300 quota = dict:user::proxy::quota quota_rule = *:storage=0 sieve = ~/.dovecot.sieve sieve_dir = ~/sieve } postmaster_address = ml@dlutt.de protocols = imap lmtp sieve service auth { unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0666 user = postfix } user = $default_internal_user } service dict { unix_listener dict { group = vmail mode = 0660 user = vmail } } service imap-login { inet_listener imap { port = 143 } } service lmtp { unix_listener /var/spool/postfix/private/dovecot-lmtp { group = postfix mode = 0660 user = postfix } } service managesieve-login { inet_listener sieve { address = 127.0.0.1 port = 4190 } } ssl_cert = </etc/ssl/certs/dovecot.pem ssl_key = </etc/ssl/private/dovecot.key userdb { driver = prefetch } userdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } protocol lmtp { mail_plugins = $mail_plugins sieve } protocol imap { mail_plugins = $mail_plugins imap_acl imap_quota }
Additional files
/etc/dovecot/dovecot-dict-sql.conf.ext
connect = host=/var/run/mysqld/mysqld.sock dbname=mail user=user password=pwd map { pattern = priv/quota/storage table = quota username_field = userid value_field = bytes } map { pattern = priv/quota/messages table = quota username_field = userid value_field = messages }
/etc/dovecot/dovecot-sql.conf.ext
driver = mysql
connect = host=/var/run/mysqld/mysqld.sock dbname=mail user=user password=pwd
default_pass_scheme = PLAIN
password_query = SELECT userid AS user, password,
home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid,
concat('*:bytes=', quota_bytes) as userdb_quota_rule
FROM users WHERE userid = '%u'
-- Daniel
On 19.7.2010, at 22.59, Daniel Luttermann wrote:
The queries uses only "userid" instead of "username" so I think if one sql field were missing it should report that "userid" could not be found because the query is "FROM users WHERE userid = '%u'".
I've not select "username" in any sql select so I wonder where does this name come from.
There's actually a default query.. Hmm. Maybe it should be made empty, since probably no one wants to use it.
password_query = SELECT userid AS user, password,
home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid,
concat('*:bytes=', quota_bytes) as userdb_quota_rule
FROM users WHERE userid = '%u'
You must also have a user_query for LDA/LMTP.
Timo Sirainen wrote on 20.07.2010:
On 19.7.2010, at 22.59, Daniel Luttermann wrote:
The queries uses only "userid" instead of "username" so I think if one sql field were missing it should report that "userid" could not be found because the query is "FROM users WHERE userid = '%u'".
I've not select "username" in any sql select so I wonder where does this name come from.
There's actually a default query.. Hmm. Maybe it should be made empty, since probably no one wants to use it.
hmm, I've nothing read about that - but it's OK because the examples in the wiki creates tables with the field "username" instead of "userid" which I've used so the default query should then match...
password_query = SELECT userid AS user, password,
home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid,
concat('*:bytes=', quota_bytes) as userdb_quota_rule
FROM users WHERE userid = '%u'
You must also have a user_query for LDA/LMTP.
OK, it seems to be working if I add this in dovecot-sql.conf.ext too:
user_query = SELECT home, uid, gid,
concat('*:bytes=', quota_bytes) as quota_rule
FROM users WHERE userid = '%u'
-- Daniel
participants (3)
-
Anton Dollmaier
-
Daniel Luttermann
-
Timo Sirainen