Re: replicator: User listing returned failure

8 May 2018


      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 =

Re: replicator: User listing returned failure

Gerald Galster