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