[Dovecot] Warning: mysql: Query failed, retrying: Unknown column 'filter' in 'where clause'

[Thomas Creutz]

This sql query must be build somewhere from dovecot itself. I can add
the column name to the user_qeuery:

user_query = SELECT email as user, maildir as home, CONCAT('maildir:',
maildir, '/Maildir') as mail, uid, gid, CONCAT('*:storage=', quota, 'B')
AS quota_rule, CONCAT(maildir, '/.sieve') as sieve, ASCII( '110' ) AS
"disablesieve-filter" FROM mail_user WHERE (login = '%u' OR email =
'%u') AND disable%Ls = 'n'

But problem is, that dovecot dont quote the column name, so the column
name with the - is a error for its own.

http://stackoverflow.com/questions/885497/hyphens-in-column-names-in-mysql-db

Thomas

Am 20.02.2014 15:04, schrieb Thomas Creutz:
> Ok, today I did the debug test:
>
> Feb 20 14:57:59 mail1 dovecot: auth: Debug: master in:
> USER#0111#011account at domain.tld#011service=sieve-filter
> Feb 20 14:57:59 mail1 dovecot: auth-worker(29572): Debug:
> sql(account at domain.tld): SELECT email as user, maildir as home,
> CONCAT('maildir:', maildir, '/Maildir') as mail, uid, gid,
> CONCAT('*:storage=', quota, 'B') AS quota_rule, CONCAT(maildir,
> '/.sieve') as sieve FROM mail_user WHERE (login = 'account at domain.tld'
> OR email = 'account at domain.tld') AND disablesieve-filter = 'n'
> Feb 20 14:57:59 mail1 dovecot: auth-worker(29572): Warning: mysql: Query
> failed, retrying: Unknown column 'filter' in 'where clause'
> Feb 20 14:57:59 mail1 dovecot: auth-worker(29572): Error:
> sql(account at domain.tld): User query failed: Unknown column 'filter' in
> 'where clause'
> Feb 20 14:57:59 mail1 dovecot: auth: Debug: userdb out: FAIL#0111
>
> my dovecot-sql.conf holds this:
> -----
> driver = mysql
> connect = host=localhost dbname=dbispconfig user=ispconfig password=XXXXXXXX
> default_pass_scheme = CRYPT
>
> password_query = SELECT password FROM mail_user WHERE (login = '%u' OR
> email = '%u') AND disable%Ls = 'n'
> user_query = SELECT email as user, maildir as home, CONCAT('maildir:',
> maildir, '/Maildir') as mail, uid, gid, CONCAT('*:storage=', quota, 'B')
> AS quota_rule, CONCAT(maildir, '/.sieve') as sieve FROM mail_user WHERE
> (login = '%u' OR email = '%u') AND disable%Ls = 'n'
>
> iterate_query = SELECT email as user FROM mail_user
> -----
>
> From where comes the disablesieve-filter?
>
> Thomas
>
> Am 18.02.2014 11:10, schrieb Michael Cramer:
>> you can try to turn on the debug logging so that you can see which
>> query is executed
>>
>> mail_debug=yes
>> auth_debug=yes
>>
>>
>> Zitat von Thomas Creutz <t.creutz at topackt.com>:
>>
>>> I would add some informations:
>>>
>>> auth lookup works fine with doveadm:
>>>
>>> $ doveadm auth lookup account at domain.tld
>>> passdb: account at domain.tld
>>>   user      : account at domain.tld
>>>
>>> Any Ideas?
>>>
>>> Thomas
>>>
>>> Am 17.02.2014 16:31, schrieb Thomas Creutz:
>>>> Hi List,
>>>>
>>>> I have a problem with dovecot 2.2.12 and sieve-filter call:
>>>>
>>>> su -c 'sieve-filter -D -v -W -C -u account at domain.tld
>>>> /var/vmail/domain.tld/account/.sieve INBOX.Refilter' vmail
>>>>
>>>> gives me
>>>>
>>>> sieve-filter(vmail): Error: user account at domain.tld: Auth USER
>>>> lookup failed
>>>> sieve-filter(vmail): Fatal: Internal error occurred. Refer to server
>>>> log
>>>> for more information.
>>>>
>>>> on log file I see this:
>>>>
>>>> Feb 17 16:21:34 mail1 dovecot: auth-worker(8430): Warning: mysql: Query
>>>> failed, retrying: Unknown column 'filter' in 'where clause'
>>>> Feb 17 16:21:34 mail1 dovecot: auth-worker(8430): Error:
>>>> sql(account at domain.tld): User query failed: Unknown column 'filter' in
>>>> 'where clause'
>>>>
>>>> but I never used filter in dovecot:
>>>>
>>>> grep -iR filter /etc/dovecot/
>>>>
>>>> gives me no result (and yes the dir is right).
>>>>
>>>> Any Idea whats wrong? I know it tells me, that the column is missing in
>>>> the Database - but why I need it? I also never see it documented
>>>> somewhere:
>>>>
>>>> http://wiki2.dovecot.org/AuthDatabase/SQL
>>>> http://wiki2.dovecot.org/PasswordDatabase/ExtraFields
>>>>
>>>> Thomas
>>> -- 
>>> MfG
>>> Thomas Creutz
>>>
>>> Topackt IT Solutions GmbH
>>> Altspeyerer Weide 2
>>> 67346 Speyer
>>> ---------------------------------------
>>> Mobil: 0151-11711132
>>> Tel:   06232-31504-0
>>> Fax:   06232-31504-240
>>> -----------------------------------------------
>>> Mail: t.creutz at topackt.com
>>> WWW:  http://www.topackt.com
>>> pgp:  https://ssl.topackt.com/pgp/
>>> Xing: http://www.xing.com/go/invite/5799158.1aa7c1
>>> -------------------------------------------------------
>>> Registergericht: Amtsgericht Ludwigshafen, HRB 53097
>>> Geschäftsführer: Michael Nist

-- 
MfG
Thomas Creutz

Topackt IT Solutions GmbH
Altspeyerer Weide 2
67346 Speyer
---------------------------------------
Mobil: 0151-11711132
Tel:   06232-31504-0
Fax:   06232-31504-240
-----------------------------------------------
Mail: t.creutz at topackt.com
WWW:  http://www.topackt.com
pgp:  https://ssl.topackt.com/pgp/
Xing: http://www.xing.com/go/invite/5799158.1aa7c1
-------------------------------------------------------
Registergericht: Amtsgericht Ludwigshafen, HRB 53097
Geschäftsführer: Michael Nist