9

I just tried to add a new user to MySQL using

CREATE USER 'name'@'%' IDENTIFIED BY '...'

However, it fails with the following error:

ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'

Adding the user just for localhost works fine:

CREATE USER 'name'@'localhost' IDENTIFIED BY '...'

I have no clue what could be the problem. I'd be grateful for any ideas.

(I'm using mysql Ver 14.14 Distrib 5.1.66.)

  • I faced the similar issue, while CREATE / DROP the user, I verified all the grant tables for user information, I could not find any user info, I tried revoking all access and dropped the user, after sometime, the operation works, without doing any changes, I written my detailed answer here rathishkumar.in/2018/10/… – Rathish Oct 22 '18 at 8:50
13

According to the docs if you ommit the @'hostname' (that is CREATE USER 'name') MySQL will interpret it as it had a @'%'. The error message you provided suggests that there is already a user 'name'@'%' in the system:

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'
mysql> CREATE USER 'name' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'

If you delete the user and still get the message, try running FLUSH PRIVILEGES.

Also see this SO question for additional info.

| improve this answer | |
1

I was facing this same error while using mysql:5.7 docker image. Main mistake was trying to create root user which exists by default. More information: https://github.com/docker-library/mysql/issues/129

As given in the above link, solution was to NOT set MYSQL_USER and MYSQL_PASSWORD in the environment variables while starting the docker image.

| improve this answer | |
  • This helped me a lot! I didn't know why I couldn't connect to the DB in docker and never suspected this was the reason. – dingo_d Nov 20 '19 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.