replicator: User listing returned failure

[Alexey]

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