[Dovecot] FTS solr : body search gives mysql error

[Alexandre Ellert]

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
>