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?

Improve this question
6
  • Here create user failed as user is already exists Commented Feb 14, 2016 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? Commented Feb 14, 2016 at 14:21
  • 1
    Is strict sql mode enabled? Commented Feb 14, 2016 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. Commented Feb 14, 2016 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. Commented Feb 14, 2016 at 15:15

2 Answers 2

Reset to default
21

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
You can use the CREATE USER IF NOT EXISTS without the IDENTIFIED CLAUSE. Then use SET PASSWORD FOR to set the password.
Answer accepted because it helps with the problem at hand. It is unclear if the behaviour is intended or buggy.
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)
-2

Try doing a FLUSH PRIVILEGES. 

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

You can check the link. It might help you

link

Improve this answer

1 Comment

Does this mean that CREATE USER IF NOT EXISTS is buggy and known as such?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.