MySQL Bugs: #85733: CREATE USER IF NOT EXISTS should always be written to the binlogs

Bug #85733	CREATE USER IF NOT EXISTS should always be written to the binlogs
Submitted:	31 Mar 2017 8:50	Modified:	3 Oct 2017 22:19
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	binlogs, CREATE USER IF NOT EXISTS, replication

Description:
MySQL 5.7 introduced a new command CREATE USER IF NOT EXISTS ...

The idea of this command is that it can be run safely on a master and would not break things irrespective of whether the user exists on the master or the slave.  However, it looks like the implementation does not write this statement to the binlogs when the user exists on the master.

How to repeat:
I run this on a master running 5.7.17 where a slave has an account missing.  The intention is to create the account IF IT IS MISSING but to not affect the account on the master or on any slaves where it is present.  This must be run in such a way that replication will not break.

myuser@master [(none)]> CREATE USER IF NOT EXISTS 'dbuser'@'%';
Query OK, 0 rows affected, 1 warning (0.05 sec)

myuser@master [(none)]> ALTER USER 'dbuser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*F97BD3A333123456789005D627EB1790CDA28CB7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.01 sec)

myuser@master [(none)]> GRANT CREATE, CREATE VIEW, DROP, EVENT, SELECT ON `db1`.* TO 'dbuser'@'%';
Query OK, 0 rows affected (0.02 sec)

myuser@master [(none)]> GRANT PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SUPER ON *.* TO 'dbuser'@'%';
Query OK, 0 rows affected (0.00 sec)

The slave with the missing user reports the following error:

Last_SQL_Error: Error 'Operation ALTER USER failed for 'dbuser'@'%'' on query. Default database: ''. Query: 'ALTER USER 'dbuser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*F97BD3A333123456789005D627EB1790CDA28CB7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK'

I check the binlogs on the master and see this:

[myuser@master ~]$ sudo mysqlbinlog -vvv /path/to/binlog.060717  | grep dbuser
ALTER USER 'dbuser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*F97BD3A333123456789005D627EB1790CDA28CB7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
GRANT SELECT, CREATE, DROP, CREATE VIEW, EVENT ON `db1`.* TO 'dbuser'@'%'
GRANT RELOAD, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'dbuser'@'%'
[myuser@master ~]$

So clearly the CREATE USER IF NOT EXISTS statement has not been pushed into the binlogs.  The fact the user exists on the master does NOT mean that the statement should not be written to the binlogs. It should ALWAYS be written to the binlogs.

Suggested fix:
Please always write the CREATE USER IF NOT EXISTS .... statement to the binlogs.

[ Note: the CREATE USER ... statement behaves differently and if the statement fails nothing is written to the binlog. That behaviour is correct. ]

Hello Simon,

Thank you for the report and feedback!

Thanks,
Umesh

test results

Attachment: 85733_5.7.17.results (application/octet-stream, text), 10.13 KiB.

on 5.7 this also affects ALTER USER IF EXISTS .....
it doesn't get written to the binlog if the user didn't exist.

MySQL 8.0 seems to behave as we expect, and writes to binlog.

Posted by developer:
 
Fixed in 5.7.21, 8.0.4.

CREATE USER IF NOT EXISTS was not written to the binary log if the
user existed. This could result in inconsistent replication behavior
if the user did not exist on slave servers. A similar issue occurred
for ALTER USER IF EXISTS. To avoid inconsistencies, these statements
now are written to the binary log.