[Dovecot] FTS solr : body search gives mysql error
Hello,
I've recently setup FTS solr plugin, following http://wiki2.dovecot.org/Plugins/FTS/Solr, and I have this error in logs when I do a body search :
Mar 13 01:46:40 mut-mx-1 dovecot: auth-worker(5808): Warning: mysql: Query failed, retrying: Unknown column 'disableindexer' in 'where clause' Mar 13 01:46:40 mut-mx-1 dovecot: auth-worker(5808): Error: sql(user@domain.com): User query failed: Unknown column 'disableindexer' in 'where clause' Mar 13 01:46:40 mut-mx-1 dovecot: indexer-worker: Error: user user@domain.com: Auth USER lookup failed Mar 13 01:46:40 mut-mx-1 dovecot: indexer-worker: Error: User user@domain.com lookup failed: Internal error occurred. Refer to server log for more information. Mar 13 01:46:40 mut-mx-1 dovecot: imap(user@domain.com): Error: indexer failed to index mailbox INBOX
Anyway, the search result seems good. Should I worry about this error or is there other thing I should do (i.e modify mysql schema) ?
I'm using mysql as a backend to store users and password. According to the documentation, I just added this block of configuration (please tell me if need full doveconf -n) :
mail_plugins = fts fts_solr [ my other plugins ] plugin { fts = solr fts_solr = url=http://127.0.0.1:8080/solr/ }
Versions : OS : Debian 7 dovecot : 2.2.12 (from dovecot-ee repo) solr : 3.6.0 (from distrib repo)
Other recommendations and feedback about using solr FTS in production would be greatly appreciated. Many thanks.
Alexandre
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Thu, 13 Mar 2014, Alexandre Ellert wrote:
I've recently setup FTS solr plugin, following http://wiki2.dovecot.org/Plugins/FTS/Solr, and I have this error in logs when I do a body search :
Mar 13 01:46:40 mut-mx-1 dovecot: auth-worker(5808): Warning: mysql: Query failed, retrying: Unknown column 'disableindexer' in 'where clause' Mar 13 01:46:40 mut-mx-1 dovecot: auth-worker(5808): Error: sql(user@domain.com): User query failed: Unknown column 'disableindexer' in 'where clause' Mar 13 01:46:40 mut-mx-1 dovecot: indexer-worker: Error: user user@domain.com: Auth USER lookup failed Mar 13 01:46:40 mut-mx-1 dovecot: indexer-worker: Error: User user@domain.com lookup failed: Internal error occurred. Refer to server log for more information. Mar 13 01:46:40 mut-mx-1 dovecot: imap(user@domain.com): Error: indexer failed to index mailbox INBOX
Anyway, the search result seems good. Should I worry about this error or is there other thing I should do (i.e modify mysql schema) ?
I'm using mysql as a backend to store users and password. According to the documentation, I just added this block of configuration (please tell me if need full doveconf -n) :
We always do and include any additional conf files with mysql queries.
Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux)
iQEVAwUBUyF19XD1/YhP6VMHAQIWiwgAzY3ZdKczZu6Mvsq/6zUdX1uhnyiptHaN 0l8NHG8cJxj1gvEAptMAstwsaIga6KbAq9XrwB2Np2H3E+LQ96C9hBeAmA6NDFAM oifUwi5nzjxYYDIO0TESxhgLCOlLxNbelIcJ+JLPk1uhM4mcHEuhJfJ/FRcwcwTN PaCyZ8k/bW9HyfyM7+YB9GNCQkLCkz8SuSzifqX2IucTAds24UefB440v1Z/dbjF 0YbheR9QlWc/87/Nzbzid3QB0yebcsBHmKd/u1bQp7A/woYtWum+iKXjlP4Cz5rh HU5vDzLg/GC5IW5DXs/qFJZ9CJac+V4I6zLWn7acAkJRJg3sIlNo8w== =D/3o -----END PGP SIGNATURE-----
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@domain.com#011service=indexer-worker Mar 14 19:51:03 mut-mx-1 dovecot: auth-worker(10769): Debug: sql(user@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@domain.com' OR email = 'user@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@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…
Alexandre
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@domain.com#011service=indexer-worker Mar 14 19:51:03 mut-mx-1 dovecot: auth-worker(10769): Debug: sql(user@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@domain.com' OR email = 'user@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@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
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@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 =
Le 14 mars 2014 à 20:05, Reindl Harald h.reindl@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@domain.com#011service=indexer-worker Mar 14 19:51:03 mut-mx-1 dovecot: auth-worker(10769): Debug: sql(user@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@domain.com' OR email = 'user@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@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
I finally found the problem :
- add column disableindexer-worker : alter table mail_user add
disableindexer-workerenum('n','y') default 'n'; - 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@numeezy.com a écrit :
I've add the column disableindexer-worker : alter table mail_user add
disableindexer-workerenum('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@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 =
Le 14 mars 2014 à 20:05, Reindl Harald h.reindl@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@domain.com#011service=indexer-worker Mar 14 19:51:03 mut-mx-1 dovecot: auth-worker(10769): Debug: sql(user@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@domain.com' OR email = 'user@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@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
participants (3)
-
Alexandre Ellert
-
Reindl Harald
-
Steffen Kaiser