MySQL - CREATE A USER IF IT DOES NOT EXIST

Question

CREATE USER IF NOT EXISTS ...

A new user is created without problems. An existing user returns this error, but the docs read that CREATE USERfor MySQL> 5.7.6 it supports it.

MySQL version

Ver 14.14 Distrib 5.7.11, for osx10.9 (x86_64) using  EditLine wrapper

Example

<root:none> CREATE USER IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar';
--------------
CREATE USER IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar'
--------------

Query OK, 0 rows affected (0.00 sec)

<root:none> CREATE USER IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar';
--------------
CREATE USER IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar'
--------------

ERROR 1396 (HY000): Operation CREATE USER failed for 'foo'@'localhost'

Suggestions?

Answer 1

CREATE USER IF NOT EXISTSthrows an error if you use the sentence IDENTIFIED BYand the user exists. It does not throw an error and works as expected if you are not using the sentence IDENTIFIED BY.

mysql> CREATE USER IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foo'@'localhost'

mysql> CREATE USER IF NOT EXISTS 'foo'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

From 5.7.8 instead, CREATE USER IF NOT EXISTSyou can use DROP USER IF EXISTSbefore calling CREATE USERwith a sentence IDENTIFIED BY.

mysql> DROP USER 'foo'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER 'foo'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'foo'@'localhost'

mysql> DROP USER IF EXISTS 'foo'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Another option is to first create a user, and then set a password after creating the user.

mysql> CREATE USER IF NOT EXISTS 'foo'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER IF NOT EXISTS 'foo'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET PASSWORD FOR 'foo'@'localhost' = 'bar';
Query OK, 0 rows affected (0.01 sec)

Answer 2

FLUSH.

FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'foo'@'localhost';

.