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] MySQL Verification Team
Hello Fred,

Thank you for the report.
Observed this with 5.7.10.

Thanks,
Umesh
[4 Feb 2016 4:33] MySQL Verification Team
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.