[Dovecot] Unknown column username in where clause

[Daniel Luttermann]

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 at 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