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

Solution 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'@'%';

Solution 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;

Solution 3

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)

Solution 4

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

DROP USER user@localhost;

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

Solution 5

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

flush privileges;

Working for me...

View more solutions

Comments

[Maninder]

This command is working for me: delete from mysql.user where user='user_name' and host = 'localhost';

[Maninder]

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

[Horitty TechMasters]

Worked For me! Many Thanks

[Maninder]

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

[Maninder]

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

[Alexis]

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.

[scai]

Execute select user,host from mysql.user to see which host belongs after the @.