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
> ab...@example.com
> none 00:03:01 01:06:36 - y
> ad...@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 ab...@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 = postmas...@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"
> }
