replicator: User listing returned failure
Hello.
I've tried to configure TCP replication to slave server. But I got some errors...
mx1:~# dovecot --version 2.2.27 (c0f36b0)
From dovecot.log: May 07 19:27:41 auth-worker(34348): Warning: mysql: Query failed, retrying: Unknown column 'username' in 'field list' May 07 19:27:41 auth-worker(34348): Debug: sql(*): SELECT username, domain FROM users May 07 19:27:41 auth-worker(34348): Error: sql: Iterate query failed: Unknown column 'username' in 'field list' (using built-in default iterate_query: SELECT username, domain FROM users) May 07 19:27:41 auth-worker(34348): Debug: sql(*): SELECT id AS username, domain FROM users May 07 19:27:41 replicator: Error: User listing returned failure May 07 19:27:41 replicator: Error: listing users failed, can't replicate existing data
mx1:~# doveadm replicator status Queued 'sync' requests 0 Queued 'high' requests 0 Queued 'low' requests 0 Queued 'failed' requests 0 Queued 'full resync' requests 0 Waiting 'failed' requests 2 Total number of known users 2
mx1:~# doveadm replicator status '*'
username
priority fast sync full sync success sync failed
abuse@example.com
none 00:03:01 01:06:36 - y
admin@exmaple.com
none 00:03:41 01:31:49 - y
From slave:
mx2:~# cat /etc/dovecot/conf.d/90-replication.conf # use tcp:hostname as the dsync target plugin { mail_replica = tcp:mx1 # use doveadm_port }
As I can see nothing happens automatically.
But mx2:~# doveadm -D sync -u abuse@example.com -d -N -l 30 -U
successfully executed and getting mails from mx1.
dovecot-sql.conf.ext: driver = mysql connect = host=localhost dbname=vmail user=sqlmail password=sqL_hidden033|TGPAS default_pass_scheme = SHA512-CRYPT password_query = SELECT id AS username, password, domain FROM users WHERE id = '%n' AND domain = '%d' AND active = 'Y' user_query = SELECT id AS username, uid, gid, home, concat('*:storage=', quota, 'M' ) as quota_rule FROM users WHERE id = '%n' AND domain = '%d' iterate_query = SELECT id AS username, domain FROM users
Regards, Alexey
mx1:~# dovecot -n # 2.2.27 (c0f36b0): /etc/dovecot/dovecot.conf # Pigeonhole version 0.4.16 (fed8554) # OS: Linux 4.9.0-6-amd64 x86_64 Debian 9.4 auth_master_user_separator = * auth_verbose = yes auth_verbose_passwords = sha1 default_vsz_limit = 512 M doveadm_password = # hidden, use -P to show it doveadm_port = 994 hostname = mx1.example.com imap_client_workarounds = delay-newmail tb-extra-mailbox-sep tb-lsub-flags imap_idle_notify_interval = 12 mins lda_mailbox_autocreate = yes lda_mailbox_autosubscribe = yes lmtp_save_to_detail_mailbox = yes log_path = /var/log/dovecot/dovecot.log mail_access_groups = mail mail_location = mdbox:~/mdbox:UTF-8 mail_plugins = " notify replication" managesieve_notify_capability = mailto managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date index ihave duplicate mime foreverypart extracttext 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 Trash { auto = subscribe special_use = \Trash } prefix = } passdb { args = /etc/dovecot/dovecot-sql-master.conf.ext driver = sql master = yes pass = yes } passdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { quota = dict:User quota::proxy::quota quota_rule = *:storage=100G quota_rule2 = Trash:storage=+10G quota_warning = storage=95%% quota-warning 95 %u quota_warning2 = storage=80%% quota-warning 80 %u sieve_before = /srv/vmail/dovecot_global_filter.sieve sieve_dir = ~/sieve sieve_global_extensions = +vnd.dovecot.duplicate } postmaster_address = postmaster@example.com protocols = " imap lmtp sieve" service aggregator { fifo_listener replication-notify-fifo { group = mail mode = 0660 user = dovecot } unix_listener replication-notify { group = mail mode = 0660 user = dovecot } } service auth { unix_listener auth-client { group = mail mode = 0660 } unix_listener auth-userdb { group = mail } } service dict { unix_listener dict { group = mail mode = 0660 user = dovecot } } service doveadm { inet_listener { port = 994 } } service lmtp { executable = lmtp -L unix_listener lmtp { group = mail mode = 0660 } } service managesieve-login { inet_listener sieve { port = 4190 } } service quota-warning { executable = script /usr/local/bin/quota-warning.sh unix_listener quota-warning { user = mail } user = dovecot } service replicator { process_min_avail = 1 unix_listener replicator-doveadm { group = mail mode = 0660 user = dovecot } } ssl = required ssl_cert = </etc/ssl/example.com/fullchain-combo.crt ssl_cipher_list = ALL:HIGH:!SSLv2:!SSLv3:!MEDIUM:!LOW:!EXP:!RC4:!MD5:!aNULL:@STRENGTH ssl_key = # hidden, use -P to show it userdb { args = /etc/dovecot/dovecot-sql-master.conf.ext driver = sql } userdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } protocol lmtp { log_path = /var/log/dovecot/lmtp.log mail_plugins = " notify replication sieve" } protocol lda { log_path = /var/log/dovecot/lda.log mail_plugins = " notify replication sieve" } protocol imap { mail_plugins = " notify replication" }
Hello Alexey,
mx1:~# dovecot --version 2.2.27 (c0f36b0)
From dovecot.log: May 07 19:27:41 auth-worker(34348): Warning: mysql: Query failed, retrying: Unknown column 'username' in 'field list' May 07 19:27:41 auth-worker(34348): Debug: sql(*): SELECT username, domain FROM users May 07 19:27:41 auth-worker(34348): Error: sql: Iterate query failed: Unknown column 'username' in 'field list' (using built-in default iterate_query: SELECT username, domain FROM users) May 07 19:27:41 auth-worker(34348): Debug: sql(*): SELECT id AS username, domain FROM users May 07 19:27:41 replicator: Error: User listing returned failure May 07 19:27:41 replicator: Error: listing users failed, can't replicate existing data
dovecot-sql.conf.ext: driver = mysql ... iterate_query = SELECT id AS username, domain FROM users
you defined a custom iterate_query but the debug message says it uses the built-in default:
using built-in default iterate_query: SELECT username, domain FROM users
Please check if your config is included:
conf.d/10-auth.conf: !include auth-sql.conf.ext
conf.d/auth-sql.conf.ext:
userdb { driver = sql args = /etc/dovecot/dovecot-sql.conf.ext }
Maybe your dovecot-sql.conf.ext is somewhere dovecot does not look for it.
Besides you have configured mysql userdb twice:
userdb { args = /etc/dovecot/dovecot-sql-master.conf.ext driver = sql } userdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql }
Best regards, Gerald
mx1:~# doveadm replicator status Queued 'sync' requests 0 Queued 'high' requests 0 Queued 'low' requests 0 Queued 'failed' requests 0 Queued 'full resync' requests 0 Waiting 'failed' requests 2 Total number of known users 2
mx1:~# doveadm replicator status '*' username priority fast sync full sync success sync failed abuse@example.com none 00:03:01 01:06:36 - y admin@exmaple.com none 00:03:41 01:31:49 - y
From slave:
mx2:~# cat /etc/dovecot/conf.d/90-replication.conf # use tcp:hostname as the dsync target plugin { mail_replica = tcp:mx1 # use doveadm_port }
As I can see nothing happens automatically. But mx2:~# doveadm -D sync -u abuse@example.com -d -N -l 30 -U successfully executed and getting mails from mx1.
dovecot-sql.conf.ext: driver = mysql connect = host=localhost dbname=vmail user=sqlmail password=sqL_hidden033|TGPAS default_pass_scheme = SHA512-CRYPT password_query = SELECT id AS username, password, domain FROM users WHERE id = '%n' AND domain = '%d' AND active = 'Y' user_query = SELECT id AS username, uid, gid, home, concat('*:storage=', quota, 'M' ) as quota_rule FROM users WHERE id = '%n' AND domain = '%d' iterate_query = SELECT id AS username, domain FROM users
Regards, Alexey
mx1:~# dovecot -n # 2.2.27 (c0f36b0): /etc/dovecot/dovecot.conf # Pigeonhole version 0.4.16 (fed8554) # OS: Linux 4.9.0-6-amd64 x86_64 Debian 9.4 auth_master_user_separator = * auth_verbose = yes auth_verbose_passwords = sha1 default_vsz_limit = 512 M doveadm_password = # hidden, use -P to show it doveadm_port = 994 hostname = mx1.example.com imap_client_workarounds = delay-newmail tb-extra-mailbox-sep tb-lsub-flags imap_idle_notify_interval = 12 mins lda_mailbox_autocreate = yes lda_mailbox_autosubscribe = yes lmtp_save_to_detail_mailbox = yes log_path = /var/log/dovecot/dovecot.log mail_access_groups = mail mail_location = mdbox:~/mdbox:UTF-8 mail_plugins = " notify replication" managesieve_notify_capability = mailto managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date index ihave duplicate mime foreverypart extracttext 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 Trash { auto = subscribe special_use = \Trash } prefix = } passdb { args = /etc/dovecot/dovecot-sql-master.conf.ext driver = sql master = yes pass = yes } passdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { quota = dict:User quota::proxy::quota quota_rule = *:storage=100G quota_rule2 = Trash:storage=+10G quota_warning = storage=95%% quota-warning 95 %u quota_warning2 = storage=80%% quota-warning 80 %u sieve_before = /srv/vmail/dovecot_global_filter.sieve sieve_dir = ~/sieve sieve_global_extensions = +vnd.dovecot.duplicate } postmaster_address = postmaster@example.com protocols = " imap lmtp sieve" service aggregator { fifo_listener replication-notify-fifo { group = mail mode = 0660 user = dovecot } unix_listener replication-notify { group = mail mode = 0660 user = dovecot } } service auth { unix_listener auth-client { group = mail mode = 0660 } unix_listener auth-userdb { group = mail } } service dict { unix_listener dict { group = mail mode = 0660 user = dovecot } } service doveadm { inet_listener { port = 994 } } service lmtp { executable = lmtp -L unix_listener lmtp { group = mail mode = 0660 } } service managesieve-login { inet_listener sieve { port = 4190 } } service quota-warning { executable = script /usr/local/bin/quota-warning.sh unix_listener quota-warning { user = mail } user = dovecot } service replicator { process_min_avail = 1 unix_listener replicator-doveadm { group = mail mode = 0660 user = dovecot } } ssl = required ssl_cert = </etc/ssl/example.com/fullchain-combo.crt ssl_cipher_list = ALL:HIGH:!SSLv2:!SSLv3:!MEDIUM:!LOW:!EXP:!RC4:!MD5:!aNULL:@STRENGTH ssl_key = # hidden, use -P to show it userdb { args = /etc/dovecot/dovecot-sql-master.conf.ext driver = sql } userdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } protocol lmtp { log_path = /var/log/dovecot/lmtp.log mail_plugins = " notify replication sieve" } protocol lda { log_path = /var/log/dovecot/lda.log mail_plugins = " notify replication sieve" } protocol imap { mail_plugins = " notify replication" }
Hello Gerald,
Besides you have configured mysql userdb twice:
userdb { args = /etc/dovecot/dovecot-sql-master.conf.ext driver = sql } userdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } Great!!! This was the root cause. Second userdb with dovecot-sql-master.conf rewrite the first one, but sql-master.conf didn't include iterate_query. I removed the wrong second declaration. Thanks.
What about automatic replication without manually running of doveadm sync?
As I can see nothing happens automatically. But mx2:~# doveadm -D sync -u abuse@example.com -d -N -l 30 -U
successfully executed and getting mails from mx1.
Regards, Alexey
What about automatic replication without manually running of doveadm sync?
As I can see nothing happens automatically. But mx2:~# doveadm -D sync -u abuse@example.com -d -N -l 30 -U successfully executed and getting mails from mx1.
I don't know if it makes a difference, I don't have quotes on my mail_plugins:
mail_plugins = $mail_plugins notify replication
Did you check permissons on the replication fifos?
Does it work if you issue:
doveadm replicator replicate '*' doveadm replicator status '*'
Do you have different hostnames for your servers?
Best regards Gerald
I don't know if it makes a difference, I don't have quotes on my mail_plugins: I don't have quotes too (It's difference between config file and dovecot -n output)
Did you check permissons on the replication fifos? For what? I think that problems on slave. How it should work in automatic mode? I repeat that from slave manually all works fine:
As I can see nothing happens automatically. But mx2:~# doveadm -D sync -u abuse@example.com -d -N -l 30 -Usuccessfully executed and getting mails from mx1.
prw-rw---- 1 dovecot mail 0 May 8 06:29 replication-notify-fifo
Does it work if you issue:
doveadm replicator replicate '*' mx1:~# doveadm replicator replicate '*' 2 users updated
doveadm replicator status '*' I already posted this output.
Do you have different hostnames for your servers? Sure. You may see in listing that short hostnames are mx1 and mx2.
Wow. Sorry, maybe it works from morning or maybe somethings changes at the evening, but I may say that replication works fine. But I can confirm it now only with "less on mdbox file". But replication works!!!
Thanks Gerald.
On 2018-05-08 23:17, Alexey wrote:
I don't know if it makes a difference, I don't have quotes on my mail_plugins: I don't have quotes too (It's difference between config file and dovecot -n output)
Did you check permissons on the replication fifos? For what? I think that problems on slave. How it should work in automatic mode? I repeat that from slave manually all works fine:
As I can see nothing happens automatically. But mx2:~# doveadm -D sync -u abuse@example.com -d -N -l 30 -Usuccessfully executed and getting mails from mx1.
prw-rw---- 1 dovecot mail 0 May 8 06:29 replication-notify-fifo
Does it work if you issue:
doveadm replicator replicate '*' mx1:~# doveadm replicator replicate '*' 2 users updated
doveadm replicator status '*' I already posted this output.
Do you have different hostnames for your servers? Sure. You may see in listing that short hostnames are mx1 and mx2.
Am 08.05.2018 um 23:37 schrieb Alexey <alukardd@alukardd.org>:
Wow. Sorry, maybe it works from morning or maybe somethings changes at the evening, but I may say that replication works fine. But I can confirm it now only with "less on mdbox file". But replication works!!!
Thanks Gerald.
That's great, you're welcome.
Maybe replication was stopped due to errors. doveadm replicator replicate '*' tells dovecot to start replication and with doveadm replicator status '*' you can immediately see if something has changed.
Best regards Gerald
On 2018-05-08 23:17, Alexey wrote:
I don't know if it makes a difference, I don't have quotes on my mail_plugins: I don't have quotes too (It's difference between config file and dovecot -n output) Did you check permissons on the replication fifos? For what? I think that problems on slave. How it should work in automatic mode? I repeat that from slave manually all works fine:
As I can see nothing happens automatically. But mx2:~# doveadm -D sync -u abuse@example.com -d -N -l 30 -U successfully executed and getting mails from mx1.prw-rw---- 1 dovecot mail 0 May 8 06:29 replication-notify-fifo Does it work if you issue: doveadm replicator replicate '*' mx1:~# doveadm replicator replicate '*' 2 users updated doveadm replicator status '*' I already posted this output. Do you have different hostnames for your servers? Sure. You may see in listing that short hostnames are mx1 and mx2.
participants (2)
-
Alexey
-
Gerald Galster