Bug #80259 sql statement "create user if not exists" fails if run twice
Submitted: 4 Feb 2016 2:08 Modified: 4 Feb 2016 4:33
Reporter: Fred Battle Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.7.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: SQL

[4 Feb 2016 2:08] Fred Battle
Description:
If I run this SQL statement twice in a row, the second one fails:

mysql> create user if not exists 'tad' identified by 'tpass' password expire never;
Query OK, 0 rows affected (0.00 sec)

mysql> create user if not exists 'tad' identified by 'tpass' password expire never;
ERROR 1396 (HY000): Operation CREATE USER failed for 'tad'@'%'

It looks the "if not exists" phrase is not used until the user account is validated, and somehow the password is not accepted.  But I think MySQL should not bother with password validation and simply note that the account name already exists and therefore issue a warning about the account already existing (and not issue an error).  The documentation says if you use "if not exists" then it will return a warning, not an error.
http://dev.mysql.com/doc/refman/5.7/en/create-user.html

I would also like to point out the general_log is logging the statements with slightly different text:
2016-02-04T01:54:19.435972Z   26 Query  CREATE USER IF NOT EXISTS 'tad'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>' PASSWORD EXPIRE NEVER
2016-02-04T01:54:51.106057Z   26 Query  CREATE USER IF NOT EXISTS 'tad'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>' PASSWORD EXPIRE NEVER

How to repeat:
Run this query twice in a row:
create user if not exists 'tad' identified by 'tpass' password expire never;

Suggested fix:
Query processing for the create user name should abort immediately and return a warning.  I don't know what the actual error is, but somehow it doesn't like the "identified by '<password>'".
[4 Feb 2016 4:31] Umesh Shastry
Hello Fred,

Thank you for the report.
Observed this with 5.7.10.

Thanks,
Umesh
[4 Feb 2016 4:33] Umesh Shastry
Looks like this is duplicate of Bug #78374, and fixed in upcoming release per changelog:

Noted in 5.7.12, 5.8.0 changelogs.

For an existing user, CREATE USER IF NOT EXISTS produced an error
rather than a warning. Similarly, for an nonexisting user, ALTER USER
IF EXISTS produced an error rather than a warning.