53

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

user3086014
  • 4,241
  • 5
  • 27
  • 56

5 Answers5

68

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'@'%';
user3086014
  • 4,241
  • 5
  • 27
  • 56
  • 1
    delete from mysql.user where user='user_name'; – Maninder Sep 23 '21 at 19:57
  • 2
    And yet, when I `drop user 'user'@'%';` (before and after flushing privileges, I still get the HY000 failure. This answer would be improved by pointing out that there are different values that may be delimited by the second set of single quotes. – Alexis Oct 04 '21 at 00:42
  • Execute `select user,host from mysql.user` to see which host belongs after the `@`. – scai Nov 17 '21 at 10:34
57

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;
Tom Mac
  • 9,693
  • 3
  • 25
  • 35
17

How to solve problem like this:

mysql> drop user 'q10'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'q10'@'localhost'

First:you should check what host of your user,such as:

mysql> select host,user from user;
+-----------+------+
| host      | user |
+-----------+------+
| %         | q10  |
| localhost | root |
| localhost | sy   |
| localhost | tom  |
+-----------+------+

if I drop user 'q10',the command is :

mysql> drop user 'q10'@'%';
Query OK, 0 rows affected (0.00 sec)

And if I drop user 'tom',the command as follow:

mysql> drop user 'tom'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Q10Viking
  • 982
  • 8
  • 9
2

Try using 'user'@'localhost' without quotes:

DROP USER user@localhost;

It should work that way in MySQL 8. I also had that problem.

t7e
  • 322
  • 1
  • 3
  • 9
2

delete from mysql.user where user='user_name' and host = 'localhost';

flush privileges;

Working for me...

Maninder
  • 1,539
  • 1
  • 10
  • 12