ERROR 1396 (HY000): Operation DROP USER failed for 'user'@'localhost'

Question

I have created a user in mysql. Now i want to delete the user ? How to do that? I am getting this error :

ERROR 1396 (HY000): Operation DROP USER failed for 'user'@'localhost'

I am using this command :

DROP USER 'user'@'localhost';

Its an amazon machine.

Thanks

Answer 1

It was because i created the user using command :

CREATE USER 'user'@'%' IDENTIFIED BY 'passwd';

and i was deleting it using :

drop user 'user'@'localhost';

and i should have used this command :

drop user 'user'@'%';

Answer 2

It is likely that the user you are trying to drop does not exist. You can confirm (or not) whether this is the case by running:

select user,host
from mysql.user
where user = '<your-user>';

If the user does exist then try running:

flush privileges;

drop user 'user'@'localhost';

Another thing to check is to make sure you are logged in as root user

If all else fails then you can manually remove the user like this:

delete from mysql.user
where user='<your-user>'
and host = 'localhost';

flush privileges;