Solve ERROR 1396 (HY000): Operation DROP USER failed for 'user'@'localhost' in MySql?

This error occurs when you drop a user with localhost while you have created a user with ‘%’.

Let us create a user with ‘%’ and drop the user as a localhost. The syntax is as follows

CREATE USER 'yourUserName'@'%' IDENTIFIED BY 'yourPassword';

Let us create a user using the above syntax. The query to create a user is as follows

mysql> CREATE USER 'Jack'@'%' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.26 sec)

Check user is created successfully or not

mysql> select user,host from MySQL.user;

The following is the output

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| Jack             | %         |
| Manish           | %         |
| User2            | %         |
| mysql.infoschema | %         |
| mysql.session    | %         |
| mysql.sys        | %         |
| root             | %         |
| Adam Smith       | localhost |
| User1            | localhost |
| am               | localhost |
+------------------+-----------+
10 rows in set (0.00 sec)

Look at the above sample output, we have a user with name ‘Jack’ and host is ‘%’. Whenever you try to drop the user with localhost then you will get an error which is as follows

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

Let us drop the above user with host ‘%’. The query is as follows

mysql> DROP USER 'Jack'@'%';
Query OK, 0 rows affected (0.19 sec)

Check the user has been dropped or not from MySQL.user table. The query is as follows

mysql> select user,host from MySQL.user;

The following is the output displaying that the user Jack is removed successfully

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| Manish           | %         |
| User2            | %         |
| mysql.infoschema | %         |
| mysql.session    | %         |
| mysql.sys        | %         |
| root             | %         |
| Adam Smith       | localhost |
| User1            | localhost |
| am               | localhost |
+------------------+-----------+
9 rows in set (0.00 sec)