CODEWITHSUNDEEP
mysql
Vishal
Vishal

Reputation: 2346

MySQL - CREATE USER IF NOT EXISTS

CREATE USER IF NOT EXISTS ...

A new user is created without a problem. An existing user returns that error, but the docs read that CREATE USER for MySQL > 5.7.6 supports it.

MySQL Version is

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

Sample

<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?

Upvotes: 9

Views: 17973

Answers (2)

Zubair Suri
Zubair Suri

Reputation: 371

CREATE USER IF NOT EXISTS throws an error if you use the IDENTIFIED BY clause and the user does exist. It does not throw an error and works as expected if you do not use the IDENTIFIED BY clause.

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 of using CREATE USER IF NOT EXISTS, you can use DROP USER IF EXISTS before calling CREATE USER with the IDENTIFIED BY clause.

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)

The other option is to create the user first and then set the password after the user is created.

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)

Upvotes: 21

Pallavi
Pallavi

Reputation: 674

Try doing a FLUSH PRIVILEGES. 

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

You can check the link. It might help you

link

Upvotes: -2

Related Questions