ERROR 1396 (HY000): Operation CREATE USER failed

Question

i was creating a db in mysql and ran commands-

create database projectdb;
create user 'aquaman'@'localhost' identified by password'aquaman123';

and it gave error-

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

anyone please help me how to resolve it

Answer 1

I ran into this issue having previously deleted a user of the same name directly from the user table:

DELETE FROM mysql.user WHERE User='aquaman';

But this isn't the right way to delete a user! Having dropped the user properly, via:

DROP USER 'aquaman';

...I was once again able to successfully create a user with this name.

Answer 2

Incorrect Syntax!!

You have included an extra password in your code. Change the code from:

create user 'aquaman'@'localhost' identified by password'aquaman123';

TO:

CREATE USER 'aquaman'@'localhost' IDENTIFIED BY 'aquaman123';

Please check the documentation: http://dev.mysql.com/doc/refman/5.0/en/create-user.html

Answer 3

I faced the same issue in the below MySQL version in MAC OS - Catalina:

Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

I was able to solve the issue by the below commands:

mysql> create database testdb;
Query OK, 1 row affected (0.04 sec)

mysql> create user 'test'@'%' identified by 'YourPassword$';
Query OK, 0 rows affected (0.07 sec)

or

mysql> create user 'test'@localhost identified by 'YourPassword$';
Query OK, 0 rows affected (0.07 sec)



mysql> grant all on testdb.* to 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

or

mysql> grant all on testdb.* to 'test'@localhost;
Query OK, 0 rows affected (0.00 sec)


mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
| test             |
+------------------+