MySQL Bugs: #78374: "CREATE USER IF NOT EXISTS" reports an error

Bug #78374	"CREATE USER IF NOT EXISTS" reports an error
Submitted:	9 Sep 2015 6:07	Modified:	16 Jan 2016 15:50
Reporter:	Giuseppe Maxia (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.7.9, 8.0.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
According to the manual, "CREATE USER IF NOT EXISTS" should issue a warning if the user already exists, instead of an error, however, this does not happen:

mysql> select version();
+--------------------------------------+
| version()                            |
+--------------------------------------+
| 5.7.9-enterprise-commercial-advanced |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> create user if not exists abc identified by 'xyz';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for abc;
+---------------------------------+
| Grants for abc@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'abc'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

mysql>  create user if not exists abc identified by 'xyz';
ERROR 1396 (HY000): Operation CREATE USER failed for 'abc'@'%'

How to repeat:
see above

Hello Giuseppe,

Thank you for the report.
Verified as described on 5.7.9/5.8.0 builds.

Thanks,
Umesh

// 5.7.9

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.9                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.9-enterprise-commercial-advanced                    |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

mysql> create user if not exists abc identified by 'xyz';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for abc;
+---------------------------------+
| Grants for abc@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'abc'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

mysql> create user if not exists abc identified by 'xyz';
ERROR 1396 (HY000): Operation CREATE USER failed for 'abc'@'%'
mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Error | 1396 | Operation CREATE USER failed for 'abc'@'%' |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> show errors;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Error | 1396 | Operation CREATE USER failed for 'abc'@'%' |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

// 5.8.0

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.8.0                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.8.0-m17-enterprise-commercial-advanced                |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> create user if not exists abc identified by 'xyz';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for abc;
+---------------------------------+
| Grants for abc@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'abc'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

mysql> create user if not exists abc identified by 'xyz';
ERROR 1396 (HY000): Operation CREATE USER failed for 'abc'@'%'

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.

Bug #80259 marked as duplicate of this.

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0