ERROR 1396 (HY000): Operation CREATE USER failed for 'username'@'localhost' IDENTIFIED BY 'mypassword';

Question

Mistakenly I deleted my root user from my mysql.user table.

delete from mysql.user where user='username';

To make same root user, I am trying to fire below query,

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

I get error as

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

As per this SO answer, I tried FLUSH PRIVILEGES;, but still I get same error.

Any idea what is going wrong?

---

Answer

I also had to delete the same from mysql.db table

delete from mysql.db where user='username';

That's it...

Answer 1

If you use DROP USER command to remove the user, then the user gets removed completely. If you have to add user with same username later, you can use FLUSH PRIVILEGES command to completely remove privileges from the mysql memory.

DROP USER username@hostname;

You have to restart mysql or run FLUSH PRIVILEGES before creating the same user because there are issues with privileges. To restart mysql in linux based systems

sudo service mysql restart

to restart in windows systems

net stop mysql
net start mysql

To flush privilege in mysql prompt

FLUSH PRIVILEGES;

You can alternatively use REVOKE command if you know which privileges that the user has

REVOKE privillege1, privillege2, ... FROM deleted_user@host

Answer 2

This user must be referenced in other tables from the mysql system schema. I would recreate the user the same way you deleted it:

INSERT INTO mysql.user (user, host, password)
VALUES ('root', 'localhost', PASSWORD('pasw'));

Then

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

Yet another reason to never mess with this schema (sorry, I couldn't help).