[Dovecot] FTS solr : body search gives mysql error

Sat Mar 15 14:45:07 UTC 2014

I finally found the problem :
1. add column disableindexer-worker : alter table mail_user add `disableindexer-worker` enum('n','y') default 'n';
2. modify dovecot-sql.conf with backtick around disable%Ls : 
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'

Le 14 mars 2014 à 22:33, Alexandre Ellert <aellert at numeezy.com> a écrit :

> I've add the column disableindexer-worker : 
> alter table mail_user add `disableindexer-worker` enum('n','y') default 'n';
> 
> But the problem remain the same.
> 
> I finally found why, here is my dovecot-sql.conf :
> 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'
> 
> The problem is in the query : disable%Ls
> 
> I don't have any indexer-worker defined in my configuration. Maybe I can do an override to stop execute user_query and password_query for indexer-worker ? (solr search seems working fine)
> 
> # 2.2.12.0: /etc/dovecot/dovecot.conf
> # OS: Linux 3.2.0-4-amd64 x86_64 Debian 7.4 ext4
> auth_debug = yes
> auth_mechanisms = plain login
> base_dir = /var/run/dovecot/
> hostname = mail.numeezy.com
> imap_capability = +XLIST
> lda_mailbox_autocreate = yes
> listen = 188.165.154.169
> login_greeting = Ready.
> mail_gid = 5000
> mail_location = maildir:/var/vmail/%d/%n/Maildir
> mail_plugins = quota mail_log notify fts fts_solr
> mail_privileged_group = mail
> mail_uid = 5000
> namespace inbox {
>  inbox = yes
>  location = 
>  mailbox Drafts {
>    auto = subscribe
>    special_use = \Drafts
>  }
>  mailbox Junk {
>    auto = subscribe
>    special_use = \Junk
>  }
>  mailbox Sent {
>    auto = subscribe
>    special_use = \Sent
>  }
>  mailbox "Sent Messages" {
>    special_use = \Sent
>  }
>  mailbox Trash {
>    auto = subscribe
>    special_use = \Trash
>  }
>  mailbox name {
>    special_use = \Drafts \Junk \Sent \Trash
>  }
>  prefix = 
> }
> passdb {
>  args = /etc/dovecot/dovecot-sql.conf
>  driver = sql
> }
> plugin {
>  antispam_backend = mailtrain
>  antispam_mail_notspam = --ham
>  antispam_mail_sendmail = /usr/local/bin/sa-learn-pipe.sh
>  antispam_mail_spam = --spam
>  antispam_spam = Junk
>  antispam_trash = Trash
>  fts = solr
>  fts_autoindex = yes
>  fts_solr = break-imap-search url=http://127.0.0.1:8080/solr/
>  mail_log_events = delete expunge mailbox_delete
>  mail_log_fields = uid box msgid from subject
>  quota = dict:user::file:/var/vmail/%d/%n/.quotausage
>  quota_grace = 10%%
>  quota_rule = Trash:storage=+200M
>  quota_warning = storage=95%% quota-warning 95 %u
>  quota_warning2 = storage=80%% quota-warning 80 %u
>  sieve = /var/vmail/%d/%n/.sieve
>  sieve_before = /etc/dovecot/sieve_before
>  sieve_vacation_send_from_recipient = yes
> }
> postmaster_address = postmaster at numeezy.com
> protocols = imap pop3
> service auth-worker {
>  user = vmail
> }
> service auth {
>  unix_listener /var/spool/postfix/private/auth {
>    group = postfix
>    mode = 0660
>    user = postfix
>  }
>  unix_listener auth-userdb {
>    group = vmail
>    mode = 0600
>    user = vmail
>  }
>  user = dovecot
> }
> service imap-login {
>  inet_listener imap {
>    port = 143
>  }
>  inet_listener imaps {
>    port = 993
>    ssl = yes
>  }
>  process_limit = 500
>  service_count = 1
> }
> service pop3-login {
>  inet_listener pop3 {
>    port = 110
>  }
>  inet_listener pop3s {
>    port = 995
>    ssl = yes
>  }
>  service_count = 1
> }
> service quota-warning {
>  executable = script /usr/local/bin/quota-warning.sh
>  unix_listener quota-warning {
>    mode = 0666
>    user = vmail
>  }
>  user = vmail
> }
> ssl_cert = </etc/postfix/smtpd.cert
> ssl_key = </etc/postfix/smtpd.key
> userdb {
>  args = /etc/dovecot/dovecot-sql.conf
>  driver = sql
> }
> protocol lda {
>  mail_plugins = quota mail_log notify fts fts_solr sieve
> }
> protocol imap {
>  mail_max_userip_connections = 80
>  mail_plugins = quota mail_log notify fts fts_solr imap_quota antispam
> }
> protocol pop3 {
>  mail_max_userip_connections = 30
>  mail_plugins = quota mail_log notify fts fts_solr
> }
> 
> 
> Le 14 mars 2014 à 20:05, Reindl Harald <h.reindl at thelounge.net> a écrit :
> 
>> Am 14.03.2014 19:58, schrieb Alexandre Ellert:
>>> With auth_debug = yes, I see that the MySQL querie change when fts solr is enabled, it adds : AND disableindexer-worker = 'n'
>>> Here is the full query :
>>> 
>>> Mar 14 19:51:03 mut-mx-1 dovecot: auth: Debug: master in: USER#0111#011user at domain.com#011service=indexer-worker
>>> Mar 14 19:51:03 mut-mx-1 dovecot: auth-worker(10769): Debug: sql(user at domain.com): 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 = 'user at domain.com' OR email = 'user at domain.com') AND disableindexer-worker = 'n'
>>> Mar 14 19:51:03 mut-mx-1 dovecot: auth-worker(10769): Warning: mysql: Query failed, retrying: Unknown column 'disableindexer' in 'where clause'
>>> Mar 14 19:51:03 mut-mx-1 dovecot: auth-worker(10769): Error: sql(user at domain.com): User query failed: Unknown column 'disableindexer' in 'where clause'
>>> Mar 14 19:51:03 mut-mx-1 dovecot: auth: Debug: userdb out: FAIL#0111
>>> 
>>> So, I guess that I need to add a disableindexer-worker column in my database but I can't find any documentation about 
>>> that nor any reference about disableindexer-worker in source code. I'm lost
>> 
>> AND disableindexer-worker = 'n' comes to my guess the other possible value is 'y'
>> so just add the missing column
>> 
>> the question before you did not understand was still the hint "provide
>> your configuration with masked internal passwords" as it is usual
>> 
> 