[Dovecot] FTS solr : body search gives mysql error

Alexandre Ellert

13 Mar 2014 13 Mar '14

10:48 a.m.

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

Show replies by date

Steffen Kaiser

13 Mar 13 Mar

11:10 a.m.

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

Alexandre Ellert

14 Mar 14 Mar

8:36 p.m.

...

We always do and include any additional conf files with mysql queries. I'm confused but I don't understand your answer. Can you please give more detail or an example ?

Thanks.

Reindl Harald

8:57 p.m.

Am 14.03.2014 19:36, schrieb Alexandre Ellert:

...

...
We always do and include any additional conf files with mysql queries. I'm confused but I don't understand your answer. Can you please give more detail or an example?

you reported a mysql query error and no configuration

Alexandre Ellert

8:58 p.m.

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

Reindl Harald

9:05 p.m.

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

Alexandre Ellert

11:33 p.m.

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 = </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@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

Alexandre Ellert

15 Mar 15 Mar

4:45 p.m.

I finally found the problem :

add column disableindexer-worker : alter table mail_user add disableindexer-worker enum('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-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 = </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@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

3935

Age (days ago)

3937

Last active (days ago)

List overview

7 comments

3 participants

participants (3)

Alexandre Ellert
Reindl Harald
Steffen Kaiser