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 at example.com
none 00:03:01 01:06:36 - y
admin at 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 at 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 at 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 usersyou 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 usersPlease 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 at example.com none 00:03:01 01:06:36 - y > admin at 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 at 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 at 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 at example.com -d -N -l 30 -U >> successfully executed and getting mails from mx1.Regards, Alexey