Just an update that I think I know what has been going on.

I believe I've been tripped up by two things.

1) Not always clearly reading the actual component being referred to when "userid", "user" and "username" are used in official online documentation or other web sources.

2) Setting up dovecot to accept delivery / auth on %n and dropping the domain.


For #1 it is descriptions such as the following from the online documentation which tripped me up

%u

user

full username (e.g. user@domain)

%n

username

user part in user@domain, same as %u if there's no domain

i.e. %n referring both to "username" and "user" where for testing@example.com %n is testing and %u is testing@example.com

Another one which could be clearer is the dovecot-sql.conf.ext file gives a example database with username and domain fields and the same file has an example password_query with the userid field.

For #2 failure to get to grips with #1 and also various examples online which aren't fully context explained meant I had an inconsistent set of queries.

After getting to grips with that I changed my mysql auth to be consistent and always return username and domain e.g.

[Note in my database username contains "testing" and domain "example.com")

password_query = SELECT username, domain [etc]

user_query = [does not return username or domain]

iterate_query = SELECT username,domain FROM users

i.e. note they all now return two separate fields with "testing" and "example.com" which dovecot is merging as per the documentation.

With that set up both

dovadm user '*'

and

doveadm -A [etc]

return and use testing@example.com

The last problem I had was that in my mail delivery (from an alias file) I was using

testing: "dovecot-lda -d testing"

And that seems to trigger replication to trigger on testing as well as testing@example.com being triggered when the user made changes to their account.

So having changed the mysql as per above and the delivery to

testing: "dovecot-lda -d testing@example.com"

and having removed all entries without @domain from replication with

doveadm replicator remove [entries without domains]

doveadm repliciator status

now returns the correct number of accounts and

doveadm repliciator status '*'

does not have "duplicate" entries.

Fingers crossed that is it and there are no other call which will cause replication on "testing" as separate from "testing@example.com"



On 04/01/19 09:11, Daniel Schütze wrote:
I have a replication set up with two dovecot 2.3.4 servers.

Messages appear to be replicating fine between them, however there is an oddity when I run doveadm replicator status '*' and I'm worried work is being carried out needlessly.

Both machines have identical configs and have a mysql backend for username/passwords.  Both machines point to the same mysql server and same tables.

When I run

doveadm user '*'

On either machine I am presented with the same correct list of users.  The users are presented in the form: username and not username@domain.

When I run

doveadm replicator status '*'

On either machine I receive a list of usernames which includes not only username but also username@domain.  Both of these "accounts" have fast sync, full sync and success sync entries and these are not the same times between username and username@domain (hence I'm worried unnecessary work is being carried out).

My ee dovecot-sql.conf.ext

Has these entires re usernames

user_query = SELECT home, uid, gid FROM users WHERE username = '%n'

password_query = SELECT username AS user, domain, password, home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid FROM users WHERE username = '%u'

[Having copied these in I do see the different usage of %u and %n]

iterate_query = SELECT username AS user,domain FROM users

The users table is as follows

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| username     | varchar(128) | NO   |     | NULL    |       |
| domain       | varchar(128) | NO   |     | NULL    |       |
| password     | varchar(64)  | NO   |     | NULL    |       |
| home         | varchar(255) | NO   |     | NULL    |       |
| uid          | int(11)      | NO   |     | NULL    |       |
| gid          | int(11)      | NO   |     | NULL    |       |
| active       | char(1)      | NO   |     | Y       |       |
| mail_replica | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

I assume that the duplication of username with and without domains isn't as intended, any suggestions where I am going wrong?

I did previously have the servers running with the iterate query commented out and when that was the case

doveadm user '*'

returned the correct number of accounts but in the format username@domain and the replicator status again 'double listed' accounts as described above.

Thank you for any tips!