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

Vishal
  • 2,097
  • 6
  • 27
  • 45
  • Here `create user` failed as user is already exists – Nagendra Nigade Feb 14 '16 at 14:15
  • I expect that `IF NOT EXISTS` is supposed to handle this error just as it does everywhere else. Is this a known bug or am I not using it correctly? – Vishal Feb 14 '16 at 14:21
  • 1
    Is strict sql mode enabled? – Shadow Feb 14 '16 at 14:30
  • I am not sure about whether it is handled or not but there are ways by which you can handle this. Like you can use `select * from user where user='name';` or you can take count from this query. **OR** As you are creating user you have to give grants to user. In mysql you can use grant command to create user if not already created like `GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;` Grant will create user if not already exists. – Nagendra Nigade Feb 14 '16 at 14:33
  • I appreciate that there are other ways to achieve the same result. I am keen to understand why `IF NOT EXISTS` doesn't work as documented. – Vishal Feb 14 '16 at 15:15
  • Possible duplicate of [mysql create user if not exists](https://stackoverflow.com/q/13357760/608639) – jww Mar 24 '19 at 10:49

2 Answers2

20

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)
Zubair Suri
  • 361
  • 1
  • 4
  • 2
    `DROP USER IF EXISTS` followed by `CREATE USER IDENTIFIED BY ...` is very different from `CREATE USER IF NOT EXISTS IDENTIFIED BY ...` and has some seriously un-desireable consequences. The former is way more dangerous than the latter. It was unclear to me that `CREATE USER IF NOT EXISTS` with `IDENTIFIED BY` was intentionally not implemented. Thank you for the comments. I appreciate the alternate suggestion nonetheless. – Vishal Mar 15 '16 at 02:48
  • 3
    You can use the `CREATE USER IF NOT EXISTS` without the `IDENTIFIED CLAUSE`. Then use `SET PASSWORD FOR` to set the password. – Zubair Suri Mar 15 '16 at 21:39
  • Answer accepted because it helps with the problem at hand. It is unclear if the behaviour is intended or buggy. – Vishal Mar 22 '16 at 22:01
  • I thought SQL was supposed to be standard. Why is it there's no standard way to do it that works everywhere? (During testing, 3 of 4 MySQL/MariaDB systems do things differently) – jww Mar 24 '19 at 11:00
-2

Try doing a FLUSH PRIVILEGES. 

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

You can check the link. It might help you

link

Community
  • 1
  • 1
Pallavi
  • 652
  • 1
  • 10
  • 26