← Back to team overview

maria-developers team mailing list archive

Re: CREATE USER gotcha

 

The same behaviour is observed with DROP USER.


On 12/18/2014 11:50 AM, Alexander Barkov wrote:
Hi Ian,

I think there is a CREATE USER related gotcha which is not described
in the manual.

CREATE USER allows to create multiple users at the same time:

CREATE USER user1, user2;

A reader of
https://mariadb.com/kb/en/mariadb/documentation/sql-commands/account-management-sql-commands/create-user/


might think that the above query is a short synonym for:

CREATE USER user1;
CREATE USER user2;

But in fact it is not always true.
The difference can happen during replication.


A "CREATE USER" statement is binlogged as a single shot,
but only if at least one user was really created.


Suppose (on master) user1 already exists, while user2 does not.

"CREATE USER user1, user2" will be binary logged as is,

while two independent "CREATE USER user1" and "CREARE USER user2"
statements will be logged only as "CREATE USER user2".
The CREATE statement for user1 won't be logged,
because it failed on master!


Now, suppose non of the users exist on slave.
In case of "CREATE USER user1, user2;" both will be created on slave.
In case of two independent queries, only "user2" will be created on slave.



Follow ups

References