Bug #81403 switchover incorrectly creates new user on candidate when wildcard in host
Submitted: 12 May 2016 17:40
Reporter: Andrii Nikitin Email Updates:
Status: Verified Impact on me:
Category:MySQL Utilities Severity:S3 (Non-critical)
Version:1.5.6, 1.6.2 OS:Any
Assigned to: CPU Architecture:Any

[12 May 2016 17:40] Andrii Nikitin
This part works as expected:

mysqlrpladmin refuses to switchover when user doesn't exists, e.g.:

$ mysqlrpladmin --master=m1 --new-master=m2  --slaves=m2 --demote-master --rpl-user=r1 switchover
Candidate slave is missing replication user.

When user exists with wide host - the error is not reported (as expected), but new user is created (which is unexpected).

E.g. with replication user r1@'127.0.0.%' - mysqlrpladmin will create new user r1@'' . Additional problem here is that this new user is missing "USING SSL" option.

How to repeat:
Following extract from general log demonstrates the problem (non-relevant entries are omitted):

mysqrpladmin correctly identifies that matching user exists:

144 Query     SELECT host FROM mysql.user WHERE user = 'r1' AND '' LIKE host
but later it checks using different query :

145 Query     SELECT user, host, grant_priv, password, Repl_slave_priv FROM mysql.user WHERE user ="r1" AND host =""

see this query checks puts different condition on 'host' ^ - = instead of LIKE

And later new user gets created for some reasons:

143 Query     CREATE USER 'r1'@''

it looks the problem starts is in topology.py and this query:

_SELECT_RPL_USER_PASS_QUERY = ('SELECT user, host, grant_priv, password, '
                               'Repl_slave_priv FROM mysql.user '
                               'WHERE user ="{user}" AND host ="{host}"')

used in switchover() , then later the code doesn't really detects that no user was selected and creates a new user.

More formal procedure:
1. Setup replication from m1 to m2 with user which uses broad host, e.g. r1@127.%.%.% .
2. Execute mysqlrpladmin switchover, e.g. :

mysqlrpladmin --master=m1 --new-master=m2  --slaves=m2 --demote-master --rpl-user=r1 switchover

observe new usr is created, e.g. r1@ while it is expected that mysqlrpladmin will use existing user or show an error if user not found

Suggested fix:
mysqlrpladmin swithcover shouldn't create new user if existing user exists
[16 May 2016 4:25] monty solomon
This is related to bug #78343
[16 May 2016 4:26] monty solomon
And related to bug #76275
[16 May 2016 4:29] monty solomon
And may be related to bug #76274
[17 May 2016 7:31] Arnoud Witt
Have the same problem with 1.5.6
I suggest to change this bug's title replacing the word 'widechar' with 'wildcard'.

'repl_user'@'new-master-host' is created on the new master. This user in fact does not make sense at all, since the repl_user will not connect from the new-master-host after switch.
If it would be done correctly, a 'repl_user'@'<slave-host>' entry should be created for each slave-host participating. In addition, in case 'skip-name-resolve' is in effect, the IP-address of each slave-host must be used, otherwise the entries will be ignored by the privilege system.